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.
Or You May Want to Try These Articles
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
