Update MySQL table with local table data 1

[xentaur]

Hi all,

This is just beyond my reach brain-wise, any assistance greatly appreciated.

BACKGROUND:
Front End MS Access 2003, Back End MySQL.
Pass-through query (QryA) references all data in remote table (TblA).
Local Table (TblB) is created from QryA based upon user-specified criteria. The primary key in QryA (pkey) is included as a criterion.

ISSUE:
Users will update data in TblB via FormA. Once data is updated I want to run an query to update corresponding records in the remote table HOWEVER I do not want to link the remote tables. This is because MS Access insists on loading the entire dataset of a linked table into memory on load/upon linking.

I have successfully utilised the method in this article to compile update strings for individual records with information contained in a form but can't determine how to adapt it to use a local table as the source to update multiple records. The issue appears to be that the ODBC call is trying to locate the local table name on the remote server, thus returning a 'call failed' error. SQL as follows:

UPDATE TblB INNER JOIN TblA ON (TblB.ID = TblA.pkey)
SET TblA.completiondate = TblB.completiondate, TblA.m1 = TblB.m1, TblA.m2 = TblB.m2, TblA.m3 = TblB.m3, TblA.m4 = TblB.m4, TblA.m5 = TblB.m5, TblA.m6 = TblB.m6, TblA.pnotes = TblB.pnotes, TblA.rating = TblB.rating, TblA.rnotes = TblB.rnotes, TblA.kpi = TblB.kpi;

I want to avoid having to iterate through and create a new pass-through update query for each individual record in TblB. Can anyone offer a solution to update a remote table with information contained in a local table using a pass-through query and without having to link the remote table?

Cheers

 

[lameid]

I don't think you can do this... My first inclination is to link the remote table and second is to recursively update the records. Updating iteratively, you could update a recordset, run a series of pass through queries or if My-SQL supports it, send it a large series of update statements.

If your backend was SQL server, I would suggest making the Jet table accessible in the server (the jargon eludes me at the momemnt) and running a pass through query to update from it. Perhaps this is an option for My-SQL?

Although, I am left wondering why you have a local table at all. If it was server side, it would solve your problem.

Maybe Offline recordsets would work with My-SQL and that might be what your looking for.

 

[xentaur]

Thanks lamied

The local table is used given that many users might be accessing the database at one time and they can't all be using the same remote table to house their respective data - the logistics of creating remote tables was too horrendous.

I've had my suspicions confirmed regarding this issue but Doug over at utter access has offered a solution which I'm yet to test but looks like it will do the trick. It is admittedly an iteration through each record (which I was trying to avoid) however significant time will be saved when compared to waiting for Access to link a table with 300,000+ records in it.

Doug's response is Here

Thanks for your interest. I hope the solution works for others too.

Cheers

 

[lameid]

The local table is used given that many users might be accessing the database at one time and they can't all be using the same remote table to house their respective data

Why? I would be inclined to make the default value of a bound field on the form the username...

=Environ ("Username")

That way the username value is stored in the form. Then it is a simple filter to just display the User's record.

 

[xentaur]

Well, that's just brilliant and elegant in its simplicity.

I use environ$ all the time at the front end for security purposes and to dump stuff to the users temp directory for mailmerge and the like - it never occurred to me to use it as an identifier in a remote table. That little gem will save me a lot of programming frustration.

Again, brilliant!

Have a star.

Cheers