home
Home button Search button Dr. Hain buttonClinic website Information for Dizzy Patients Fun Opinions (rants/raves)

Dr. Hain's Rants/Raves has helpful information about things that Dr. Hain has discovered by trial and error.

Microsoft Access-- painful and frustrating

Access is a very frustrating piece of software, that has evidently lost the battle with the free alternative, Mysql. We say this because Access features have shrunk in newer versions, and working with it is very painful. Here are a list of problems.

We think that the better way to go about this is to just use excel for simple database projects, and use mysql for complex projects.

Here is how one moves data out of Access into Mysql.

Our observation is that running your mysql server/apache server/php on a windows desktop is pretty difficult -- nearly impossible if you attempt the "brute force" method of following instructions on the web. The trouble is that 3 complicated programs -- Access, PHP, mysql have to all coordinate with each other, and they were all written by different groups of open-source people. . Better we think to put your database on a Linux machine where support is better.

How to synchronize a mysql and access database.

This involves writing a PHP program that can access both a mysql database and access database at the same time. The easiest way to do this is to run a XAMPP stack on your windows machine, connect to your access database using odbc on your windows machine, and connect to your mysql database wherever it happens to be. This can be done remotely -- i.e. on another network than the network that is hosting your access database.

Mysql will allow you to connect from linux or windows. Microsoft access is very difficult to get to from linux. There is no usable open-source driver. While there are commercial drivers that are sold for roughly $800 by "easysoft", they are complicated things -- the installation manual runs many pages. It is much easier to set up an XAMPP stack (i.e. run apache, mysql, PHP, PERL) on a windows machine. It runs nearly out of the box, and it also costs nothing.

I also attempted to run IIS (instead of apache) on a windows server. This is far more difficult than running an XAMPP stack. Probably possible to do this too, but anything having to do with windows systems is generally much more time consuming than figuring out the same thing on a linux or similar system.

A few Mysql workarounds.

Mysql, being "open software", is sometimes a little frustrating. Here are a few solutions that took me more than an hour to figure out.

Search for special characters in mysql using regular expressions:

Use the strange syntax below using the [[. See http://dev.mysql.com/doc/refman/5.1/en/regexp.html for a list of all of the special characters. Oddly enough, PHPAdmin doesn't act the same as PHP, so you have to figure this out by trial/error.

"SELECT * FROM `YOURDATABASE` WHERE DATAFILE Regexp '^[[.question-mark.]]';";

 

© Copyright June 13, 2015 , Timothy C. Hain, M.D. All rights reserved.
Dr Hain's CV Clinic dizziness-and-hearing.com FLW Rant-Rave