We all love Access, but our favorite tool has many “features” that lead the naive developer into error. You may not appreciate the cost of these less-than-helpful additions but, should you upgrade to an enterprise database, you’ll regret every one of them. Garry Robinson outlines those errors and how to avoid them (along with some code to find the errors).
Recently I was asked to start preparing one of the Access databases that my company provides support for so that it was ready to upgrade to SQL Server 2000 or another enterprise database. The database was initially designed by a techno-savvy person, who, to his credit, came up with a database design that has stood the test of time and the critique of many of his peers. Unfortunately, Access can be a little too accommodating when an enthusiast designs a database, and this can allow design flaws to creep in–errors that a database professional may have been wise enough to avoid.
I’m going to discuss some of the subtleties that you’ll need to address in your database tables in terms of upsizing your tables to an enterprise or open source database. It’s better to make your database as perfect as you can before you try to convert your data. Once you’re in an environment where you have Access as a front end and some other database as your back end, things get a lot more complicated. Or, if you’re like me, improving your database model and reducing the size of your database is just a good thing to do.
If Access is going to update a back-end database through ODBC, Access requires that a table must have at least one unique index. This means that just about every table in the database will need to have a primary key. A primary key isn’t absolutely essential since any unique index on a table will do. In fact, tools like the SQL Server upsizer wizard will simply make the first unique index in the table the primary key.
Once you decide that you need to add a primary key to a table, you may not be allowed to add the key because you have duplicate values in the fields that you want to use in your primary key. There are two solutions. The first is to add one of those ugly AutoNumber fields to your table and make this your primary key. This is certainly quick and, if you resolve to review the key again later, you’re really no worse off than you were before you started.
The better way to solve the duplicate items issues is to use the Find Duplicates query wizard (just click on the new query button in the database container to get to the wizard). This query will identify your duplicate values so that you can eliminate them. After having revised your index, don’t forget to renew your database’s relationship diagram if there are any other related tables.
If you don’t add a primary index to a table straight away, Access goes out of its way to offer to add a unique index for you. Some naive developers accept this offer and never give it another thought. Unfortunately, the default name for this primary key is “ID” and the naive developer often accepts this. Once Access has played this card, a subsequent trap for the unwary occurs when the lookup table wizard is used. If the wizard is invoked, Access will often add an auto number field to a main table that matches the auto number field in the lookup table. This has the effect of storing a number in your table and a number in your lookup table. In addition, the name (probably “ID”) is also duplicated into the main table, leading to more confusion. This repetition of names makes it difficult to figure out what tables are related to each other.
Another problem with these “ID” fields is that the field will generally be accompanied by a unique index that’s also called ID. This unfortunate naming convention will cause problems with the transfer where it may be that indexes and fields might not be allowed to share names (or where “ID” is a reserved word). The solution is to search through and eliminate all ID field and relationship names and replace them with meaningful names.
Another Access “feature” whose results you can run into during conversions is the AutoIndex option. This little “nuisance” is located in the Table/Queries tab in Options (see Figure 1). Even though I try to clear this option as soon as I start working on a database, many developers are unaware of this option. The result is that there could be many tables in the database with indexes that weren’t planned for. If you think that this is unlikely, try this little exercise:
- Make sure that the option “Auto Index on Import/Create” has the value “ID”.
- Open a new table in design mode and add a field with any name.
- Add “ID” to the end of the field name.
- Save the table.
- When prompted, choose Yes to create a primary key.
- Now open the table in design view and choose View | Indexes from the menu.
You’ll now find that you have two indexes in the table: your primary key and the key automatically generated on the field ending with “ID”.
Imagine that particular “feature” applied in a database with 100 tables (or more) and you’ll start to see the challenges that can beset a database developed by an enthusiastic developer with the assistance of an enthusiastic Access wizard interface. While some of these indexes might actually speed data retrieval, keeping all of these indexes up-to-date is slowing down your database. And, when you upsize, they’ll slow down the database server for everybody.
Another great gotcha is finding a relationship between two tables that have different sized fields. I don’t seem to fall for this one very often, probably because, when I have a field in one table that I want to duplicate in another table, I copy and paste the common field. Maintaining these relationships is inefficient and, in SQL Server, forbidden. The error message that you’ll get if you attempt to upsize a mismatched relationship to SQL Server looks like this:
[Microsoft][ODBC SQL Server Driver][SQL Server] Column 'myTable.sampleNumber' is not the same length as referencing column 'mySecondTable.SampleNumber' in foreign key 'MyTable_FK00'.
To fix the relationship, head to Access’ relationship window, right-click on the join between the two tables, and delete the relationship. Next, right-click on one of the tables and switch into design mode. Now change the field size to match the size in the other table, save the table, and voilà!–you’ll be back in Access’ relationship window. To complete the exercise, re-create the relationship between the tables.
At this stage, you may be wondering if I can show you some code that will identify these issues rather than making you wade through your databases to find these problems manually. I certainly can, and I’ll start with some VBA that loops through all the tables. For each table, I call two functions of mine: one that checks for the existence of a primary key and another that verifies that the fields used in a relationship are the same size in both tables:
Dim i As Integer Dim strTable As String Dim varMsg As Variant For i = 1 To CurrentData.AllTables.Count strTable = CurrentData.AllTables(i - 1).Name If Left(strTable, 4) <> "msys" Then varMsg = checkPrimaryKey(strTable) If Not IsNull(varMsg) Then MsgBox varMsg & strTable End If varMsg = chkFKeyLength(strTable) If Not IsNull(varMsg) Then MsgBox varMsg & strTable End If End If Next i
Both of the functions that I wrote use the good old DAO library to retrieve information about the tables. I’ve recently become more upbeat about using DAO in my applications, as it’s become obvious that ADO is never going to replace DAO for managing Access databases (this was confirmed for me when DAO reappeared in the Access Help files in Access 2003).
Here’s the function that reviews all the indexes for every table to see if any of them have the Primary property value set to True. Passed a table name, the code retrieves the definition of the table from the TableDefs collection, and then loops through the table’s Indexes collection looking for a key flagged as the Primary key:
Function checkPrimaryKey(tableReq As String) _ As Variant Dim dbData As DAO.Database Dim tdf As DAO.TableDef Dim idxLoop As DAO.Index checkPrimaryKey = Null Set dbData = CurrentDb dbData.TableDefs.Refresh On Error Resume Next tdf = dbData.TableDefs(tableReq) For Each idxLoop In tdfLoop.Indexes If idxLoop.Primary = True Then GoTo checkPrimaryKey_exit Exit Function End If Next idxLoop checkPrimaryKey = "No Primary key for table " checkPrimaryKey_exit: Set dbData = Nothing End Function
My next piece of code is the function that checks the fields on both sides of a relationship to see if the field size is the same. It does this by working through the relationship objects in the database and verifying the field size on both sides of the relationship. If a discrepancy is found, the function returns a descriptive error:
Function chkFKeyLength(tableReq As String) _ As Variant Dim dbData As DAO.Database Dim relLoop As DAO.Relation Set dbData = CurrentDb chkFKeyLength = Null On Error Resume Next dbData.Relations.Refresh For Each relLoop In dbData.Relations With relLoop If tableReq = .Table Then If dbData.TableDefs(.Table) _ (.Fields(0).Name).Size <> _ dbData.TableDefs(.ForeignTable) _ (.Fields(0).ForeignName).Size Then chkFKeyLength = "Different foreign key " & _ "lengths between " & .Table & _ " and foreign table " & .ForeignTable End If End If End With Next relLoop dbData.Close Set dbData = Nothing End Function
With those examples, you can see how you might write code to test for upsizing issues that you commonly encounter in your databases. But wait! There’s more that you should check for.
No matter what Access will let you do, all of your tables should be named without any fancy characters or spaces between parts of the name. Moving tables with these kinds of names to any other database (including Microsoft’s own SQL Server) is going to make your conversion more difficult. Even in Access, dealing with table and field names with embedded spaces is awkward, requiring you to enclose the name in square brackets.
From time to time, all Access developers will have used a reserved word as a field name in a table or a query. Once again, Access isn’t too harsh on the developer and will frequently forgive these errors. But, as I stated before, now is the time to sort out these anomalies before you upsize to a more restrictive database. You should avoid not only reserved words from the Access environment but also reserved words from the server environment. You even have to consider the reserved words used by the ODBC environment if you intend to use links to the server database tables.
For one project that I worked on, there were more than 50 tables that suffered from issues such as reserved words or field names that didn’t follow safer naming conventions. I considered using an Access renaming tool like Speed Ferret or FindAndReplace but, in the end, I took a simpler approach. I remembered that the name of the table in the server/back-end database must follow the correct naming convention. However, my method prevents the names from appearing to change in the front-end database so my code doesn’t need to change:
- Open the back-end database.
- From the Tools | Options menu, make sure all of the Name Autocorrect options are turned off.
- Rename the table from its current name to a (slightly) different name that conforms to your stricter naming conventions.
- Fix up any issues with the field names in the renamed table.
- Open the front-end database and delete the link to the old table.
- Create a new link to the renamed table.
- Create a new query that has exactly the same name as the original table.
- Add the renamed table to the query.
- Add all of the fields from the table to the query.
- Where a field has been renamed, create an alias for the field that matches the old field name.
In Figure 2, I demonstrate how I’ve set up a field alias for a couple of fields in a query so that the query now mimics the old naming conventions. I’m not suggesting that you shouldn’t fix these unfortunate names. But the good thing about my approach is that it quickly resolves the issues in your back-end database by isolating those issues in your front end. You can more easily fix and test these issues in your front-end database–and do it after the hurly-burly of the back-end conversion has been completed.
Access tries so hard to be helpful, it seems almost cruel to criticize the results. However, if you accept the results of the Access “helpers” without thought, you won’t be following the “best practices” for a professional database design. While Access may let you get away with these problems, it’s only a matter of time until these deficiencies rise up and bite you–and converting to an enterprise database is just one of those times.