Working with or without a DSN

This month, Doug Steele answers questions about connecting to external databases like SQL Server and Oracle.

Okay, I know how to work with desktop data sources other than Access, such as Excel or dBase. What about using other databases such as SQL Server or Oracle?

Connecting to a data source works a bit differently when you use ODBC to connect. Typically, you use a DSN (Data Source Name) to contain all of the information required to connect to your external data source. But using a DSN means that your application is now no longer self-contained, since you must ensure that the DSN exists on all user machines as well. (Okay, I know that a database with external data sources really can’t be considered “self-contained,” but hopefully you understand what I’m trying to say!) Note that when you’re attempting to link to an external data source using ODBC through the GUI, you have no choice but to use a DSN.

Last month I answered the question, “How can I tell where the back end is supposed to be?” Answering the same question for ODBC databases becomes a two-part exercise. The Connect property for a table that’s been linked from an ODBC database will contain something like “ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers”, which really isn’t enough for you to know where the database is located. You need to look at the details of the DSN (named Publisher in this case) in order to know more details.

You can find the DSNs that have been defined for you and your computer by using the Windows Control Panel. Once you’ve opened the Control Panel, look for the “Data Sources (ODBC)” icon. Those of you running Windows XP with your Control Panel set to Category View, which is the default, need to look under “Other Control Panel Options” first. Once you’ve found that, you need to know whether you’re dealing with a User DSN (available only to you), a System DSN (available to all users of the computer), or a File DSN (available to all users of the computer who have permission to access the file that contains the information). Once you’ve found your DSN, then (and only then) can you look at it to determine where your data actually resides.

How do my users get the same information in their DSNs as I have on my machine?

Hopefully, at this point it’s obvious that when you distribute your application to other users you also must distribute any DSNs referenced by your application.

Now, all the information in a User or System DSN is stored in the Windows Registry. Specifically, each DSN is contained in either HKEY_CURRENT_USER\SOFTWARE\ODBC (in the case of User DSNs) or HKEY_LOCAL_MACHINE\SOFTWARE\ODBC (in the case of System DSNs). There will be two sets of entries for each: one in the ODBC.INI\ODBC Data Sources section, indicating the name of the DSN (and what driver it uses), and one in the ODBCINST.INI, containing the details of the DSN. For example, the following represents the details extracted from the Registry for a SQL Server DSN named StaffingTest, which points to database StaffingModelTest on server YORSQL06:

If you want to create an identical DSN on another machine, all you need to do is create a .REG file containing all of the above, and then merge it with the Registry on the other machine. Your users can do this by double-clicking on the .REG file, for instance.

Another option is to have your application dynamically create the DSN in your application’s code. There are a couple of ways of doing this. One is to use the SQLConfigDataSource API. To use it, you need to add the following declarations to your program:

The following code will create a DSN identical to the DSN I showed you before:

To learn more about this API, check out http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlconfigdatasource.asp at the Microsoft MSDN site.

Another approach, since you’ve seen what’s stored in the Registry associated with a DSN, is simply to use the appropriate API calls to write to the Registry. To do that, you’ll need the following declarations:

Now, to create the same DSN as I showed before, you can use the following code. The first thing that’s required is to declare the variables that are going to be used, and to specify the values of the various DSN parameters:

Once that’s done, you create the key for the actual DSN information by calling the RegCreateKey function:

The next step is to set the values associated with that new key by calling the RegSetValueEx function:

Now that you’ve assigned the values, close the key using RegCloseKey:

However, that’s only one of the keys. So, once that’s all done, you must create the other Registry key, assign it values, and then close it:

If you want more information about creating Registry keys through API calls, a good place to start is http://msdn.microsoft.com/library/en-us/sysinfo/base/registry.asp.

Having to create DSNs on all of my user machines is a nuisance. Is there any other way?

As a matter of fact, there is. Access is capable of using DSN-less connections. While you can’t create a linked table as DSN-less through the GUI, you can create the connection through code.

What I typically do is create the linked tables using a DSN, and then run the code below to convert all of the linked tables to DSN-less before deploying the application. I do this by capturing the necessary information about the existing linked tables, deleting the existing links, and then re-creating the links without referring to the DSN.

I recently got into a discussion about this process in one of the newsgroups on the Internet. The discussion centered around whether or not it was necessary to delete the links first. To be honest, I’m still not sure whether it’s possible to change the connection without deleting and re-creating the existing tables. However, I do know that the process that I’m about to describe does work.

One thing you may notice when you create the linked tables is that sometimes you’re asked to indicate the Unique Identifiers for the linked table. This happens when the table to which you’re linking doesn’t have a unique index that Access can use. If Access can’t identify which field(s) make up the primary key, it can’t reliably determine which record is to be updated in the database. As a result, you won’t be able to perform updates on the linked table. So, even if you’re not intending to use the DSN-connected tables, it’s important that you select a Unique Identifier when you’re linking tables through the Access user interface. The code that follows will take advantage of what you’ve set up, so that you’ll still be able to update the linked tables correctly.

This code block I’m going to show is aimed specifically at SQL Server databases, using Trusted Connection. If you want to use some other DBMS, or if you don’t want to use Trusted Connection, you’ll need to alter the value of strConnect before you use it. Carl Prothman has ODBC DSN-less connection strings at www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm. If you can’t find what you’re looking for there, drop me a line, and I’ll see whether I can help Carl find the string you need.

One other thing: If you check, you’ll see Carl shows the connection string for Trusted_Connection SQL Server as:

While this connection string works fine in an ADO Connection object, when you’re using it to create a linked table (or a pass-through query), you need to preface it with the string “ODBC;” to give:

Okay, enough talk. Here’s how to relink your tables without requiring a DSN.

The first thing I do is define a user-defined type that I can use to capture the relevant information. What’s required is the name of the table, the name of the table to which it’s linked in the external database, and the information about the table’s Unique Identifier:

Once that’s taken care of, I determine the maximum number of entries that could possibly be stored in the array typTables. In actual fact, I know that I’ll never require all of these entries (if nothing else, the information for the normally hidden system tables won’t be used). However, I fix things up afterwards (the reason for over-declaring that array size is that the ReDim statement is expensive in terms of processing demands, so I don’t want to do it more than necessary):

Now I loop through all of the tables in the database, looking for those that have a Connect string defined. For each one, I store the information necessary to be able to re-create the linked table. I’m capturing the actual SQL commands necessary to generate the unique index by calling the function GenerateIndexSQL (I’ll talk more about that function later):

Now that I have enough information to re-create the linked tables, I delete each linked table and re-add it to the TableDefs collection, using the connection string I created, based on the server and database information passed to the routine. As I mentioned earlier, this bit of code is what you need to change if you’re using other than Trusted Connection SQL Server.

If the function GenerateIndexSQL found a Unique Identifier that needs to be re-created, I use the Execute method to re-create the index. When issuing the SQL with the Execute method, I use the dbFailOnError parameter. This means I’ll be able to trap any errors that may occur when I run the SQL:

That’s it. I’ve now re-created all of my connected tables as DSN-less connections. All that’s left is some cleanup work. I also include some specific error handling code if the SQL to create the Unique Identifier doesn’t work. For reasons I’ve never been able to determine, sometimes the index can’t be re-created, and error 3291 (“Syntax Error in CREATE INDEX statement”) gets raised. If I do get that error, I report the error, but continue creating the linked tables:

The last thing I should do is discuss how to generate the SQL necessary to re-create the Unique Identifier. When you link a table through the Access user interface and have to specify the unique index, Access creates an index named __uniqueindex (that’s with two underscore characters). The following function, given a table name, determines whether or not there’s an index by that name. I could have looped through all of the indexes associated with the table, looking for one named __uniqueindex, but it’s less work to simply try to open that index, and trap the error that’s raised if the index doesn’t exist. If the index exists, I determine all of the fields included in the index, and generate a SQL statement that looks like this:

If the index named __uniqueindex for the tablename passed to the function doesn’t exist, my code will raise error 3265 (“Item not found in this collection”). When that happens, it simply means that it wasn’t necessary for Access to generate a unique index and so I don’t need to generate the SQL, either. I exit the function without reporting an error. If any other error is generated, I report it using a message box. I put square brackets around the Table and Field names in the SQL, just in case there are field names with embedded spaces:

I’m using DAO for this code, so if you’re using Access 2000 or 2002, ensure that you’ve set a reference to DAO in order to get the code to work.

Though Access doesn’t generate them automatically, you can use the same DSN-less connection string as the Connect property for any pass-through queries you might have in your application. For those, though, you can simply change the value of the existing Connect string, without having to delete and re-create the object.

 

Or You May Want to Try These Articles

Client/Server Development with Access

Access Answers: Client/Server Issues

Client/Server Development with Access

Migrating Access (and Outlook Data) to SQL Server

Improving on VBA with Transact-SQL

!An Access Explorer for SQL Server Tables

Using Access to Prototype for SQL Server

 

This entry was posted in Old Material. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.