MS Database Migration

Microsoft Access Database Migration:
what to do when your web host drops the old Access and JET drivers…

Shop for web design and development books here.

It will happen sooner rather than later—your web hosting company will “upgrade” their servers to Windows Server 2008 and stop supporting your trusty old Access databases. If you are a Windows-based web developer, you already know that the JET OLEDB driver (from MDAC 2.8) does not come with Windows 7, and you have to install it. The same situation exists with Server 2008, but the difference is that your web hosting company is not likely to install JET and continue offering a database connection driver that Microsoft is abandoning in favor of the newer ACE driver.

The following account is my saga of migrating several of my clients' sites to the new Access 2007 (accdb) format. I have several sites that use one to five databases for both dynamic content and applications like membership rosters and event calenders. These have fairly complex administrator apps so that my clients can update them on-line. They are all low-traffic sites that do not need SQL Server. I have thousands of hours in dev time in these custom apps, and they have all worked well for several years. Then the other day I got a broadcast email notice from my hosting company saying they will be upgrading to Server 2008R2 in a couple weeks and, among other things, will no longer support the Access 2000-style databases. I spent about 100 hours (uncompensated) trying to figure out how to accomplish this unexpected migration without spending a fortune…

I will show you two plans. Please read both before committing to either one.

Plan One: SQL Server

First, since they offer SQL Server with my hosting package, I decided that would be the way to go. One can get SQL Server Express (SSE) and SQL Server Management Studio Express (SSMSE) free from Microsoft, so I should be able to install them, convert the old Access databases to SQL Server format, then debug all my apps—right? Not so fast! Here are few advantages, and many disadvantages:

  1. SQL Server is much faster in high-volume sites, and allows concurrent connections without collisions. End of advantages—now for the bad news…
  2. You can’t just make an SQL Server database file and upload it and then connect your web site to it, like you do with Access databases. The only thing you can do is set it up in your web host’s control panel, import your Access databases to it, and generate a backup. Well, some web hosts may offer some on-line management tool, but you still can’t do local development and simply transfer it to your web host.
  3. The host’s conversion process is buggy and you’ll likely end up with an SQL Server database on their SQL Server that won’t run, unless your Access databases were already perfect for conversion—and they don’t tell you what’s likely to fail.
  4. The shared web hosting company will not allow remote connections directly to their SQL Servers (security reasons). You’ll have two choices: (1) use their management tool (if they offer one), or (2) write hand-made asp/vbscript/sql files then upload and run them on the web server to make any changes to the database—and even then, your host may block scripts that try to run SQL like DROP, CREATE, and ALTER.
  5. Much of the SQL in your web site will need to be re-written and debugged before it will run an SQL Server database. Unless you’re an expert with it, this is likely to take dozens (or hundreds) of hours per site.
  6. You can’t charge your clients for this—after all, it’s not their fault the web host is upgrading, or that Microsoft is screwing everybody (again) with an upgrade that isn’t backward-compatible.

If you want to install SQL Server Express and Management Studio in your computer for testing and training, here's a few pointers:

  • Get the 32-bit version, even if you have Windows 7 x64. Set IIS 7.5 to run 32-bit apps.
  • Use the default data directory, as SSE will set it to write to it. If you decide to set its default data directory to something else (like \inetpub\wwwroot\db\) you may have to fiddle with permissions.
  • See connectionstrings.com for those, and use the Native Client driver. Only one of them worked for me:
    conn.open "Provider=SQLNCLI11;Server=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=mydbname"
    SQL Server 2008 will use the SQLNCLI10 driver, and 2012 will use the SQLNCLI11 driver, like above.
  • Management Studio will provide an SQL window where you can write and execute Transaction-SQL to create a database, tables, fields, and populate your database with data. It will not do any of these automatically—you have to hand-write and execute the SQL (repeatedly) to do it. It is by no means a WYSIWYG development environment! For that, you’ll need Visual Studio or similar.
  • Remember that once you’ve created, debugged, and populated your SQL Server database, you will not be able to upload it to a shared web hosting server! You’ll need to make one there, somehow, and import your data into it with uploaded ASP/SQL scripts, unless your hosting company has some management tool to do so.

Here’s something like the ASP/ADO page I made to export the data (table by table) from my Access mdb database files, after using the host’s control panel to set up an SQL Server db. In this example, I’ll use an imaginary database called “MyPages” and it has one table called “pages” with fields ID (primary key), PageTitle nVARCHAR(40), and PageHtml nTEXT (nTEXT is a Unicode field that’ll hold up to about 2 gigs). See raw asp text file here. If your database has any MEMO (called TEXT in SQL Server), they probably contain line-feeds and you’ll need to use something else as the row delimiter.

Note that if your table data contains any | or ~, this will fail. You might first export it with regular delimiters, then do a search on the CSV file, or you can do it as above and then search the file to make sure that your field separator | and your row separator ~ are the only ones and where they belong—separating fields and rows.

Note also that the import will fail if you put ANY line-feeds in the CSV file (outside of a MEMO field). You’ll need a text editor that will handle an unlimited line length.

Once the CSV file is correct, you need to write SQL something like the following in Management Studio to create the table and its fields, then import your CSV data into them. See raw t-sql text file here.

When you execute that, either the import will work, or it will fail, or it will work but be broken in unexpected places. You can open another SQL query window and run SELECT * from pages to see the table output in Management Studio. When all the records look right, you have succeeded.

Note that if you not already a T-SQL expert (and why would you be reading this?), you’ll need to expend many hours of study first, just to accomplish the above task.

Plan Two: Migrate Your mdb Databases to accdb

accdb is the new format for Access databases, and it usually called the “Access 2007” format, though it also generally applies to 2007, 2010, 2012, etc. Here’s why you must do so when your web host upgrades their servers: Windows Server 2008 does not (without a willing hosting company) support the JET driver (which is 32-bit only), and the JET driver knows nothing of the accdb format. Worse, their old servers will not run accdb (no ACE driver), so you must have your databases converted, and a new version of the web site, ready to drop in place when they upgrade, to minimize down-time.

After spending 100 or so hours fighting with SQL Server and Management Studio, I decided $116 was not too much to pay for Microsoft Access 2010—if only to keep my clients happy. I logged into Amazon, bought it, downloaded it, and had it running in about an hour. Now, don’t choke me, but I never have liked their over-priced, confusing Office products. I’m a fan of OpenOffice! But it doesn’t do Access. Yes, OO will open and even edit Access databases, but it will not do more complex things like create and modify structure (AFAIK).

Access 2010 will open and edit both mdb and accdb files, and will convert them—sort of… You can’t just open an mdb and then ‘Save As’ accdb. You have to choose ‘Save and Publish’ and save your mdb as accdb, or you have to create a new accdb database then import your mdb tables into it. That’s where it starts to get confusing: You do so, but your new, populated accdb may not run on your web app, not even with the new ACE driver. It will, in typical Microsoft fashion, give you a cryptic error instead. You will then copy and paste that error into your favorite search engine, and spend the next 20 hours reading misleading forum postings, and even more misleading and cryptic articles.

In my case, the error message (reported from ADO to the browser) was some crap about "Operation Must Use an Updateable Query," leading millions of people to believe there is a “write permissions” problem on the db file—as is frequently the case. However, in this case, it was a couple of settings in the Properties of two tables that the new driver didn’t consider to be right for a web database. You don’t know this error will exist unless you (1) create a new “web” database in Access, (2) import your mdb file(s) into it, then it gives you the import failure errors. Then (3) you must save and close the error table, and open the mdb file(s), fix said errors, then (4) close them, then (5) wash and rinse repeatedly from #1. Then (6), when you can import your mdb file(s) into a new “web” accdb without any errors, you'll need to (7) make a new regular (not web) database and import your mdb file(s) into it — because you can’t edit the structure of an accdb “web” database! But once you get that done, the new accdb will work with the new ACE driver, just as your mdb’s did with the JET driver.

Now, if you’re a hand-coder like me, you’ll need to thoroughly test your web apps to make sure everything works. If not, well, you’re in for more hours of useless web searching and debugging! Then, you still don’t know if will work on the hosting server until you upload it and try it. In the case of my hosting company, they allow no overlap time—the old servers do not have the ACE driver, and likely the new ones will not have the JET driver, you have to be ready (as much as possible) to pounce when they come back on line from the upgrade.

Conclusion

The moral of the story is this: MS is dropping support for more and more legacy components, and seemingly at an accelerated rate. We (us ASP coders) should have learned PHP and MySQL a over a decade ago, and now all our clients’ web apps would still be working, and we would not be forced into this stupid “upgrade.”

September 2012 Update: I got so disgusted with the whole @%&!^$@#!%^$#! MS database show that I got a virtual private server and installed Ubuntu 12.04LTS Server, Nginx, PHP, and MySQL!

Leave a Reply

Your email address will not be published. Required fields are marked *