MS Access - ADO recordset, using with SQL statement to retrieve data and build table

So lets say I have some code like below to pull data from another access file:

Sub ADO_Recordset_OpenTable()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim MyPath As String
MyPath = CurrentProject.Path

Set cn = New ADODB.Connection     
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"

cn.ConnectionString = "Data Source=C:\Users\Justin\Desktop\ExampleFile.mdb"
cn.Open

Set rs = New ADODB.Recordset
rs.Open "Schedule", cn, adOpenDynamic, adLockReadOnly, adCmdTable

' I would like to at this point build a table within the currentdb file 
'  with the data in the recordset. Either some kind of cre开发者_JS百科ate table or
'  SQL INSERT?? Just trying to learn how to work with the data set

So within the example are my comments. Basically would like to know how to create a table out of the data contained with the recordset. I guess creating a tabledef? But this is DAO right? and I couldn't really use both DAO and ADO together in a routine right?

Thanks Justin

You can use both ADO and DAO for different objects within the same procedure.

You could create a DAO.TableDef and examine the recordset's Fields collection, creating new TableDef fields matching each rs.Fields(i).Name and rs.Fields(i).Type

Once you have created the table structure (TableDef), you can loop through the recordset rows to build and execute INSERT statements to store the row values in your new table.

But that seems like waaaay too much work to me. I like Raj's SELECT INTO suggestion better. However, since you already know the table name and path to your MDB, I would reach first for DoCmd.TransferDatabase, and leave ADO only for tasks DAO can't do at all or can't do as conveniently as ADO.

Finally, if your primary interest on this one is exploring possibilities, take a look at the recordset's Save method. You could save with adPersistXML, then import the saved XML as a new table in your current db. See Save Method (ADO)

I have done this the ugly way - parse the incoming ADO recordset, build the CREATE TABLE statement and execute it, and then RBAR through the ADO dataset to insert into the local table.

You can also create a passthrough query which you can then use to SELECT * INTO MyNewTable FROM MyPassThroughQuery

You could try ADOX.