insert into dilema

B

Bre-x

Hi

I have a local table (Temp) that is made of 24 columns of diferents data
types, and it will contain one single record.

Using VBA, I would like to update a record on a MYSQL server. The link is
the jobnumber

What i use is a UPDATE Query ( i have linked my jobs table)

UPDATE jobs INNER JOIN Temp ON jobs.jobnumber = Temp.JOBNUM SET jobs.SONUM =
Temp!SONUM, jobs.CUSCOD = Temp!CUSCOD, jobs.SODESC = Temp!SODESC, jobs.CUSA1
= Temp!CUSA1, jobs.CUSNME = Temp!CUSNME, jobs.CUSA2_1 = Temp!CUSA2_1,
jobs.CUSA2_2 = Temp!CUSA2_2, jobs.CUSCTY = Temp!CUSCTY, jobs.CUSST =
Temp!CUSST, jobs.CUSZIP = Temp!CUSZIP, jobs.CUSCNT = Temp!CUSCNT,
jobs.CUSATT = Temp!CUSATT, jobs.SHPCTY = Temp!SHPCTY, jobs.SHPST =
Temp!SHPST, jobs.SHPZIP = Temp!SHPZIP, jobs.SHPNME = Temp!SHPNME, jobs.SHPA1
= Temp!SHPA1, jobs.SHPA2_1 = Temp!SHPA2_1, jobs.SHPA2_2 = Temp!SHPA2_2,
jobs.SHPATN = Temp!SHPATN, jobs.SHPVIA = Temp!SHPVIA, jobs.SHPCNT =
Temp!SHPCNT, jobs.CUSORD = Temp!CUSORD, jobs.ORDDTE = Temp!ORDDTE, jobs.FOB
= Temp!FOB, jobs.SLSP = Temp!SLSP;

There must be another way rigth?

I dont like to "Link" tables and I allways try to use VBA code as much as
posible.

Regards,

Bre-x
 
J

John Vinson

I dont like to "Link" tables and I allways try to use VBA code as much as
posible.
Click to expand...

ummm... why? Masochism?

You're using a program (Access) which is focused on the use of
Queries. It's a relational database environment.

Sure, you can update in VBA code - open a Recordset on each table, use
MoveFirst and MoveNext to step through the source recordset, FindFirst
to locate the matching record in the output recordset, etc. etc.

Or you can run an Update query, which will be MUCH faster (for an
optimized query with proper indexing), simpler to write, and easier to
use. Your choice!

John W. Vinson[MVP]
 
Top