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.
- Access costs quite a bit, while Mysql is free. One would really like to "replicate" instances of Access in a way similar to Microsoft Excel. However, as Access is not "baked in" to Office, most people in the office will not have any way to open up Access files. Another problem is that because Access is very complex, most support personnel will not have the ability or inclination to learn to use it.
- While one interact with to "Access" databases through Microsoft visual compiler products, it is not easily possible to use internet browsers to get to these databases. In other words, the primary way that people get to data these days, is very difficult to implement in Access.
- Access manuals are not included with the product, and secondary market books typically run 1000 pages. We do not think that any program should require a manual that runs 1000+ pages. This means that the program is too complex for use by most ordinary human beings. To use access, one needs to be able to understand both relational databases as well as program in Visual Basic. This is just too much.
- Access versions are incompatible and are degrading in function
- Access 2010 no longer supports "data access pages". While it appears through arcane manipulations -- building forms that work off the "runtime package, one can do something similar, the 2 chapters in my 2003 Access seondary market book are obsolete. We have no intention of using "sharepoint", whatever that is, as we don't want to learn yet another proprietory interface, that is limited to 64 bit installations.
- Access tables edited in 2010, can be made incompatible with 2007, without any warning at all. They have the same file extension.
- Access has no "wizard" to make a 2010 file backwards compatable.
- Access blocks installation as a standalone program -- on a computer running office 2007, one cannot install Access 2010.
- Access is not secure - there are utilites readily available that will "crack" access 2003 passwords.
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.
- Download and install the Mysql, usually with Apache and PHP.
- The easiest way to do this is to use the "wamp" distribution. Wamp stands for Windows, Apache, Mysql, PHP.
- Change the "datadir" link in my.ini to a convenient one (access my.ini by left-clicking on Wamp). We use dropbox for this.
- Create a new database in Mysql, using the command line interface or using phpMYadmin (part of Wamp)
- Install the ODBC connector. This link http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-examples-tools-with-access.html tells how to do it.
- Save your Access database tables to your Mysql database, by right-clicking on the table name, selecting Export/ODCB database.
- Open up your mysql database to make sure you got what you thought.
- One can put the data-files for mysql into dropbox.
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.]]';";
June 13, 2015
, Timothy C. Hain, M.D.
All rights reserved.