0

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

Improve this question

3 Answers 3

Reset to default
3

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Oh wow....so yeah thanks Hans! I was actually just asking because I am playing around with the ADO stuff, just kinda getting started with it, as I have used DAO before. I do not really have an actual need to transfer the table from one access database to another at work, just trying different things to learn ADO a little more. I like the loop idea, even though as you pointed out there may be better ways. So I would HAVE to know the exact properties of the table, all fields and data types to get this done right? as I would be building the table first? the INSERT just copies what is there but..
I am assuming that pulling it in with a recordset is different than having both objects in the same database because the currentdb would know all those properties, making the INSERT INTO method doable because of that? does that make sense?
Where can I check out the Save Method for the XML transfer? do you know of a resource with an example of this? thanks as always....I haven't forgotten about the dashboard either man. as soon as I have time to make a blank copy I will figure out how to get it to you, as I could use your advice!
Yeah, I suspected you were "exploring". :-) I added a link for recordset .Save
The attractive aspect of Raj's SELECT INTO suggestion and my DoCmd.TransferDatabase is that you don't have to first build a table structure to hold the data you will import --- it's done for you automagically. The first method I mentioned would require you to inspect each field in the recordset and create a compatible field in the destination table in your current db. Too much effort, IMO!
2

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

Improve this answer

Comments

1

You could try ADOX.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.