Update records in two tables based on most recent

[tuxalot]

Hi guys,

I have a local Access table (tblResponses) and a MySQL table (tblAndroid) connected to my database via ODBC. I currently use two queries to update data in these tables based on the most recent timestamp (SQL table timestamp being AnswerTIme and local Access timestamp being AnswerTimeLoc). An Android app I am developing updates the SQL table and the Access table pulls updated data from this table. I am learning that I may be able to update both tables using a single query but I do not know how to write this. here are the queries I am using:

To update from local Access table to SQL table:

UPDATE tblAndroid
INNER JOIN tblResponses
ON (tblAndroid.QstnID = tblResponses.QstnID)
AND (tblAndroid.RspnsID = tblResponses.RspnsID)
SET tblAndroid.Rspns = [tblResponses].[Rspns], tblAndroid.RspnsComment = [tblResponses].[RspnsComment], tblAndroid.FlagTag = [tblResponses].[RepeatRec], tblAndroid.ImgPath = [tblResponses].[ImgPath], tblAndroid.AnswerTIme = [tblResponses].[AnswerTimeLoc]
WHERE (((tblResponses.RspnsID) Like [forms]![frmSyncAndroid]![txtRspnsID]) 
AND ((tblResponses.AnswerTimeLoc)>[tblAndroid].[AnswerTIme]));

To update from SQL table to Access table:

UPDATE tblResponses
INNER JOIN tblAndroid
ON (tblResponses.RspnsID = tblAndroid.RspnsID)
AND (tblResponses.QstnID = tblAndroid.QstnID)
SET tblResponses.Rspns = [tblAndroid].[Rspns], tblResponses.RspnsComment = [tblAndroid].[RspnsComment], tblResponses.RepeatRec = [tblAndroid].[FlagTag], tblResponses.ImgPath = [tblAndroid].[ImgPath], tblResponses.AnswerTimeLoc = [tblAndroid].[AnswerTIme]
WHERE (((tblAndroid.AnswerTIme)>[tblResponses].[AnswerTimeLoc])
AND ((tblResponses.RspnsID) Like [forms]![frmSyncAndroid]![txtRspnsID]));

So as you can see these are the same, just moving data the opposite direction.

Any insight is appreciated.

Thanks!

 

[Rx_]

Haven't done this, but wouldn't it be something like this?

"UPDATE table1, table2 SET table1.field1=newvalue1, table2.field1=newvalue2 WHERE table1.field2=existingvalue1 AND table2.field2=existingvalue2;"
Take note of the commas between the object location values. You will need to replace all of the table names, field names and values with the information relating to your database.

Or for Access 2010 the new Triggers
http://blogs.office.com/b/microsoft...ess-2010-data-macros-similar-to-triggers.aspx
However there are limitations to the Linked Tables.

From a source I feel is correct (until proven incorrect of course)
In MS-Access you can update 2 tables in a single query. But it is not on MSSqlServer.

If you created a view (or in Access a query with both the attached SQL Table and the Local Table, can you update a record?
​

 

[tuxalot]

Hey Rx - thanks for the response. Not sure data macros would work per the FAQ on the page you sent:

Link Tables. Data macros are not supported on link tables;

If I create a query like you mention, I cannot update records.