Need help with Access SQL format

[JellyMan]

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.i d =
tbl_hours_Palm1 .id),
Employee = (Select `Employee` from tbl_hours_new where
tbl_hours_new.i d = tbl_hours_Palm1 .id),
Job = (Select `Job` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
Hours = (Select `Hours` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
PalmID = (Select `PalmID` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id)
WHERE exists (Select id from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id);

[Alan Webb]

JellyMan,
Either through OLE-DB or ODBC or some such I'd try running a pass-through
query so the SQL is executed against MySQL and doesn't have to go through
Jet on its way to MySQL. Jet wants an editable SELECT statement before it
will allow updates to the source tables. Also, entities are bracketed in
Jet, not quoted, so [Date] instead of 'Date'. Jet does allow subqueries, so
an alternative might be:

UPDATE TBL_HOURS_NEW SET . . . (SELECT DATE, HOURS, EMPLOYEE, JOB, PALMID
FROM (Select [Date], [Hours], [PalmID] from tbl_hours_new where
tbl_hours_new.i d = > tbl_hours_Palm1 .id) AS DATE_VW INNER JOIN (Select
[Job], [Employee] from tbl_hours_new where > tbl_hours_new.i d =
tbl_hours_Palm1 .id) AS EMPLOYEE_JOB_VW ON
DATE_VW.EMPLOYE E=EMPLOYEE_JOB_ VW.EMPLOYEE AND DATE_VW.JOB =
EMPLOYEE_JOB_VW .JOB) AS UPDATES_VW

or something like this. A select statement per value may not work.
--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS"

"JellyMan" <ca*********@ho tmail.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

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.i d =
tbl_hours_Palm1 .id),
Employee = (Select `Employee` from tbl_hours_new where
tbl_hours_new.i d = tbl_hours_Palm1 .id),
Job = (Select `Job` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
Hours = (Select `Hours` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
PalmID = (Select `PalmID` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id)
WHERE exists (Select id from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id);

[JellyMan]

Thank you for your help Alan. I have attempted to convert the file to a
Pass-Through query and the SQL is the following:

UPDATE tbl_hours_new SET tbl_hours_new.` Date` = tbl_hours_Palm1 .`Date`,
tbl_hours_new.E mployee = tbl_hours_Palm1 .Employee, tbl_hours_new.J ob =
tbl_hours_Palm1 .Job, tbl_hours_new.H ours = tbl_hours_Palm1 .Hours,
tbl_hours_new.P almID = tbl_hours_Palm1 .PalmID
WHERE (Select id from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id)<>False;

Now, when I attempt to run the query, it prompts me for a File DSN ODBC
connection. I have a Machine DSN set up, which is how the table is
connected and linked into the access db. I attempted to choose the same
DSN in Machine DSN and I get an "ODBC -- call failed" error. I am at a
loss. I am a newbie to Access, at least this deep in the programming of
a db anyway, and am not even sure how to begin putting in an OLE DB.

Any help would be very much appreciated.

Andrew