Working with or without a DSN

<< Click to Display Table of Contents >>

Navigation:  SQL Server and Access >

Working with or without a DSN

Doug Steele          

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:

REGEDIT4

[HKEY_CURRENT_USER\SOFTWARE\ODBC]

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI]

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]

"StaffingTest"="SQL Server"

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\StaffingTest]

"Driver"="C:\\WINNT\\SYSTEM32\\sqlsrv32.dll"

"Description"="StaffingTest"

"Server"="YORSQL06"

"Database"="StaffingModelTest"

"Trusted_Connection"="Yes"

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:

Private Const ODBC_ADD_DSN = 1

Private Const vbAPINull As Long = 0&

Private Declare Function SQLConfigDataSource _

  Lib "ODBCCP32.DLL" ( _

  ByVal hwndParent As Long, _

  ByVal fRequest As Long, _

  ByVal lpszDriver As String, _

  ByVal lpszAttributes As String) As Long

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

Sub CreateDSN()

Dim lngRet As Long

Dim strDriver As String

Dim strAttributes As String

  strDriver = "SQL Server" & _

    "SERVER=YORSQL06" & Chr$(0) & _

    "DESCRIPTION=StaffingTest" & Chr$(0) & _

    "DSN=StaffingTest" & Chr$(0) & _

    "DATABASE= StaffingModelTest" & Chr$(0)& _

    "Trusted_Connection=Yes" & Chr(0)

  lngRet = SQLConfigDataSource(vbAPINull, _

    ODBC_ADD_DSN, strDriver, strAttributes)

  If lngRet = 0 Then

     MsgBox "Create Failed"

  Else

     MsgBox "DSN Created"

  End If

End Sub

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:

Private Const REG_SZ = 1

Private Const HKEY_CURRENT_USER = &H80000001

Private Declare Function RegCreateKey _

  Lib "advapi32.dll" Alias "RegCreateKeyA" ( _

  ByVal hKey As Long, _

  ByVal lpSubKey As String, _

  phkResult As Long) As Long

Private Declare Function RegSetValueEx _

  Lib "advapi32.dll" Alias "RegSetValueExA" ( _

  ByVal hKey As Long, _

  ByVal lpValueName As String, _

  ByVal Reserved As Long, _

  ByVal dwType As Long, _

  lpData As Any, _

  ByVal cbData As Long) As Long

Private Declare Function RegCloseKey _

  Lib "advapi32.dll" ( _

  ByVal hKey As Long) As Long

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:

Dim lngKeyHandle As Long

Dim lngResult As Long

Dim strDatabaseName As String

Dim strDataSourceName As String

Dim strDescription As String

Dim strDriverName As String

Dim strDriverPath As String

Dim strRegional As String

Dim strServer As String

  strDataSourceName = "StaffingTest"

  strDatabaseName = "StaffingModelTest"

  strDescription = "StaffingTest"

  strDriverPath = "C:\\WINNT\\SYSTEM32\\sqlsrv32.dll"

  strServer = "YORSQL06"

  strDriverName = "SQL Server"

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

  lngResult = RegCreateKey(HKEY_CURRENT_USER, _

    "SOFTWARE\ODBC\ODBC.INI\" & _

    strDataSourceName, lngKeyHandle)

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

  lngResult = RegSetValueEx(lngKeyHandle, _

    "Database", 0&, REG_SZ, _

    ByVal strDatabaseName, _

    Len(strDatabaseName))

  lngResult = RegSetValueEx(lngKeyHandle, _

    "Description", 0&, REG_SZ, _

    ByVal strDescription, _

    Len(strDescription))

  lngResult = RegSetValueEx(lngKeyHandle, _

    "Driver", 0&, REG_SZ, _

    ByVal strDriverPath, _

    Len(strDriverPath))

  lngResult = RegSetValueEx(lngKeyHandle, _

    "Server", 0&, REG_SZ, _

    ByVal strServer, _

    Len(strServer))

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

  lngResult = RegCloseKey(lngKeyHandle)

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:

  lngResult = RegCreateKey(HKEY_CURRENT_USER, _

    "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", _

    lngKeyHandle)

  lngResult = RegSetValueEx(lngKeyHandle, _

    strDataSourceName, 0&, REG_SZ, _

    ByVal strDriverName, Len(strDriverName))

  lngResult = RegCloseKey(lngKeyHandle)

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:

oConn.Open "Driver={SQL Server};" & _

           "Server=MyServerName;" & _

           "Database=myDatabaseName;" & _

           "Trusted_Connection=yes"

If you translate this into a string, you get this:

Driver={SQL Server};Server=MyServerName;

Database=myDatabaseName; Trusted_Connection=yes

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:

ODBC;Driver={SQL Server};Server=MyServerName;

Database=myDatabaseName;Trusted_Connection=yes

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:

Type TableDetails

  TableName As String

  SourceTableName As String

  IndexSQL As String

End Type

The first part of the sub declares the necessary variables:

Sub FixConnections(ServerName As String, _

    DatabaseName As String)

On Error GoTo Err_FixConnections

Dim dbCurrent As DAO.Database

Dim intLoop As Integer

Dim intMaxToChange as Integer

Dim intToChange As Integer

Dim strConnect As String

Dim tdfCurrent As DAO.TableDef

Dim typTables() As TableDetails

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):

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

intMaxToChange = dbCurrent.TableDefs.Count

ReDim typTables(0 To (intMaxToChange - 1))

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):

  intToChange = 0

  For Each tdfCurrent In dbCurrent.TableDefs

    If Len(tdfCurrent.Connect) > 0 Then

      typTables(intToChange).TableName = _

        tdfCurrent.Name

      typTables(intToChange).SourceTableName = _

        tdfCurrent.SourceTableName

      typTables(intToChange).IndexSQL = _

        GenerateIndexSQL(tdfCurrent.Name)

      intToChange = intToChange + 1

    End If

  Next

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:

  ReDim Preserve typTables(0 To (intToChange — 1))

  strConnect = "ODBC;DRIVER={sql server};" & _

    "DATABASE=" & DatabaseName & _

    ";SERVER=" & ServerName & _

    ";Trusted_Connection=Yes;"

  For intLoop = LBound(typTables) To _

    UBound(typTables)

    dbCurrent.TableDefs.Delete _

      typTables(intLoop).TableName

    Set tdfCurrent = _

      dbCurrent.CreateTableDef(typTables(intLoop).TableName)

    tdfCurrent.Connect = strConnect

    tdfCurrent.SourceTableName = _

      typTables(intLoop).SourceTableName

    dbCurrent.TableDefs.Append tdfCurrent

    If Len(typTables(intLoop).IndexSQL) > 0 Then

      dbCurrent.Execute typTables(intLoop).IndexSQL, _

        dbFailOnError

    End If

  Next

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:

End_FixConnections:

  Set tdfCurrent = Nothing

  Set dbCurrent = Nothing

  Exit Sub

Err_FixConnections:

  If Err.Number = 3291 Then

    MsgBox "Problem creating the Index using" & vbCrLf & _

        typTables(intLoop).IndexSQL, _

        vbOKOnly + vbCritical, "Fix Connections"

    Resume Next

  Else

    MsgBox Err.Description & _

      " (" & Err.Number & ") encountered", _

      vbOKOnly + vbCritical, "Fix Connections"

    Resume End_FixConnections

  End If

End Sub

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:

CREATE INDEX __uniqueindex ON [tablename] (

  [Field1], [Field2], ...)

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:

Function GenerateIndexSQL(TableName As String) _

    As String

On Error GoTo Err_GenerateIndexSQL

Dim dbCurr As DAO.Database

Dim idxCurr As DAO.Index

Dim fldCurr As DAO.Field

Dim strSQL As String

Dim tdfCurr As DAO.TableDef

  Set dbCurr = CurrentDb()

  Set tdfCurr = dbCurr.TableDefs(TableName)

  If tdfCurr.Indexes.Count > 0 Then

    Set idxCurr = tdfCurr.Indexes("__uniqueindex")

    If idxCurr.Fields.Count > 0 Then

      strSQL = "CREATE INDEX __UniqueIndex " & _

        ON [" & TableName & "] ("

      For Each fldCurr In idxCurr.Fields

        strSQL = strSQL & "[" & fldCurr.Name & "], "

      Next fldCurr

' Remove the trailing comma and space

      strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"

    End If

  End If

End_GenerateIndexSQL:

  Set fldCurr = Nothing

  Set tdfCurr = Nothing

  Set idxCurr = Nothing

  Set dbCurr = Nothing

  GenerateIndexSQL = strSQL

  Exit Function

Err_GenerateIndexSQL:

  If Err.Number <> 3265 Then

    MsgBox Err.Description & " (" & Err.Number & _

      ") encountered", _

      vbOKOnly + vbCritical, _

      "Generate Index SQL"

  End If

  Resume End_GenerateIndexSQL

End Function

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.

 

 

Your download file is called 408STEELE.ZIP in the file SA2004-08down.zip

This can be purchased with all the other downloads on this page

 

Or You May Want to Try These Articles

Client-Server Utilities

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