‘Save’ Recordset As New Table (2003 SP1)

Topic

New Reply

I’m surprised I’ve never needed to do this before (or maybe I’m just forgetful, which is just as likely), but I’m stumped…

I’m accessing an external database via VBA. The connection returns a recordset object. I’d like to create a new local Access table from this recordset. Is there a “trivial” way to do this? I could step through the recordset and AddNew/Update records to the new local table, one record at a time, but it seems there ought to be a more elegant way to do this. I guess what I’m looking for is the VBA equivalent of the Import Table command, starting with a populated recordset.

Thanks.

Reply | Quote

Replies

I think looping through the records and adding them to the table one by one is the way to go.

Reply | Quote

Hmmm… Okay. Dang! As an alternative, I’m trying to link to the external tables via “ODBC Databases” data source.. Machine Data Source… but so far, this alternative has been unreliable.

Thanks.

Reply | Quote

Why not just use an Append query?

Reply | Quote

Patt,

Help me with the syntax. I have table (named, say, “SaveTable”) defined to match the contents (field definitions) of the recordset. It’s empty (I applied “DELETE * FROM [SaveTable];” to it). I have an ADO recordset loaded with the records that I want appended to this table. What is the VBA code I would use to do the append? I can step through the recordset one record at a time and do an AddNew & Update for each one — as Hans suggests as the way to go, but if you can suggest one or two lines of “append” VBA code that would do the same thing…

Thanks,

Reply | Quote

You can persist a recordset to a text file in one of the specified formats, like XML. You could then import the data from that file into a new table.

Reply | Quote

Charlotte,

Per your suggestion, I’ve exported the recordset to an XML file. Now I’m stuck on how to import that file as a new table using VBA code. (I tried the File…Get External Data…Import… from Access menus to import this XML file, but the resulting tables weren’t meaningful.)

Thanks,

Reply | Quote

Sorry, but what do you mean by “the resulting tables weren’t meaningful”?

Reply | Quote

Sorry I wasn’t more explicit. File…Get External Data…Import… produced 3 new tables: “AttributeType” (with one text field named “datatype” and 7 blank records), “data” (with one text field named “row” and one blank record), “ElementType” (with one text field named “extends” and one blank record). The recordset I exported to xml has 7 fields (5 long integers and two text) and 15 records. From viewing the xml file it appears that the recordset is accurately represented with correct field definitions and row contents (that match what I exported).

I’m an XML novice, at best, so any help here would be welcome.

Thanks.

Reply | Quote

Ah, I see what happened. You imported the xml file and Access did the best it could to translate the contents into tables. What file type did you specify? If I import an XML file in 2002 and specify XML, I get a table. Were you using a text type? Were you trying to import from an XSL file?

Reply | Quote