CODEWITHSUNDEEP
ms-accessvbaadodaorecordset
Justin
Justin

Reputation: 4539

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 create 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

Upvotes: 0

Views: 6219

Answers (3)

HansUp
HansUp

Reputation: 97101

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)

Upvotes: 3

Raj More
Raj More

Reputation: 48016

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

Upvotes: 2

user325117
user325117

Reputation:

You could try ADOX.

Upvotes: 1

Related Questions