linking to mysql server with Access

Is it possible with VB Access code to link to MySQL server database tables instead of using preset DSNs?

By VB Access code, I assume you mean VBScript / ADO? You can connect to a MySQL Server with the following connection string:

"DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=; UID=;PWD=; OPTION=35"

of course you would change those parameters to match your database.

Thank you for reply. My problem is that I can connect as you suggest, but so far I have been unable to write code which replaces the DSN table linking and which will enable me to carry out insert/delete functions.

DSN's allow you to write to a db? Are you getting an error?

Thanks for your reply. I am trying to avoid using DSNs. I can connect to the MySQL database using ADO and can read the data. I need to be able to delete/insert and am having trouble.

Hmm - you might have your file permission set incorrectly - does the mysql users have write permissions
Also, check the DSN setup options - i recall a few years ago there being a setting for "read only", and it was set by default... very weird.

If u don't want to go down the DSN route (which is slow) - your better off using a a "DNS-less" connection, or, even better, OLEDB (the fastest DB connection method)

more info:
http://www.activeserverpages.com/learn/dbopen.asp
http://www.4guysfromrolla.com/webtech/042599-1.shtml

Oh I wasn't aware that you were unable to modify the database. As Stumpy said, you need to check the MySQL permissions for the user that you're attempting to log in under.

I'm beginning to think the problem is that the MySQL ODBC does not put installable ISAMs in the registry. I've had a look and there do not appear to be any. Consequently when I try to link I get a 'no installable ISAMs' message.

Does this make sense? and am I missing something? If the DSN works I would have though that a DNSless connection should be possible on the recordsets as well as the main connection.

When you set up a dsn, does the option appear for MySQL in the first frame where yo uselect the database type? If so, then the system has an ISAM installed. You can try going to mysql.com and downloading MyODBC...

Thanks for help. I have downloaded the latest MySQL ODBC for Windows XP but on inspecting the registry there does not appear to be ISAM support. The Microsoft supported databases show ISAM support. I am new to MySQL and I am puzzled that this particular issue does not seem to be covered in the literature.

As you know unless one can link programatically without using DSNs, it is necessary to set each client up and it is difficult to switch MySQL databses 'on the run'.

i'm short of ideas how to get around the problem.

http://www.mysql.com/products/myodb..._is_MyODBC_3.51

apparently you DO have to use a DSN. sorry about that =-(