by Garry Robinson
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
Optimizing Microsoft Office Access Applications Linked to SQL Server
Import or link to SQL Server data--Access 2007 Help
Migration Considerations for Access 2007
Post migration gotchas
Get Good Help Here
If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions
We have converted vb123.com to Expression Web, contact us if we can help you move to the latest Microsoft web tool.
About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia