Update Access 2007 table from SQL Server 2005

[jvpark]

I am a beginner and stumped attempting what sounds like a simple task: Repopulating Access 2007 reference tables (static) from SQL Server 2007.

I am using VBA and ADO connection to create a recordset of the SQL Server table(s) with no problem:

I can't find out how to use this recordset to replace all rows in the local (CurrentDb) table:

I get error that CurrentDb does not recognize the ADO RecordSet "rs". I have tried many variations [commented out below] to bulk load new data into an empty tblASA. Shouldn't this be easy? Does it require looping through the recordset?

Thanks.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
strConn = "Provider=SQLOLEDB;Data Source='sql2k5.....';Initial Catalog='SQL2005...........';User Id='Int.......';Password='...........';"
Set cn = New ADODB.Connection
cn.Open strConn
' Find out if the attempt to connect worked.
If cn.State = adStateOpen Then
MsgBox "Welcome to IntraOp!"
Else
MsgBox "Sorry, no IntraOp connection."
End If
Set rs = New ADODB.Recordset
rs.Open "SELECT * From dbo.tblASA", cn, adOpenStatic, adLockOptimistic
MsgBox "rs.RecordCount = " & rs.RecordCount
'With rs
'CurrentDb.Execute "INSERT INTO tblASA SELECT * FROM RecordSet("rs");"
'CurrentDb.Execute "INSERT INTO tblASA SELECT * From dbo.tblASA IN rs;"
cn.Execute "INSERT INTO tblASA SELECT * FROM CopyFromRecordSets(rs);"
'cn.Execute " INSERT INTO tblASA SELECT * FROM rs.Recordset", , 129
'cn.Close
'End With
' dbs.Execute " INSERT INTO Customers SELECT * FROM rs;"

If Err.Number = 0 Then
MsgBox "Ran SQL"
End If

Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

 

[LPurvis]

Hi, welcome to the forum.

I'm afraid you can't expect the database engine to be aware of an API's objects (or each other's objects, i.e. DAO methods referencing ADO objects).
ADO is just an example of one particular VBA referenced library, and the recordsets it returns are accessible by VBA and extended into Access support as far as binding to forms, controls etc.

However the database engine requires disk objects in the format it is designed to read or an ODBC driver to expose the non-native format. As the recordset is local and in memory there won't ever be an ODBC driver capable of exposing that (he says confidentally, not knowing if this post will be around in 15 year when db engines are reading minds lol ;-)

If you want to fill a local, native table using a recordset currently in memory then you must indeed do so in code, iteratively (one row at a time).

To perform the action as a set based operation (i.e. using the database engine) then you'd be better off using a passthrough query.
Don't get hung up on passthroughs using ODBC being necessarily inferior to your ADO OLEDB connection. They're a common and efficient tool (a read-only resultset adds to that efficiency and is perfectly adequate for mass append as you require).

See how you get on.

Cheers.

 

[jvpark]

Thanks,
I have automated the pass-thru queries and will test on another clients application.

The next step is to automate the pushing (or pulling) of Access 2007 table updates to SQL Server 2005 for clients to pull (using the pass-thru queries) into their Access 2007 apps.