Access VBA query to SQL Server

Question

Hello experts I'm having trouble in my update query from SQL Server. Running first a select query then pass the result to currentdb.execute (to update the table of the access file currently using), using Access vba I'm not doing it right. I really hope you could help me. Maybe you guys know much better way to run my procedure:

connect to sql server 2008, run select query.
pass the result of select query to an access database execute command (or if you have a better idea) to update a table in the current access file that is using.

The error I'm getting to the code is Type mismatch and highlighting .OpenSchema.

These is part of the code that I made wrong (and I really have no idea how to do this).

 dbObj.Execute ("UPDATE ACCESS.tbl_Name RIGHT JOIN " & _
     conn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "SQLSVR.tbl_Name")) & _
         " ON ACCESS.tbl_Name.FieldName_access = " & rst!FieldName_sqlsvr & _
             " SET ACCESS.tbl_Name.FieldName_access = " & rst!FieldName_sqlsvr)

These is the whole code.

 Option Compare Database

 Sub LocalSQLServerConn_Test()

 Dim dbOjb As DAO.Database
 Dim strDBName As String
 Dim strUserName As String
 Dim strPassword As String


 Set dbObj = CurrentDb()
 Set conn = New adodb.Connection
 Set rst = New adodb.Recordset


 strDBName = "DataSet"

      strConnectString = "Provider = SQLOLEDB.1; Integrated Security = SSPI; " & _
           "Initial Catalog = " & strDBName & "; Persist Security Info = True; " & _
                "Workstation ID = ABCDE12345;"

      conn.ConnectionString = strConnectString

           conn.Open


 strSQL = "SELECT DISTINCT SQLSVR.tbl_Name.FieldName_sqlsvr FROM SQLSVR.tbl_Name"


 rst.Open Source:=strSQL, ActiveConnection:=conn, _
      CursorType:=adOpenDynamic, LockType:=adLockOptimistic
    
 If rst.RecordCount = 0 Then
      MsgBox "No records returned"
 Else
      rst.MoveFirst
           
           Do While Not rst.EOF
      
 dbObj.Execute ("UPDATE ACCESS.tbl_Name RIGHT JOIN " & _
      conn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "SQLSVR.tbl_Name")) & _
           " ON ACCESS.tbl_Name.FieldName_access = " & rst!FieldName_sqlsvr & _
                " SET ACCESS.tbl_Name.FieldName_access = " & rst!FieldName_sqlsvr)
                        
      rst.MoveNext
            
           Loop

 End If

 conn.Close
 rst.Close
 Set dbObj = Nothing

End Sub

If you're just trying to update ACCESS.tbl_Name, couldn't you just drop the RIGHT JOIN altogether and have "UPDATE ... SET ..."? (I'm not of the experts on this matter.) — Richard

Razvan Socol Razvan Socol · Accepted Answer · 2013-06-20T18:22:26

You should add a linked table (or a pass-through query) to get the data from SQL Server, create an Update Query in your MDB, using a JOIN to update all rows at once (your can use the query designer for this part) and then execute that query using CurrentDb.Execute.

Access VBA query to SQL Server

1 Answers