CODEWITHSUNDEEP
sql-serverms-access
YellowLarry
YellowLarry

Reputation: 429

Copy SQL Server record to Access local table

SQL Server data will be queried by ADO recordset. Currently, I use a loop to move server data to Access local table for further process.

How to eliminate the Do-Loop? Local table has exactly same columns as the server table while local table have more columns than server which will not be filled up data by the loop.

Dim rsServer As New ADODB.Recordset
Dim rsLocal As New ADODB.Recordset
Dim fField As ADODB.field


str = "SELECT * FROM tableServer WHERE ....."
rsServer.Open str, dbServer, adOpenStatic, adLockReadOnly, adCmdText

str = "SELECT * FROM tableLocal"
rsLocal.Open str, dbLocal, adOpenStatic, adLockOptimistic, adCmdText


Do Until rsServer.EOF
    rsLocal.AddNew

    For Each fField In rsServer.Fields
        rsLocal.Fields(fField.Name).Value = rsServer.Fields(fField.Name).Value
    Next

    rsLocal.Update
    rsServer.MoveNext
Loop

Upvotes: 0

Views: 434

Answers (1)

Ove Halseth
Ove Halseth

Reputation: 461

If you connect the SQL Server table as a remote table then Access will not differentiate between that an a local table. So you will be able to run a insert query on the local table with data from the remote table.

Upvotes: 1

Related Questions