Summary
This article discusses some of the subtleties that you can apply to your database tables prior to upsizing your tables to SQL Server. In this article, I will show you how to fix up some of your Access backend before you make the conversion. 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 off 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 tempt 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, 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 a 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 seem 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 its 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 I will be back in the relationship window. To complete the exercise, I 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 issue 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 VariantFor 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 IfvarMsg = chkFKeyLength(strTable)
If Not IsNull(varMsg) Then
MsgBox varMsg & strTable
End IfEnd 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.IndexcheckPrimaryKey = Null
Set dbData = CurrentDb
dbData.TableDefs.RefreshOn Error Resume Next
‘Enumerate TableDefs collection.
For Each tdfLoop In dbData.TableDefs
If tableReq = tdfLoop.Name ThenFor 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 IfNext 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 sameDim dbData As DAO.Database
Dim relLoop As DAO.RelationSet dbData = CurrentDb
chkFKeyLength = Null
On Error Resume Next
dbData.Relations.RefreshFor Each relLoop In dbData.Relations
With relLoopIf 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 relLoopdbData.Close
Set dbData = NothingEnd Function
So that is 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, there is third party software available that fixes these problems and many others as part of the upsizing process.
Postscript ~ Get ready for the Economic Upturn
If you are in the unfortunate position of not having much paid work on in this global economic downturn, now is the time to upgrade your skills because if you are a skilled developer who can help people solve business problems, positive times will not be far away. So as it is likely that you are an Access practitioner, I suggest that the best way to improve your skills is to push into areas where Access provides a stepping stone. To me SQL Server is one of those areas and here is what you have to do.
1. Identify the database that you are most proud of and convert it to run with a SQL Server backend.
2. Do not stop when things get hard, solve all the problems.
3. It would be best if you then started using that database in your day to day work.
4. When that works, start converting the Access queries to Views, Pass Thru Queries and Stored Procedures
One thing you shouldn’t do though. is become 100% immersed in your unpaid projects because its always better to write software that people need. For this, you will need to make sure that you attend conferences, participate in online forums and contact all the people that can help you get back into the thick of things. Good luck to you in your endeavours.
Garry Robinson from Sydney, Australia
Download CheckTables.zip
» See All Articles by Columnist Garry Robinson
