How to put ODBC data into a table?

[Air_Cooled_Nut]

First, I'm very handy with VBA for Excel but basically a beginner when it comes to VBA for Access.

I have established an ADODB connection and can pull data with VBA in Access 2003 (XP OS). I have the following code:

...
Set objRecordSet = objCommand.Execute  'Pull the record set

'Test
Do Until objRecordSet.EOF  'Go thru each record until we hit the EOF marker
    For i = 0 To objRecordSet.Fields.Count - 1  'Loop thru every field in the record
        Debug.Print i & "=" & objRecordSet.Fields.Item(i).Name & ", " & objRecordSet.Fields(i).Value
    Next
    objRecordSet.MoveNext  'Move to the next record
Loop
...

This works and I can see the fields in my Immediate Window.

Okay, so how do I get this data into an actual Access table? Either creating a new table or -- better yet -- appending the data into an existing table?

My goal is to create a table at the beginning of the month and populate it at the end of every day w/data from the ADODB data source. Once the month is over a new table is created and the process repeats. TIA!

 

[pbaldy]

Well, if you are linked to the ODBC data, the simplest way would be to execute an append query:

INSERT INTO AccessTable (Field1, Field2)
SELECT Field1, Field2
FROM ODBCTable

An alternative keeping with your current setup would be to open a second recordset on the target table, and use the AddNew method within your loop to add each record.

I have to point out that proper database design would not be to have a new table for every month. You'd have one table with a date field to differentiate months. That way querying across months would be a snap. What you're describing is "spreadsheet" thinking.

 

[Air_Cooled_Nut]

Hehehe, you're right Paul, ya got me! It's been YEARS since I've had to perform proper database design. Thanks for the reminder

 

[boblarson]

Hey Toby - what part of Portland are you in? I am out near Troutdale (although I work in Tigard).

 

[Air_Cooled_Nut]

Hey Toby - what part of Portland are you in? I am out near Troutdale (although I work in Tigard).

Beaverton. Kinda between Tectronix and Nike. That's one heckuva commute you got!

 

[Air_Cooled_Nut]

Just a note, I'd still like to know how to do this (my original question). It's very possible this could grow and I'd like the know-how.

 

[pbaldy]

Did the 2 methods I suggested not work, or do you need more info?

 

[Air_Cooled_Nut]

Did the 2 methods I suggested not work, or do you need more info?

Your first idea is good but I want to perform all the operations w/in VBA. I really don't have much idea how to do it so, yes, I would appreciate more info, particularly on your second suggestion of opening a second record set.

I haven't done a search yet but any recommendations on books for Access VBA? I'd rather reference a book opposed to bothering you guys with what I'm sure is simple stuff. A few years back I got "Excel 2002 Power Programming with VBA" by Walkenbalk and LOVED IT! Very helpful about getting into Excel VBA and I still reference it from time to time. I'm not looking into getting a bunch of Access books nor do I want to become an Access expert but I'd like to be a little more up on the expertise curve than a newbie

 

[pbaldy]

It's not specifically VBA, but the reference I like best is the "Access xxxx Developer's Handbook", where xxxx is the version. The code within the loop (I typically use DAO, and this assumes you've already opened a recordset on the Access table)

        rsAccessTable.AddNew
        rsAccessTable!FieldName = objRecordSet!FieldName
        ...
        rsAccessTable.Update

 

[Air_Cooled_Nut]

Thanks Paul. Your code, along with the Help VBA example for .AddNew, I figured this out (it works!):

Do Until objRecordSet.EOF  'Go thru each record until we hit the EOF marker
    rsTable.AddNew  'Sets up a spot to add a new record in local designated table
    For i = 0 To (objRecordSet.Fields.Count - 1)  'Loop thru every field in the pulled record
        rsTable.Fields.Item(i) = objRecordSet.Fields.Item(i)  'Put pulled data into new record
    Next
    rsTable.Update  'Update (commit appends to) our table
    objRecordSet.MoveNext  'Move to the next record in data pull
Loop

This assumes that the table being updated has the same # of fields as the table the call is being made to.