what is the syntax for join on Access table and Sql server table

[cmmrfrds]

I need to join an access 2000 table to an sql server table using ADO. Here is my select string

Sql string = UPDATE cnn.CaseActivity INNER JOIN pubCNN.dbo_CaseActivity ON cnn.CaseActivity.caseActivityID = pubCNN.dbo_CaseActivity.caseActivityID SET pubCNN.dbo_CaseActivity.caseActivityCaseNotes = cnn.CaseActivity.caseActivityCaseNotes WHERE cnn.CaseActivity.caseActivityID = 36137)

The pubCNN is the Sql server connection
The cnn is the Access current project connection

Here is my call
'-- Open the the Activity table
RSMT.Open SqlString, , , adOpenKeyset, adLockOptimistic

The above syntax does not work.

 

[PhillCart]

Which table are you updating, SQL or Access?
Try,
UPDATE SQLTable
SET SQLTable.field = AccTable.field
FROM AccTable
INNER JOIN SQLTable ON AccTable.field = SQLTable.field
WHERE AccTable.Field = 36137

If you're updating the Access table switch the references to SQLTable and AccTable around.

Not sure if you can use the actual connection in the statement though. If the SQL table isn't alreay linked into Access you may have to do that prior to executing the statement.

 

[cmmrfrds]

Thank you. Linked tables will work but I thought it would be cleaner to do it all in code.

I am hoping to do the joined sql without linking the sql tables. I have 2 access programs prog2 is where I will update tables to be downloaded to a laptop. prog2 tables are linked in prog1, which has logic to read sql server tables and load the content into prog2 tables. The laptop user downloads prog2 and does their work, then they upload prog2 to the server. At this point I wanted to avoid trying to link prog2 tables back to prog1 since this would be a new copy of prog2. Prog1 would then update the sql server tables with new content.

I thought that one could prefix the table with enough information to identify the server,
such as, servername.databasename.dbo.tablename and then do the update in one sql statement. How to do this??