Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Save' Recordset As New Table (2003 SP1)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Save' Recordset As New Table (2003 SP1)

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

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Save' Recordset As New Table (2003 SP1)

    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.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: 'Save' Recordset As New Table (2003 SP1)

    Why not just use an Append query?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Save' Recordset As New Table (2003 SP1)

    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.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Save' Recordset As New Table (2003 SP1)

    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,

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Save' Recordset As New Table (2003 SP1)

    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,

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Save' Recordset As New Table (2003 SP1)

    Sorry, but what do you mean by "the resulting tables weren't meaningful"?
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Save' Recordset As New Table (2003 SP1)

    Sorry I wasn't more explicit. File...Get External Data...Import... <xml file> 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.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Save' Recordset As New Table (2003 SP1)

    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?
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •