Microsoft Access is a very good database solution, but it has limits. While the portability of mdb and accdb files is convenient, there are advantages to moving to the less portable SQL Server solution. Cost, however, is not one of those advantages. Unless you have a SQL Server available to you in your network, Access data stores will continue to be a better, more cost-effective route.
On the other hand, if you do have SQL Server, there's very little reason not to consider migrating your Access Databases. Not all custom-made Access applications easily lend themselves to a SQL Server solution so you'll need to do some analysis before choosing a migration path. That's what we'll consider in this article.
Why upsize from Access to SQL Server?
Microsoft Access files, be they MDB or ACCDB, have a maximum capacity of 2 gigabytes, minus the space needed for system objects. The work-around for exceeding this limit is to use multiple data files, and this is indeed an adequate solution when the required capacity is only a handful of GBs. However, once your data reaches tens or hundreds of gigabytes, Access files are no longer a viable solution.
There are, of course, other databases that would work. Oracle, if you can afford it. MySQL, now owned by Oracle, has been a free, open-source solution. Perhaps there are other database solutions out there, but for more than a decade I've been impressed enough with SQL Server that I haven't cared to look around for anything else. It can handle whatever capacity your application requires.
I am the worst developer when it comes to security. I hate thinking about it and I loathe implementing it. Using security in SQL Server can be as simple as you like, or as complicated as you need. You can GRANT access to objects and DENY to others; whole tables or individual columns.
In my opinion, this sort of Power-Mad Micro-Management leads only to pain and frustration. I've seen implementations where a user is granted access to a stored procedure that references a view to which the user is denied. As I recall, DENY always trumps GRANT, which means a procedure with this security configuration would fail. (This sort of thing is also very difficult to debug!)
So what does this mean for your Access application? Implementing security on SQL Server is at least as simple as trying to manage User-Level security in MDB files of the Access 97-2003 era and in most cases, simpler. As for Access 2007 and 2010, it is virtually the only way to implement security as the old User-Level security paradigm was abandoned in ACCDB files.
The easiest way to apply security for users of an established domain is to link the SQL Server tables to your Microsoft Access client using a Trusted Connection and implement a role-based security model. Putting domain users in one or more roles gives them access to the tables that correspond to that role. For example, if a user attempts to read salary information in the HR tables but they are not in the HR role, the SQL Server will deny access. This might generate an ungraceful error user interface, but that too could be managed through VBA code.
By "availability" I have in mind two ways that SQL Server is more available than Access files: downtime and visibility.
In order to modify an Access data file, whether that be adding tables and columns or simply performing a compact and repair, the file must be opened exclusively by one user. That means you need to get all other users to close their client files that link to the data. This can be extremely difficult when users go home at night and leave the application open on their desktop.
Sure, there are ways around this. I once built a module that would watch the data file for a signal to shut down the client. It works, but at the expense of having a hidden form open on each client instance, constantly pinging the server to see if it should shut down. I hated that approach. The other method I implemented was to restart the server. This approach is very drastic and one I wouldn't recommend, but it is effective.
SQL Server eliminates the need for either of these offensive practices. Go ahead and add a table or compact the data files. You can even add a column to a table that clients are linked to and currently using. The Access application won't see the new column until their links are refreshed, but provided you don't remove or rename an existing column, the application will continue to function correctly.
The other way SQL Server availability exceeds that of Access MDB or ACCDB files is the answer to this question: How do I make my Access application available over the Internet? With an MDB file, this is simply impossible. You and I cannot create table-links to an MDB file on a remote server via the Internet. We can, however, link to a hosted SQL Server. (For more information on how to do this, see my August 2010 article titled Migrating your Access/SQL Server App to the Cloud.)
How to upsize your Access database
There are a number of ways to upsize your Access database to SQL Server. I discuss three options below. The topography of your application will determine which one that is best for you.
SQL Server Import Wizard
If you simply want to pull in the basic table structure and data, you can use the SQL Server Import and Export Wizard that is part of SQL Server Integration Services (SSIS). This is a quick and dirty way to move your tables and data. It does not, however, reproduce primary and foreign keys, so this route will require that you tweak every table in design mode. Indexes too will have to be added manually, except for those that SQL Server generates when you add primary and foreign keys.
As a rule, I don't consider this method to be that valuable when you are migrating a complex application. The other methods we will discuss perform more of the "grunt work" for you. As you'll see, this is very desirable.
Access Upsize Wizard
Most likely, you'll use the Access Upsize Wizard that lives in the Microsoft Access user interface. The menu option is in different places for Access 2003 and Access 2007, but the function is the same. Below is a sample of the dialog box used to define the migration. Indexes, rules, defaults and primary/foreign keys can be included in the upsize process. This will save you a ton of work and it's virtually fool-proof. I've performed dozens of these upsize actions and have never had an issue.
Notice in the bottom half of this dialog that you can choose to assign a TIMESTAMP field to all tables. Should you do this? Unequivocally yes!
Access does this funky thing with SQL Server when a table has a "memo" field. To determine if a user has changed a record while you are editing it, the values in all columns are compared, including memo fields. But because of the nature of memo fields, Access throws up its hands and says, "I'm sorry, I just can't tell is another user has edited this record or not. Gonna have to drop your changes." The only way around this is to give the system a better way to see if a record has been updated. That's what TIMESTAMP columns do. You don't need to expose it and in fact, you cannot edit it. The column only needs to be there and may be named how you like. Its mere existence solves the multi-user error issue.
Next, the wizard asks you how you want the application to interact with the newly created SQL Server database, if at all. I usually elect to make no local changes and create a new, empty, pristine MDB shell into which I import all the forms, reports, queries and modules I need.
If your application already links the front end to tables in a back-end MDB, then you simply redirect the links to your SQL Server. If you have no linking paradigm, you'll need one. I won't discuss that here but it's a trivial matter to find code for that. This is where you will implement either Trusted Connection, using the Windows Login and its associated permissions, or a home-grown security paradigm with custom SQL Server logins. On one occasion I used a single "application login" for all users but in the end, I didn't like that. The advantage of using a Trusted Connection is that SQL Server can then identify the user for any given transaction.
Below is the Table Relationship diagram for the sample database I upsized in preparation for this article. As you can see, it's not trivial. The upsize worked flawlessly, retaining all keys, indexes and defaults. (This database implemented no rules.) The other screen shot is a sample from the SQL Server object explorer showing how tblSites was correctly upsized. While the object names chosen leave something to be desired, it is fully functional and needs no further attention.
MUST SQL Upsizer Utility
There is one commercially available option I feel compelled to mention. While I've never used this product in production and this article isn't meant to be a product review, it's worth considering. You can read about (and purchase) the MUST SQL Upsizer Utility at Garry Robinson's web site: http://www.vb123.com.au/up/orders.htm.
According to the literature, this program does the following
· Identifies and corrects problems
· Generates SQL scripts
· Removes indexes that aren't needed (SQL ignores them anyway)
· Supports repeated migrations for commissioning
· Supports SQL Server 2000/2005/Express/2008
· Replaces the Access upsizer without the hassle
Below is one screen shot from the utility. Notice the third box down where the label reads Converting SQL. The application promises to convert your queries to views and stored procedures. This highlights the major flaw of the free Access Upsize Wizard. It doesn't even mention to you that your queries are completely ignored.
Without this tool, or one like it, you have two choices with respect to your queries:
1. Keep your queries in Access and forego any server side processing benefits
2. Learn T-SQL and rewrite the Access queries yourself
I'm not saying that Option 2 is bad or that it can't be done. I've done it and in fact, that's how I learned T-SQL. It just takes time and a lot of googling for advice. If you've embedded functions like IIF(), InStr(), Mid(), Date(), Now(), etc., in your queries, these will have to be converted to their T-SQL equivalents in order to run in SQL Server views and stored procedures.
If you have any complex custom VBA functions for analyzing data and you want this to run on the server to improve performance, you'll have to rewrite your VBA as T-SQL code. It's actually great fun, and I love doing it, but if you have a deadline, you may need the help of a utility such as this one. Sure, it will set you back at least $199 for the most basic version, but that represents only a couple of hours of billable time.
The application I converted had about 900 queries and the manual conversion process took me about 6 weeks. Part of that time was spent writing a code generator that would do the basic typing of field and table names. After that, I used templates to help me remember how to implement the standard functions. It's just a very time-consuming process, but here are some links that might help:
The last reference includes a download of the utility I wrote to convert my queries into VIEWs. It's not perfect but does a lot of the busy work for you.
I recently created an Access database for a friend and using a local MDB file was absolutely the right call. At my office, however, there is seldom a compelling reason not to use SQL Server. You might experience some pain migrating to SQL Server, but as you become more comfortable in the environment, you'll never want to go back to MDB files again.