I have two tables in my Access database that I want to be able to
synchronize by pressing a form button. I think it can work, but I am
not sure how. I am trying to use a single query to do this.
The first table, and more updated table, is called tbl_hours_new. This
table is a linked table to an ODBC conduit to a MySQL database on the
web. (I have full access to this database).
The second table is called tbl_hours_Palm1. This table has all the same
fields and primary keys etc.
What I want to be able to do is get a query to read each record on the
first table, using id as the key, and do an update or replace on the
second table for each record whose id matches. (There is method to my
madness here. I am trying to use another application for my Palm that
won't sync to ODBC or SQL databases so I have to sync to Access and
then write a script to dump it to the live database.)
I have written a SQL statement, but I have done something wrong. I
can't figure out what it is. Access's method of SQL is a bit different
from the MySQL syntax with which I am familiar. The SQL statement is
below.
UPDATE tbl_hours_new SET
[Date] = (Select `Date` from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id),
Employee = (Select `Employee` from tbl_hours_new where
tbl_hours_new.id = tbl_hours_Palm1.id),
Job = (Select `Job` from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id),
Hours = (Select `Hours` from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id),
PalmID = (Select `PalmID` from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id)
WHERE exists (Select id from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id);