Putting It All Together: Building a Connection String

Before you create a connection string, you need to know the data source you will connect to and the DAO object you will use with the data source. An external data source will be either an ODBC database or an ISAM (desktop) database.

The recommended way to connect to an ODBC database is through an ODBCDirect workspace object. For more information about ODBCDirect, see Chapter 9 “Developing Client/Server Applications.” However, there may be situations where you will connect to an ODBC data source through a Microsoft Jet workspace; for example, to take advantage of specific Microsoft Jet features such as heterogeneous joins or indexed searches. If you are using a version of DAO earlier than 3.5, you must connect to an ODBC data source through a Microsoft Jet workspace.

You can use the DAO Connection, Database, QueryDef, and TableDef objects to connect to an external data source. The following table shows which objects can connect to which database type and workspace type.

A complete connection string includes all information necessary to connect to and open an external data source on a local drive, network, or ODBC-compliant database server. Connection strings consist of a database type followed by a database name or a DSN (ODBC data sources only). If the external data source requires additional information, such as a password, you can include this in the connection string as well. You can set a connection string by using the OpenDatabase method, the CreateTableDef method, or the Connect property.

For ODBCDirect workspaces, you can also set the connection string by using the OpenConnection method. An ODBC connection string uses the same syntax, regardless of which object you connect it to and regardless of whether you connect through an ODBCDirect or Microsoft Jet workspace.

Connecting to an ISAM database can only be done through a Microsoft Jet workspace. The IISAM drivers expose the database to Microsoft Jet as a Microsoft Jet database supporting all the functionality of a native Microsoft Jet database. Any IISAM driver (except Microsoft Exchange or an external Microsoft Jet database) can use FTP or HTTP protocols to connect to a file on an intranet or the Internet.

All IISAM drivers accept a connection string that uses the same basic syntax:

"identifier; source; options"

The identifier argument is a keyword that specifies the type of file being opened. The “Specifying Source Database Types” section earlier in this chapter contains a table with the identifier for each ISAM database listed as “Source Database Types.” No identifier argument is used when you are connecting to an external Microsoft Jet database.

The source argument contains a drive letter or transfer protocol (such as HTTP), a fully qualified path, and (if needed) a file name that is the file (or the directory containing the file) you want to open. The source argument (except for Microsoft Jet databases) always begins with DATABASE=, for example:

strConnect = "FoxPro 3.0;DATABASE=C:\JetBook\Samples\FoxTables"

The options argument is used to specify additional, optional arguments to the IISAM driver. For example, you can specify a password or indicate whether the first record in a spreadsheet file contains column header information. Multiple arguments are separated by semicolons.

The connection string is slightly different when you are opening or creating an external Microsoft Jet database file. There are two IISAM drivers for Microsoft Jet databases: one for databases created by using Microsoft Jet 2.5 or earlier and another for databases created by using Microsoft Jet 3.0 or later. When you are creating a connection string for an external Microsoft Jet database, the identifier argument is left blank (but the semicolon is included). The source argument is the drive, path, and file name to the .mdb file. The options argument may contain, where needed, the password associated with a password-protected .mdb file. For example, the following code fragment creates a connection string for an external Microsoft Jet database. You could use this code to link the Employees table in the NorthwindTables database to the JetSamples database:

Dim dbs As Database, tdf As TableDef

' Create new TableDef object in a Microsoft Jet database.
Set tdf = dbs.CreateTableDef("LinkedEmployees")
' Link table from another Microsoft Jet database.
With tdf
	.Connect = ";DATABASE=C:\JetBook\Samples\NorthwindTables.mdb"
	.SourceTableName = "Employees"
End With
dbs.TableDefs.Append tdf

The following code fragment sets the connection string by using the OpenDatabase method:

Dim dbs As Database

Set dbs = OpenDatabase("C:\JetBook\Samples\FoxTables\Sales", _
	False, False, "FoxPro 3.0")

Note that the connection information appears in a slightly different format when it is specified in the arguments to the OpenDatabase method. The identifier and options arguments are passed in the connect argument (the fourth argument in the example above), while the source argument is supplied in the first argument without prepending the DATABASE= information.

The following code fragment sets the connection string with the CreateTableDef method; connection information is supplied as arguments in the method. In this example, strDbPath is the path to the Microsoft Jet database:

Dim dbs As Database, tdf As TableDef

Set dbs = OpenDatabase(strDbPath)
Set tdf = dbs.CreateTableDef("Q1Orders1996", 0, "Q1Ord96", _
	"FoxPro 3.0;DATABASE=C:\JetBook\Samples\FoxTables\Sales;")
dbs.TableDefs.Append tdf

The following code fragment sets the connection string by using the Connect property, CreateTableDef method, and SourceTableName property:

Dim dbs As Database, tdf As TableDef

Set dbs = OpenDatabase(strDbPath)
Set tdf = dbs.CreateTableDef("EmployeeSalesTotals")
With tdf
	.Connect = "Paradox 3.X;DATABASE=C:\JetBook\Samples\pdx"
	.SourceTableName = "PDXSales"
End With
dbs.TableDefs.Append tdf

The following code fragment sets the connection string by using the OpenConnection method of an ODBCDirect workspace object:

Dim wrk As Workspace, cnn As Connection

' Create an ODBCDirect workspace.
Set wrk = CreateWorkspace("NewWS", "admin", "", dbUseODBC)
' Open a connection on the workspace.
Set cnn = wrk.OpenConnection("NorthwindConnection", , , _
	"ODBC;DSN=NorthwindSQLDSN;DATABASE=NorthwindSQL;UID=sa;PWD=;")