ODBC Hell continued
Well, the migration of a client of mine’s MS Access system to SQL 2005, did not finish without any headaches. But finally it seems to be stable and the system is live.
One thing I’ve learned during this exercise is that MS Access has a tendency to teach bad habits. It’s an incredibly easy to use and powerful database platform, BUT, the fact that it does not enforce some basic database design rules is worrying.
For one, you can happily design tables without any primary keys. Now this works very nicely whilst the application is storing and retrieving it’s data from access, but as soon as you use the The Microsoft SQL Server Migration Assistant, to migrate the data to SQL and use the MS Access application as the front end, you start running into some interesting ODBC related data issues.
First and most important. When creating an ODBC data source for SQL 2005, use SQL Native Client, it’s the new ODBC interface for SQL server. You do get a few funnies when using the old SQL Server ODBC interface.
When linking an upgraded table(SQL server table) , without a primary key, Access will prompt you to select the field that uniquely identifies the table. This is a difficult task, considering you can’t really know without a primary key. One work around is to select up to 10 field that could possibly identify a record uniquely. This works, but is still risky, since there is no guarantee that the fields will always be unique per record.
Another issue I’ve discovered is that every query in MS Access that must return an editable recordset, must include the table’s primary key or unique fields. Thinking about all this it does make sense. The fact that MS Access allows users to get away with such design flaws in their databases is concerning.
I know of a few consultants/developers who cut their database development teeth with MS Access, myself included. In the last few months I’ve worked with a few "legacy" systems written in Access and it is obvious that the guys that wrote them, was learning as they go along, despite this fact they still billed the client a fairly large amount for their work. I have no problem with billing for your work, but…try to limit the amount of time billed for learning.
