UPDATE a linked access table with value from a ODBC linked SQL table

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Hi, Ive been searching the net and this group for this problem,
without any luck. I found some posts with a similar problem, but I
could not get it work for me.

Task: I want to update a field in one linked table (linked from
another access db) with values from another linked table (MSSQL7).

When I do this with a standard update query, everything is ok, but
with vba code, its not ok.

I have tried some different args for the OpenRecordset, but only to
get various error codes.

Here is a piece of my code...

Dim dbsCurrDB As DAO.Database
Dim rstSQLQueryResult As DAO.Recordset
Dim strSQL As String

strSQL = "UPDATE LinkAccessTable" & _
" INNER JOIN LinkODBCTable ON LinkAccessTable.PartNo =
LinkODBCTable.PartNo " & _
" SET LinkAccessTable.Balance = LinkODBCTable!Balance ;"

Set dbsCurrDB = CurrentDb
Set rstSQLQueryResult = dbsCurrDB.OpenRecordset(strSQL)

Do I have to split this one up, and create two record sets, one for
each table, and then make my update from that? Is it possible?
 
Back
Top