MySQL

[Matt_Hirst]

I have been given the task of updating an old databse that I wrote about 2 years ago. Basicly the database updated anumber of linked MySQL tables to allow a group of analysts easy access to the data.

The new/updated database needs to look at a new version of software that uses mysql 5.0. Now I updated the code to use the correct odbc driver and got it to update the linked tables all well and good, but the problem now is that the MySQL database holds 150 odd new tables that need linking in the Access databse and I am wondering what is the best way to do things.

Options:

* Is it better to just have the database auto load a form that lists the available mysql databases on the system the user then picks the database and it autolinks the tables in the access database.

or

*Is it better to start with the database that is half populated and search throught he mysql database and when a new table is found only that table is added?

Questions I have are:

* Is it possible to list the databases that are held on the host server? If so how?

* How do I interogate the database to find out what tables are held?

* How do I create a linked table in code?

Any help would be appreciated.

regards,

Matt

 

[Banana]

Did you mean 150 tables in one database or 150 tables over more than one databases. Based on your questions it sounds like the latter, but if it's former, please let us know- the below may not apply.

First thing to understand is that ODBC, by definition can only work with one database at a time. To use more than one database you need a separate ODBC connection. This will concur extra overhead, and if you had several database, I would doubt your DBA will be very excited at the idea of your clients consuming tens of connections.

A workaround is to get data via server side processing... stored procedure for instance.

As for question about what linked table to populate.... we would need to know more about your model here to give good advice. Why do you think you need to work with more than one database? Why would have the choice to be done at runtime, not design time? This is unusual requirement AFAIK.

For getting list of databases, I've not tried this but I know from experience I can do a passthrough query and call SHOW ERRORS to get the table with all recent errors. Therefore, a passthrough query with SHOW TABLES should work. Try it.

Finally search for "DSN-less connection"; this is an example of how to do it in code. I wrote an FAQ article about this in the FAQ section of this forum. See if this helps.

 

[Matt_Hirst]

Banana,

Thanks for the reply.

The database I am connecting to has 370 tables, it is the output from a rather large model. It is held on a PC and depending on how many times the model runs depends on how many databases there are on that computer.

Initially I am wanting:

A. To have a list box or combox populated with the MySQL databases on that system.

B. Once a database is selected the tables are linked to a blank Access database.

c. After the Access database has been populated all of the tables are gone through one by one and the relationships created. Primary Tables have unique Indexes and foreign tables contain the same field but with differnt names e.g. Primary Table Customer Details with Fields Cust_Numb, Name, etc, Linked table Acoounts Cust_Pkey, Order Date, etc, etc.

Thanks for the definiton of ODBC as that made sense but I dont understand "A workaround is to get data via server side processing... stored procedure for instance.".

Any help would be appreciated.

regards,

Matt

P.S. One of the problems I am hitting is making a recordset of a table called day, I assume day is a reserved word so any help on creating a recordset with reserved words would be appreciated.