Hi everyone, connected MySQL database to MS Access via ODBC.Now, I can work in access and append queries to the MySQL DB (if it is less than 50MB - Limit set by my Provider).However, I want to automate the logging process, (Username, password) so It will run smoothly without promting me for anything.Is this possible?

Yes, this is very possible.On my last post in this thread, I gave a sample connecting to a MySQL database. While this sample uses a custom Log In form, you can just automate the thing behind the scene by using the code in the module in the sample. Do be aware, though - if you don't want to have to type something to login, that may mean you need to store password and it's never safe to store a password. It's OK if your MySQL database is accessible via only local network and you trust your employees but that is a hole you need to know about.HTH.

Below is the code that I found on your post. I modified it a bit. I don't understand what "opts" is for?lso, do I need the last paragraph? I just want to connect to MySQL automatically, so is the 1st and 2nd part of this code sufficient?

Options: They determine what optional features you want to enable/disable. At a minimum, you must specify 2 (Return Affected Rows instead of Matched Rows) else Access may behave oddly now and then.Documentations of the Options can be found here. If you look in the standard moduel in my sample, I wrote an enum that corresponds to the options to make it easy to figure out what options you want to enable/disable.Yes your code should work well enough if ADO is the only thing you need.Do beware: You may not actually need the "FOR UPDATE" clause - that was for the other user who needed restrictive locking. FOR UPDATE can be expensive, and is very restrictive - nobody else can even read the rows! Also, it won't work if the table isn't driven by InnoDb. Do consider moving your Connection object out and make it global so you don't have to re-open your connection every time you need a new Recordset. Connection are quite expensive so it's good to re-use it if you can.

Hi BR, could not find the link. the page could have been removed.-I changed from private to public sub now. Is this how I make my connection reusable?-I also removed the FOR UPDATE clause, since I don't need it and you said it was expensive.

Sorry about that. Does this link work for you?Well, public is one way but you're still creating a new connection every time you call that sub so it's not really re-use.Here's how I typically handle this:

Yes, that link does work for me! think I will put 0 for options (which is the default). I read all the other options but I don't understand them at all.So, to recap:I will use your first code and put it in a module in my Access DB.Then If I want let's say to run my Append Query to the MySQL table, I am not sure what the next step would be... Will Access go fetch that code in the module or do I have to call it by running it in a macro?Thanks, I am quite new at this ODBC thing. I usually run queries by using macros so that 1 click can run 10 queries one after the other...

At a minimum you should at least specify 2. This is to necessary for Access to work well with MySQL. The rest are optional but you do really need to have the 2.Okay. Two very important things.1) If you want to use linked tables, Access saved queries, etc. you are actually using DAO. If you want to use ADO, you must supply the SQL all by yourself. While you can bind forms to an ADO recordset, you have to do that in VBA; you couldn't just say, open form in the design view, go to Data tab and change the Recordsource because that is using DAO, not ADO. 2) There is a reason why I use ADO only 5% of time: DAO already does a great job of working well with ODBC sources and by sticking to DAO, I can continue to use Access objects and techniques that are not only familiar but also requires less coding. I happily reach for ADO when I need something that depends on specialized features available only in backend... writing in its native dialect or to run a stored procedure that I can then bind to the form *and* update the resultset. But most of time, I don't really need that, so DAO covers the 95%. So if you want to be able to use your existing Append Query, you may find it much easier to stick with DAO. To use ADO for the same operation, you would actually do something like:

Basically, where you have Append, Update, Delete queries, you use Execute method. When you do a SELECT, you do a Open. Do note that you can't necessarily just copy the SQL from your original Access query to ADO's statement because you now have to use native dialect. So if we had a Access Delete Query:

CODE

DELETE * FROM my_tbl;

and put it in VBA:

CODE

MySQLConn.Execute "DELETE * FROM my_tbl;"

This would fail because MySQL does not recongize the form "DELETE <column-list>" that Jet uses to presumably makes it easy to view the effect of deletion. So you have to remove the "*" to get it to work. There are more but that's one idea. I hope this helps...

Thanks Banana,You really made it clear ADO vs DAO. I was already planning to go with ADO (saved queries, Access objects, etc).However, I am still not clear as to what the process is to automate the login process. I have the code that you provided, do I still need that code even if I use DAO?

The sample contains both code to support both DAO and ADO.If you wanted to use DAO exclusively, you probably only need the FixConnections and GenerateIndex sample. If you want to support ADO as well, just import the entire modODBCConn module and change the parameters. In the startup, you would call FixConnections, meaning that your linked tables will get refreshed every time it's started up. This may not be strictly necessary but that way, the code is relatively straightforward in terms of keeping the links functional without having to prompt the users to fix the links or to check for changes to the links, etc.HTH.