In my experience, one of the most frequent data-related helpdesk calls is for ODBC errors.
ODBC is a way to connect Microsoft Access and other Office products to a database such as SQL Server. You can set your ODBC connections by going to:
START–>Control Panel–>Administrative Tools (in Vista’s classic view)–>Data Sources(ODBC)
In a corporate situation with a Domain it is common for the administrator to set up a login script to run on each desktop that will make sure a bunch of standard ODBC connections are in place.
ODBC errors can be very difficult to resolve. The error messages themselves are rarely helpful – often saying nothing more than “ODBC Error”. Here are a list of some common causes of the annoying ODBC error:
- 32 bit/64 bit architecture – The ODBC error has to have the same architecture as the *APPLICATION* not the Operating System. If the user is running Windows 7 64-bit and Microsoft Access 32-bit then the ODBC driver for the database they need to connect to must be 32-bit
- Bad SQL Code – This can take many forms. If the user is trying to open a query that points to a View or Stored Procedure or Function, and if there is a problem with that object, then the user will get an ODBC error. Also keep and eye out for triggers failing on the database after Inserts, Updates, and Deletes. One of the most common situations I have encountered is where an Access or Excel query uses a view and the columns in the view may change – removed or an altered data type.
- Permissions – If the user is a member of an Active Directory group that has permissions to an object on the database, or if the user’s network login has direct access to an object – sometimes administrators can get accidentally remove or change a user’s permissions indirectly during a clean-up effort. Or sometimes if a generic SQL user is used, the password may get locked or changed.
- User Tampering – If a temporary situation is encountered by a power user (or at least someone who thinks of himself as a power user) they may try to correct the situation, and actually end up breaking the ODBC connection. One example of this is changing the password setting from Active Directory to SQL and then typing in their network username and password.
This doesn’t even begin to scratch the surface of the myriad of problems I have encountered, but these are some frequent issues – reliable first places to look.