As I mentioned previously, I just upgraded to Office 2007, and because I switched to a different Office edition to save money, I no longer have a current copy of Microsoft Access. I have a handful of Access databases that I use in a couple of programming projects, so I needed to convert those to either SQL Server Express or MySQL.

I’ve been using MySQL for quite a while for PHP projects, but I’ve never used it for a .Net project. So I decided to try SQL Express first because the programs that use the Access databases are written in .Net. I also already had the VB and C# Express versions installed on my machine, so I figured they would work well together.

SQL Express installed just fine and everything seemed to be okay, but with the Express version you don’t get a copy of query analyzer and you get a super-stripped down version of enterprise manager. So stripped down, in fact, that it does not have import and export. What the heck? How are you supposed to get data in and out? Well, I thought I would get sneaky and use the SQL upsize feature built into Access. That seemed to work okay and for a while I thought everything was fine until I discovered that it had truncated a bunch of my long text fields at 257 characters. Not good at all. I tried various ways of doing the upsize from Access, but no matter what I did the values were still truncated. So I wrote a quick utility to read out the values from the Access database and make a bunch of SQL insert statements in a text file, and then I ran that and finally got all of my data loaded correctly. Then I tried some of my views in SQL Express to make sure everything was working, and I discovered that SQL 2005 no longer nicely handles views with an order by clause. When you open the view it just ignores the order by and returns the rows in a random order. There are apparently some workarounds for that, but I was starting to get really frustrated.

So I installed MySQL 5 (well, technically I just upgraded my previous install of MySQL 4). The installation went smoothly and everything seemed fine. I tried to run the MySQL migration toolkit that lets you pull in data from other sources, but it told me that I needed to update the Java runtime on my machine first, so I went ahead and did that. It took less than five minutes, including the download, and it didn’t even need a reboot, so no big deal. With the new Java runtime installed, it let me run the migration toolkit and then it was able to pull in my Access database, including the data, in about 15 seconds. And the data wasn’t truncated. And it played nicely with my views. That went so well, I decided to try switching my .Net programs over to the MySQL connector. I downloaded the connector, added it as a reference to the projects, and then did a fairly simple search and replace to swap the SQL Server objects out for the MySQL objects. And then it just flat out worked.

So what did I do next? I completely uninstalled SQL Express. How is it that two products from Microsoft can’t work together, but MySQL had no problems whatsoever pulling in data from Access? I don’t know the answer to that, but I do know that I’m using MySQL for all of my projects at home from now on.

Advertisements