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.
- Make sure that the option “Auto Index on Import/Create” has the value ID in the entry line.
- 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.
- Save the table and choose Yes to create a primary key.
- 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.
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
Extra: Code To Transfer All Tables
Setup a brand new database and Link to Original database
Now link to resultant database (maybe SQL)
Here is the code
'This code assumes that there are X linked tables in Original database 'and X linked tables in the target database that have a suffix of 1 in the Linking database eg Customers1 'Code then deletes all data in the target database. 'Then all tables are moved from the Original database to Target database 'There are no relationships in the target database Dim obj As AccessObject Dim dbs As Object Set dbs = Application.CurrentData ' Search for open AccessObject objects in AllTables collection. For Each obj In dbs.AllTables 'First delete all the records in the dataonly accdb database If Right(obj.Name, 1) = "1" Then ' Delete data in target table DoCmd.RunSQL "delete from " & obj.Name End If Next obj For Each obj In dbs.AllTables 'Now transfer all records from 1 table to the other If Right(obj.Name, 1) <> "1" And Left(obj.Name, 4) <> "msys" And Left(obj.Name, 4) <> "usys" Then DoCmd.RunSQL "INSERT INTO " & obj.Name & "1 SELECT * FROM " & obj.Name End If Next obj
