Preparing To Upgrade Access Tables to SQL Server 2005/2008

by  Garry Robinson

Summary

This article discusses some of the subtleties that you can apply to your database tables prior to upsizing your tables to SQL Server. I do this because once you are in an environment where you have Access as a front-end and a SQL Server back-end, things are going to get more complicated. Another good thing about this article is that it will probably improve your database model and reduce the size of your database at the same time.

Get Stuck into Primary Indexes First

If MS Access is going to talk to a SQL Server database through ODBC, Access requires that a table must have at least one unique index if it is going to allow editing of the data in the table. This means that just about every table in the database will need to have a primary key. Whilst it is possible to use a unique index that is not a primary key, tools like the SQL Server upsizer wizard will force the first unique index in the table to be the primary key.  Therefore I suggest that you have a primary key in every table and you will be on the way to solving your first upgrade issue.
 
Another reason why I like sorting out primary keys is that it generally means that you have a chance to revisit and improve the design of your database model. If you decide not to add a primary key, after you upsize the table you will only be able to read the table and not to write to it.
 
Once you decide that you need to add a primary key to a table, you may not be allowed to add the key because of duplicate values. There are two ways around this. The first way is to add one of those "ugly" AutoNumber fields and make this your primary key.  This is certainly quick and if you resolve to review the key again later, you really are no worse of now than you were before you started. The better way to solve the duplicate items issues is to utilize the Find Duplicates query wizard. In Access 2003, you will find this when you click on the New Query button in the database container. In Access 2007, select the Create Tab in the Ribbon and choose Query Wizard. Finally when you have revised your index, don’t forget to renew the relationship diagram if there are any other related tables.

Unnecessary Indexes and Auto-Numbers

If you do not add a primary index to a table straight away, Access goes out of its way to encourage you to add a unique index. Whilst most DB professionals will see this as a cue to revisit the table design, the DB enthusiast may accept the primary key offered and never give it another thought. Unfortunately the default name for this primary key is “ID” and many a DB enthusiast will fall for this trap. So if you see a lot of these fields in your tables, it is time to spend a number of hours fixing up these less than useful keys. Once Access has played this card, a subsequent trap for the unwary occurs when the lookup table wizard is used. In this instance Access will temp you to 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 this instance, the name (probably "ID”) is also duplicated into the main table leading to more ambiguity.
 
You should clear these unnecessary lookup indices up before you make the transfer or you will forever be searching your Database (relationship) Diagrams and Tables in SQL Server to work out the value of a lookup code.

Watch Out For Those Pesky Auto Indexes

Another Access gotcha “feature” that you can run into during conversions is the Auto index option. This little “nuisance” is located in Access 2003 in the Table/Queries Tab under the menu Tools ~ Options. In Access 2007, you need to click on the Office button, choose Access Options, then choose Object Designers as shown in Figures 1 and 2.  You will find that most database enthusiasts are unlikely to know anything about this option. The result is that there could be many tables in the database with indexes that were not planned for. If you don't understand what this means, try this little exercise.

1. Make sure that the option “Auto Index on Import/Create” has the value ID in the entry line.
 
2. Open a new table in design mode and add a field with any name and then add “ID” to the end of the field name. 
3. Save the table and choose Yes to create a primary key.
4. Now open the table in design view and choose the menu item View ~ Indexes.

You will now find that you have two indexes in the table when it seemed that you only approved one.

Imagine that particular “feature” in a database with 100 tables or more and you can start to see the challenges that can beset a database developed by an database enthusiast with the assistance of the Access table and lookup wizards.

If you think that is implausible, I have recently worked on a database with 1200 indexes on 200 tables. I can guarantee you that 75% of those indexes were not needed.

Figures 1 & 2. Finding and setting the Auto index on creation option.

Different lengths of keys

Another great gotcha that you will experience in a conversion is when you find a relationship between two tables that have different sized fields. Personally I don’t seems to fall for this one very often and I imagine that is because I copy and paste the common fields between each of tables whenever I can.  Nevertheless I imagine that we all fall victim to leaving a text field at ifs default size of 50 characters from time to time. In the SQL Server upsizer Wizard, you would see an error message that looks like the following.

Server Error 1753: Column 'Customers.CustomerID' is not the same length as referencing 
column 'Orders.CustomerID' in foreign key 'Orders_FK00'. Columns participating in a 
foreign key relationship must be defined with the same length.
Server Error 1750: Could not create constraint. See previous errors.

 
So how do we go about fixing a problem like this. Usually I will head to the relationship window, right click on the join between the two tables and delete the relationship. Next I will right click on a table and head into design mode. Now I can change the field size, save the table and L will be back in the relationship window. To complete the exercise, L can now readily add back the relationship between the tables in the relationship window.

Automated Testing

As the readers of this article are probably VBA coders, here is some code that will identify this issues rather that making you wade through your database to find these problems manually. Initially I will demonstrate some VBA that loops through all the tables. For each table, I now call a simple function to check for the existence of a primary key and then a second function to verify 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 am illustrating use the DAO library to retrieve information about the tables. In the first function, the VBA code reviews all the indexes for every table to see if any of them have the Primary property value set to True.

Function checkPrimaryKey(tableReq As String)   As Variant
  
'Check a table for the primary key
 
Dim dbData As DAO.Database
Dim tdfLoop As DAO.TableDef
Dim idxLoop As DAO.Index
 
  checkPrimaryKey = Null
  Set dbData = CurrentDb
  dbData.TableDefs.Refresh
  
On Error Resume Next
 
  'Enumerate TableDefs collection.
  For Each tdfLoop In dbData.TableDefs
    If tableReq = tdfLoop.Name Then
    
      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 "
    End If
   
  Next tdfLoop
   
   
checkPrimaryKey_exit:
 
  Set dbData = Nothing
 
End Function

In the second function, the code 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, this function returns a descriptive error that can then be tested for and displayed in the calling subroutine.

Function chkFKeyLength(tableReq As String) _
 As Variant
 
'check that the length of the fields on both side
'of a relationship are the same
 
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
      
'Check the foreign key relationship is the same size
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

Now that how you might write code to test for upsizing issues that you can commonly encounter in your databases. If this all seems a little complex for you, my company is a reseller of software that fixes these problems and many others as part of the upsizing process.  http://www.vb123.com/up for more on this tool.

SQL Server Upsizing The Easy Way

If you want to upgrade to SQL Server, you can use the SQL Upsizing Wizard that comes with Access (which may actually do the job) or you can use the MUST tool written by Access MVP Andy Couch.  It just depends how much time you want to spend and whether you want to go to the next level and transfer queries as well.

Author Bio.

Garry Robinson runs GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues,  visit his companies popular web site at  http://www.vb123.com/  or sign up for his Access email newsletter by sending a blank email here. The web site features many Access resource sand software  that are used by  more than 10,000 readers a month. To find out about Garry’s book which is called “Real World Microsoft Access Database Protection and Security”, point your browser to http://www.vb123.com/map/. You can find Garry’s contact details at  … www.gr-fx.com

Other Pages On This Site You Might Like To Read

Here is some code to strip out the DBO prefix on SQL Server linked tables
Replace Your File API’s With The FileDialog Object
Five Questions to Garry from Mike Gunderloy
Access Traps for the Naïve Developer

 

  Your Sample Database Is Called   "CheckTables.zip"

Sample database is suited to all versions of Access

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

External Links

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

Import or link to SQL Server data--Access 2007 Help
http://office.microsoft.com/en-us/access/HA102004941033.aspx

Migration Considerations for Access 2007
http://technet.microsoft.com/en-us/library/cc178973.aspx

Post migration gotchas
http://thatlldoit.com/howtosarticles.aspx#sqlbackend