Multi-User Microsoft Access Databases

Upsizing to Microsoft SQL Server or MySQL Server

Why Upsize MS Access?

There are many advantages to moving your important data from Access to a Microsoft SQL Server or MySQL Server. We've listed the major reasons below, along with some brief rationale for each.

Better Access Performance

If you presently have your database split into a front-end and back-end, with the back-end on a shared file server, then you know Access slows can slow to a crawl when the network is busy and more than one user is trying to work in your database. Why the slowdown? Imagine you and two other people are trying to read the same book. In all likelihood, you are each trying to read a different page, but because you are all holding a single book, it is a slow process. You may try taking turns reading a page at a time, or holding the book "just-so" to let each person see part of their page. Either way, it's uncomfortable, slow, and inefficient. Microsoft Access, built mostly for the desktop, has similar challenges.

A true server, like Microsoft SQL Server or MySQL Server, if far more effective at letting multiple users work in the same database. Taking our book example, now imagine you had the ability to split the book up into sections and give each person their page and a few before and after it. You would only need to worry about sharing when two people needed the same page. Microsoft SQL Server or MySQL Server are far faster because they are designed to handle this sort of situation.

Lastly, in the case of a server-based database, the Microsoft SQL or MySQL server's actual hardware does far more of the heavy lifting and computing. When you store your Access back-end on a file-server, the file server simply stores the data and your local machine does all the heavy lifting, including pulling all the data down from the network to your machine, whether you need it all or not.

How much faster can a Microsoft SQL or MySQL server-based database run compared to a file server-based database? The answer is, of course, "it depends." It will likely run far closer to what you would expect if the database was entirely on your local PC than when located on your file server.

Microsoft Access on a file server with multiple users can feel like gridlock

Reliability

With a Microsoft SQL or MySQL server to store your data, your IT organization generally assumes the server hardware and software will need to operate more reliably than a file server. Why? Largely because a database server is often used by many groups, and potentially with business-critical activities to carry out. Contrast that with a file server, which are also quite reliable, but may get less focus from an IT organization. If the file server goes down, you may not be able to retrieve a Word or Excel document, but generally the impact is lower. And for very large organizations, Microsoft SQL or MySQL servers are often designed to be redundant - meaning that if one machine fails, another is ready to pick up the work almost immediately. Check with your IT organization to be sure, of course.

Security

Microsoft SQL Server and MySQL Server databases have far more comprehensive and robust security features to help you lock down your database tables, if needed. You can specify at a granular level which users and groups should have access to specific tables and views (queries), and what sort of access. Microsoft Access Workgroup Security can provide similar levels of security for tables, but it is generally agreed in the industry that Microsoft Access is still vulnerable to experienced hackers.

Backups

Microsoft SQL Server and MySQL Server have built-in backup and restore capabilities. IT organizations generally place a file server on a slower backup cycle than they might for a database server. So while the database server might be backed up nightly, the file server may only be backed up every few days.

Leveraging the built-in backup features in Microsoft SQL Server and MySQL Server means a smaller impact if your database gets corrupted, data is accidentally deleted, or the server has a catastrophic failure.

Microsoft Access and SQL Server Security

SQL Server and MySQL Server provide flexible user- and group-level security

How To Upsize

Microsoft provides two primary tools to facility creating and migrating your data to SQL Server. The first tool is provided with Microsoft Access 2000, 2002, 2003, and 2007. In versions before Access 2007, select Database Tools from the File Menu then Upsize Wizard. In Access 2007, select "SQL Server" in the Database Tools Ribbon tab. Microsoft also supports a slightly more feature-rich utility called SQL Server Migration Assistant for Access, which is installed separately.

MySQL offers the MySQL Migration Toolkit, which walks you through the process of creating a new MySQL database from your Access database, and then migrating the data.

With all three utilities, the end result is the same. Your data resides on the server, and the tool establishes linked tables to the server. Local tables are renamed and retained in your Access database, but it is important to cease using them, and only use the linked tables.

How It Works

When you link your Access database to Microsoft SQL or MySQL server, Access employs an interface called Open Database Connectivity (ODBC), which has been available from Microsoft since 1992. The ODBC connection between your Access database and the server database turns your Access database into a "client" which retrieves data from and sends data to the server.

Microsoft Access linked tables