Access Traps for the Naïve Developer

Summary

We all love Access, but our favorite tool has many "features" that lead the naïve 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.

Index gotchas

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 naïve developers accept this offer and never give it another thought. Unfortunately, the default name for this primary key is "ID" and the naïve 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:

1. Make sure that the option "Auto Index on Import/Create" has the value "ID".

2. Open a new table in design mode and add a field with any name.

3. Add "ID" to the end of the field name.

4. Save the table.

5. When prompted, choose Yes to create a primary key.

6. Now open the table in design view and choose View | Indexes from the menu.

<<< Figure 1 - Click to expand

    <<< Figure 1: Pictures for Access 2007

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.

Automated detection

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.

Table Gotchas

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 more on Access reserved words, head to www.utteraccess.com. For more on SQL Server and ODBC reserved words, see http://msdn.microsoft.com/library/

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:

1. Open the back-end database.

2. From the Tools | Options menu, make sure all of the Name Autocorrect options are turned off.

3. Rename the table from its current name to a (slightly) different name that conforms to your stricter naming conventions.

4. Fix up any issues with the field names in the renamed table.

5. Open the front-end database and delete the link to the old table.

6. Create a new link to the renamed table.

7. Create a new query that has exactly the same name as the original table.

8. Add the renamed table to the query.

9. Add all of the fields from the table to the query.

10. 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.

<<< Figure 2 - Click to expand

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.

Sidebar: Upsizing Issues

While my article focuses on poor practices (and highlights how those practices create problems when upsizing your database), in this sidebar I'm going to look at a variety of issues that occur only during upsizing.

Before you wade into a SQL Server conversion project, you really do need to sit down with a good book on the topic. In fact, you'll probably need to sit down with a few good books. As an introduction to upgrading, I like Russell Sinclair's book From Access to SQL Server. I also like the book SQL: Access to Access SQL, by Susan Harkins and Mike Reid, because it offers some good insights into setting up SQL Server. The book also offers lots of detail on Access and SQL Server query design, which is good reading whether you're converting or not. The most comprehensive and up-to-date book is The Developer's Guide to SQL Server, by Andy Baron and Mary Chipman. This book should probably be on your shelf. For those of you who have the Enterprise Edition of Access Developer's Handbook, don't forget to thumb through the book, as it has enough information to get you off and running through those troubling early stages of getting to know a new technology.

You should consider using a tool to help with your conversion. At the time that I was writing this article, I was putting together an Access wizard that will be available as part of the tools sold from my Web site. For a more comprehensive solution, you may want to try SSW Upsizing PRO!, which Adam Cogan's company sells at www.ssw.com.au. This will give you a very detailed list of all the issues that you will face. I recommend Adam's tool to anyone who's seriously considering a larger upsizing project.

Tools do present their own special problems. For instance, the SQL Server upsizing wizard can miss hidden tables when doing a conversion. Finding out that you missed a whole bunch of hidden tables late in a conversion project can be a little embarrassing. Unhide tables before running any automated tools.

Even if you use a tool, it would be surprising if your conversion went right the first time. Make a copy of your back-end database and run the conversion wizard to give you a detailed list of all the issues.

When your conversion does succeed, you're into a new world. One of the key components of making a conversion to an enterprise server work is to be sure that the correct skills are onsite for when the conversion succeeds. There's no doubt that Access databases are easier to manage than SQL Server (for instance, I can ask my clients to e-mail me a compressed copy of an Access database for me to make enhancements to). SQL Server databases require a more qualified technician with administration access to the server to assist in maintaining your new database server.