temporary tables and mysql

[icemonster]

hello i have a mysql back end.

im trying to set a table within the front end so that when users open a record or create a record on the listbox it would first get entered into the table then queried out to the mysql back end when saved.

any good ideas or sample i can look at for this one? thanks.

 

[sparks80]

Hi,

Have you installed the ODBC driver for MySQL, if not I would suggest you do that first. The ODBC driver is called MyODBC

I would then use ADO to connect to the MySQL database using this as the connection string, assuming the MySQL database is stored on the local machine

Dim cn As ADODN.Connection
cn.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase; User=myUsername;Password=myPassword;Option=3;"

Is that helpful as a starting point, or do you need more assistance with the code?

 

[icemonster]

i already have the connection figured out, what i want to know is how to be able to append temporary tables in ms access to mysql backend. the thing though is that when i append each row of record i need to retrieve their autonumber because the way my tables are set need to have that kind of sql chaining.

 

[mdlueck]

Downloading records from the SQL BE DB to cache them in the FE DB is a good idea to boost performance. Connect to the BE DB, download some records into a FE temp table, then disconnect allowing the client application to view the record(s).

If later they wish to edit the record, then I would lookup that one record by unique ID from the BE DB AGAIN to make sure someone else did not change it and thus the record has become stale.

Finally when checking in records via an UPDATE query, I check the record back in passing in the ID and LASTSTAVETIMESTAMP of the checked out record. I use the SQL standard CURRENTTIMESTAMP to obtain the server time that the INSERT/UPDATE occurs. If a record can not be found matching the ID/timestamp cols then I toss an error that "perhaps someone else changed the record / rollback / edit again". Totally avoids record locking!

An application I am currently working on for a client works extremely well over a remote VPN connection. Client/Server Rocks!

 

[icemonster]

That is a good idea. But I still don't know how to append my tables properly. The way I designed my tables are like this:

tblclient
tblphone
tblclietphone

So the deal is for each for number entered on the temp table for phone numbers I need te autnber retrieved so that I can use that number for the table that will hold both the client's ID and phone number ID.

 

[mdlueck]

When I check out records from the BE DB table and download them into FE temp tables, then I do not add a different key column. The ID key from the BE DB I also download to the FE temp table. The downladed records might not have sequential ID numbers, and that is all right... just that they match the ID's of the BE DB table.