My Experience with MySql and MS Access

(retrieved from stronglytyped v1)

I have been pondering the idea of using MySql as a database backend with Microsoft Access. I thought that if it worked well it would make a great match. Some friends asked why not just use Microsoft SQL or the desktop version. My question to that was why not use MySql? We would need some tests to prove feasiblilty and here’s what I found:

NOTE: I’ve been using a product called DBTools - DBManager Professional . It’s an awesome product and can connect to a variety of databases including MySql (Brasillians Rock!!).

After getting MySql installed you’ll need the MySql ODBC driver. Access connected and worked very well from the start with the combination although there are a few things you need to keep in mind.

First MySql supports types that Access doesn’t. For example MySql can use BigInt. Access doesn’t support that and if you open a table with an unsupported type you’ll get “#error” in the grid view. Also if you make any changes to the tables you’ll have to delete and recreate the linked tables.

The second thing I found that helps a great deal is to include a timestamp field in each table. MySql supports this and will update the field with a date/time value of when the record was updated. My guess is Access uses this for concurrency management.

The next thing I did was started logging how Access talks with the database. The results were interesting:

Access doesn’t open a connection to the database until it needs to which is good. When I opened my app it didn’t connect until I opened the form. The connection isn’t closed until Access is shutdown not when the MDB is closed.

For reference I’m using this table:

CREATE TABLE `todos` (
`id` int(11) NOT NULL auto_increment,
`description` varchar(100) NOT NULL default ‘’,
`done` tinyint(4) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The MyISAM database engine seemed most appropriate because of its goal as a general purpose engine.

When I first opened the form I found a series of queries that came through:

1> 45 Connect root@localhost on todo
2> 45 Query SELECT Config, nValue FROM MSysConf
3> 45 Query SELECT `todos`.`id` FROM `todos`
4> 45 Query SELECT `id`,`description`,`done` FROM `todos` WHERE `id` = 1

The query to MSysConf was unfamiliar to me. I found that if the table exists it help control Access with optimizing its connection:

http://office.microsoft.com/en-us/assistance/HP051876211033.aspx

The next two queries look familiar. The #3 looks like it’s grabbing a list of primary keys and #4 is pulling the record which is being displayed

Here’s what I found after an update. As soon as I started the edit Access pulled the latest record:

SELECT `id`,`description`,`done` FROM `todos` WHERE `id` = 1

I’m assuming it’s pulling the latest record for concurrency checking.

Here’s what it did after I committed the record:

SET AUTOCOMMIT=0
UPDATE `todos` SET `description`=’Upgrade to python 2.9′ WHERE `id` = 1 AND `description` = ‘Upgrade to python 2.8′ AND `done` = 1
COMMIT
SET AUTOCOMMIT=1

So far everything makes sense and seems reasonable. MySql should be more than capable of handling this type of interaction.

Here’s what happened when I committed a new record:

SET AUTOCOMMIT=0
INSERT INTO `todos` (`description`,`done`) VALUES (’Buy more books’,0)
SELECT `id`,`description`,`done`,`sys_timestamp` FROM `todos` WHERE `id` IS NULL
COMMIT
SET AUTOCOMMIT=1
SELECT `id`,`description`,`done`,`sys_timestamp` FROM `todos` WHERE `id` IS NULL
SELECT `todos`.`id` FROM `todos` WHERE `description` = ‘Buy more books’ AND `done` = 0
SELECT `id`,`description`,`done`,`sys_timestamp` FROM `todos` WHERE `id` = 5
SELECT `id`,`description`,`done`,`sys_timestamp` FROM `todos` WHERE `id` = 5 OR `id` = 5 OR `id` = 5 OR `id` = 5 OR `id` = 5 OR `id` = 5 OR `id` = 5 OR `id` = 5 OR `id` = 5 OR `id` = 5

I’m not sure what Access is doing with the 4 select queries. I think the second one is to refresh the form to take into account of any default data produced by the database.

The one with multiple ‘id’ = 5 just confuses me. I also noticed that if the timestamp field doesn’t exist you’ll get ‘#deleted’ text in the fields if you go back to the record in Access.

I’ve been testing Access and MySql with several databases with 100K+ records and it seems to hum pretty well. I haven’t found any show stoppers.

Currently we’re porting a medium sized Access application to a MySql back end. We had to rename many fields and tables (i.e. Balance$ had to be changed to BalanceAmt) and made sure primary keys existed. Besides that it’s working quite well.

Welcome to Stronglytyped Version 2.0

In the real world we have the preacher, the congregation, and the atheists. In the world of computers and software I have blogged through StronglyTyped as all three.

I really love software. It's a passion of mine to learn, build, debug, break, test, deliver, and exchange code. It's a simple pleasure. There's no need to analyze it. Coding in just plain fun.

In the real world there are lots of enemies against the enjoyment of coding. Corporate politics, outsourcing, downsizing, burn out, and bad egos all play against the developer who does it for the love of code.

With StronglyTyped v2.0 I want to write about code and how I have fun with it. I will fill it up with thoughts and ideas on various areas of software development. Occasionally I will post something from StronglyTyped v1.0 but I will not carry the old site over. It's a new version; a new start.

I will stay away from dissing technology and weak and bias comparisons. This is not about M$ against the Slashdot geeks. It's about enjoying the goodness of code.