Databases Reference
In-Depth Information
database. This also changes the DSN definition to include the specified
database. It is a good idea to always include the DATABASE parameter
when you use a DSN. This will ensure that you connect to the proper
database because another user may have changed the default database
parameter since you last checked the DSN definition.
This seems to be saying that when we omit the provider portion of the connection string
(which can always be supplied using the Provider property), an OLE DB connection
string is identical with an ODBC connection string. Of course, this begs the question:
“How do we compose an ODBC connection string?”
The simplest answer is to let Windows do this for us. However, the starting point for this
is a DSN that we must create, probably using the ODBC Administrator. The
GetODBCConnectString procedure in Example 17-6 will extract a connection string from
a DSN. The procedure first uses DAO (yes, DAO) to create an ODBC workspace. Then
the
OpenConnection
method:
Set c = ws.OpenConnection("", dbDriverPrompt, , "ODBC;")
causes Windows to display the ODBC Administrator so we can create a DSN. Once this
is done, the procedure prints the complete connection string.
Example 17-6. The GetODBCConnectString procedure
Private Sub GetODBCConnectString( )
' Create an ODBC workspace and get the connect string for a DSN
Dim db As Database, ws As Workspace, rs As Recordset
Dim cn As Connection
Set ws = CreateWorkspace("NewODBC", "admin", "", dbUseODBC)
' The following causes a prompt for the DSN
Set cn = ws.OpenConnection("", dbDriverPrompt, , "ODBC;")
Debug.Print cn.Connect
cn.Close
End Sub
Actually, there are two types of ODBC connection strings—DSN and DSN-less. Here are
examples of the two types of connection strings for a connection to an Excel worksheet
and to a text file. These strings were obtained using the
GetODBCConnectString
procedure:
' Excel DSN-less connection string
ODBC; _
DBQ=D:\BkAccessII\Connect.xls; _
DefaultDir=D:\bkado; _
Driver={Microsoft Excel Driver (*.xls)}; _
