Querying the Database

I've had to do this a few times... and of course, it depends a lot on the data you're trying to work with. First, Access is VERY sensitive to the way you index data. Do what you can to optimize queries in terms of what data you pull in and how you grab it. If you can get the indexing right, then it can make all the difference in the world. I'm not necessarily an expert on this, but here are couple of things that have worked for me. First, in some cases I periodically pull data into access using php as kind of a cache, generally once a night. (Access DB's can be added as odbc data sources, same as Mysql...) From there, it becomes a lot quicker to spit out data, because it doesn't have to query the external db. When I put the actual Access db frontend right on people's desktops instead of on a server, I've generally noticed a huge speed boost, even when querying an external db. The only weirdness there is that you have to install the mysql drivers on the client machine and set up the connection appropriately... which can be a pain in the butt if you're still fiddling with things. At any rate, this is a good excuse to learn how to program. PHP.net has some good docs/examples on http://us2.php.net/manual/en/function.odbc-exec.php -- it's not really any different than querying a mysql db. On the excel front, you can use COM to grab data from a native excel doc -- again, there are http://us2.php.net/manual/en/ref.com.php as to how to do it. Some programming/tweaking is required, but it's not too evil. (This assumes you've got a copy of excel on your server.) An alternative, if you want to intervene manually and do a quick conversion every day, is to take the excel doc, save it as a tab delimited file, and import it that way...

By the way... if you have specific questions as to how to do something, let me know and I might be able to point you in the right direction. Recently, we had to convert an old foxpro db into a lamp-based db, and while the process was a little quirky, it was well worth the effort. At least for us. Now, I just maintain a small cadre of access db applications that I've distributed to people who need to spit out various kinds of formatted reports, but all the data itself lives in a mysql db.

Some problems I ran into: -When I imported the old Access database into mySQL, it made one of the fields memo, which shouldn't be. -It added all sorts of weird indexes in places where I didn't want indexes. -It wouldn't allow me to import data into an existing table. Is there a really good GUI tool for Windows for database manipulation? Note, all of this stuff in running in a Windows environment.

phpMyAdmin is a reasonable tool for database manipulation (it's just a directory full of php scripts, so easy to install). I don't think it has to be installed on the same server as MySQL, so if you have PHP running on your own computer you could run it from there. There are probably GUI tools too.

How did you do the import? Generally, I'll write my own to ensure data integrity... but yeah, I've been doing this stuff for awhile. Here's my process in a nutshell: 1) Build the db the way you want in mysql. Set up the fields to the appropriate type and whatnot. 2) Read the data out of access using php/odbc. You can just dump the data to the screen to make sure you're getting the right stuff that mysql expects (you'll have to reformat the data, typically...) 3) Compare the incoming data to what's already in the mysql db... If it's there already, continue on... otherwise... 4) Insert the data into the mysql db. Make sure it doesn't choke on the data... if it does, go back to step one and make sure the incoming stuff is formatted properly. (Remember, quote marks need to be escaped with slashes, for instance, not an extra quote mark.) I'm sure there are gui's out there, but I'll second cb's recommendation of phpMyAdmin. While it's not a true windows gui, it does pretty much everything one might want... I generally prefer it to the mssql equivalent.

I have resolved many issues, including speed. However, I have a new problem: I use Microsoft Access to connect to the mySQL server using myODBC. However, when I close Access and open it back up, it loses the connection to myODBC/mySQL and I have to remove the linked table and re-add it in order to have it work. Any ideas on how to resolve?

It seems like it should be going faster than it is, but doesn't seem to be going any faster than Access did. You can (and should) tweak the settings of mySQL to balance out the size of the database with the size of the user pool. Unfortunately, I can't help you with specifics since I generally go with the default installation, but I'm sure you'll be able to get some advice on, say, experts-exchange. http://books.slashdot.org/article.pl?sid=04/12/02/2029208&tid=221&tid=198&tid=6 on Slashdot -- perhaps some of the 5-Informative comments can point you in the right direction. The other thing to consider is that MySQL (usually, but not always) locks the entire table when doing row inserts. From their http://dev.mysql.com/doc/mysql/en/Internal_locking.html: Currently, MySQL supports table-level locking for ISAM, MyISAM, and MEMORY (HEAP) tables, page-level locking for BDB tables, and row-level locking for InnoDB tables. [...] To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB.Is there a really good GUI tool for Windows for database manipulation? Unfortunately, not really. phpMyAdmin is absolutely terrible in terms of UI, but then, it DOES do the job and has never bugged-out on me, which is better than nothing. we had to convert an old foxpro db into a lamp-based db I had to convert a HUGE FileMaker Pro database to mySQL. I don't think I've swore more for longer periods of time. The FM author seems to think it's the bees knees, so he adamately refuses to support, say, FM->Access conversion. When that project was through I felt like tracking the guy down and beating him over the head with a mySQL desk reference or two. Anyway, if the original author is really concerned about multi-user speed with updates, they might want to consider PostgreSQL. It even has a semi-decent http://www.pgadmin.org/screenshots.php.

In general, ODBC drivers run slower than native drivers. That's possibly one of the factors here.

Re: a Windows admin GUI, last time I was mucking about with MySQL I downloaded a tool called MySQL Control Center (I've got 0.92 beta) that gives me a reasonable Windows admin interface. I can view all my databases (all one of them), browse the objects and run SQL scripts against them. I don't follow MySQL development so I don't know where the tool is now, but I got it from mysql.com, so perhaps it got rolled into the http://www.mysql.com/products/administrator/ or something.