insert or copy recordset from sql server to access database

jmss66 · June 6th, 2011, 03:37 PM

I have two tables with the same field names. One is from SQL Server which I query and have a recordset from and want to insert or copy it into an access database.
How do it do this?

So far I have:

Code:

Do While Not rs_SQL_Element.EOF
rs_Element.AddNew
    Dim i As Integer, sFieldname As String
    For i = 1 To rs_Element.Fields.Count
       With rs_Element
       sFieldname = rs_SQL_Element.Fields.Item(i).Name
       If Not IsNull(rs_SQL_Element.Fields.Item(i).Value) Then
         sFieldname = rs_SQL_Element.Fields.Item(i).Value
        End If
        End With
 
    Next
 
    rs_Element.Update
    rs_SQL_Element.MoveNext
Loop

I can't seem to get the value of the fields from sql database to access.
Please help.
Thanks

Shasur · June 7th, 2011, 07:22 AM

Hi

The following code will be useful for inserting data to AccessDB (http://vbadud.blogspot.com/2009/02/blog-post_07.html)

However, since you are inserting records from another RDBMS, which definitely will be strucutred. Hence you can try using the Import feature of Access. Here you can map the columns, provide necessary criteria

Cheers
Shasur

jmss66 · June 7th, 2011, 08:26 AM

Hi Shasur, I was hoping to loop through field/column names instead of typing them one by one and assigning the value to them. Your example is making me type all of them. There are 350 fields in the table and the other table which I want to copy to has the exact corresponding fields or columns.

What I did in the VB application is select records from table A with various criteria and copy or populate table B with all the records in the result set. That's why I was hoping I can just find a code that basically says...table B = table A resultset.

Thanks,

Shasur · June 7th, 2011, 10:04 AM

Hi

I think the best option for you would be to import it

1) Export your record set as CSV
2) Import the CSV to Access

if needed you can manipuate the CSV in Excel

Regards
Shasur

jmss66 · June 7th, 2011, 10:22 AM

Hi Shasur,
I will go with that logic since no one seems to be able to give me a code or logic to be able to accomplish what I want to do iterating through field names.

Thanks,
jmss66