Connect Property

Applies To

Database Object, QueryDef Object, TableDef Object.

Description

Sets or returns a value that provides information about the source of an open database, a database used in a pass-through query, or an attached table. For QueryDef objects, Database objects, attached tables, and TableDef objects not yet appended to a collection, this property setting is read/write. For a base table, this property is read-only.

Syntax

object.Connect = [databasetype;[parameters;]]

The Connect property syntax has these parts.

Part	Description

object	An object expression that evaluates to an object in the Applies To list.
databasetype	A string expression that specifies a database type. For Microsoft Jet databases, exclude this argument; if you specify parameters, use a semicolon (;) as a placeholder. The data type is String.
parameters	A string expression that specifies additional parameters to pass to ODBC or installable ISAM drivers. Use semicolons to separate parameters. The data type is String.

Settings

The Connect property setting is a String composed of a database type specifier and zero or more parameters separated by semicolons. The Connect property is used to pass additional information to ODBC and certain ISAM drivers as needed. It isn't used for Jet databases, except for those containing attached tables, to allow SQL pass-through queries.

To perform an SQL pass-through query on a table attached to your .mdb file, you must first set the Connect property of the attached table's database to a valid ODBC connect string.

For a TableDef object that represents an attached table, the Connect property setting consists of one or two parts (a database type specifier and a path to the database), each of which ends with a semicolon.

The path as shown in the following table is the full path for the directory containing the database files and must be preceded by the identifier "DATABASE=". In some cases (as with Jet, Btrieve®, and Microsoft Excel databases) a specific filename is included in the database path argument.

The following table shows possible database types and their corresponding database specifiers and paths for the Connect property setting.

Database type	Specifier	Path

Database using the Jet database engine	"[database];"	"drive:\path\filename.mdb"
dBASE III®	"dBASE III;"	"drive:\path"
dBASE IV®	"dBASE IV;"	"drive:\path"
dBASE 5	"dBASE 5;"	"drive:\path"
Paradox 3.x	"Paradox 3.x;"	"drive:\path"
Paradox 4.x	"Paradox 4.x;"	"drive:\path"
Paradox 5.x	"Paradox 5.x;"	"drive:\path"
Btrieve	"Btrieve;"	"drive:\path\filename.ddf"
FoxPro® 2.0	"FoxPro 2.0;"	"drive:\path"
FoxPro 2.5	"FoxPro 2.5;"	"drive:\path"
FoxPro 2.6	"FoxPro 2.6;"	"drive:\path"
Excel 3.0	"Excel 3.0;"	"drive:\path\filename.xls"
Excel 4.0	"Excel 4.0;"	"drive:\path\filename.xls"
Excel 5.0	"Excel 5.0;"	"drive:\path\filename.xls"
Excel 7.0	"Excel 7.0;"	"drive:\path\filename.xls"
Text	"Text;"	"drive:\path"
ODBC	"ODBC; DATABASE=defaultdatabase; UID=user; PWD=password; DSN=datasourcename LOGINTIMEOUT=seconds" (This may not be a complete connection string for all servers; it's just an example.)	None

Remarks

If the specifier is only "ODBC;", a dialog box listing all registered ODBC data source names is displayed by the ODBC driver so the user can select a database.

If a password is required but not provided in the Connect property setting, a login dialog box is displayed the first time a table is accessed by the ODBC driver and again if the connection is closed and reopened.

For Jet database base tables, the Connect property setting is a zero-length string ("").

You can set the Connect property for a Database object by providing a source argument to the OpenDatabase method. You can check the setting to determine the type, path, user ID, Password, or ODBC data source of the database.

For a QueryDef object, you use the Connect property with the ReturnsRecords property to create an ODBC SQL pass-through query. The databasetype of the connection string is "ODBC;", and the remainder of the string contains information specific to the ODBC driver used to access the remote data. For more information, see the documentation for the specific driver.

Note

The Connect property must be set before the ReturnsRecords property is set.

See Also

OpenDatabase Method, ReturnsRecords Property.

Example

This example creates a TableDef object in the specified database; sets its Connect, Name, and SourceTableName properties; and then appends it to the TableDefs collection.

Function ConnectSource () As Integer
    Dim dbsLocal As Database, tdfPDXAuthor As TableDef
    Set dbsLocal = DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
    Set tdfPDXAuthor = dbsLocal.CreateTableDef("PDXAuthor")
    ' Attach Paradox table Author in database C:\PDX\PUBLISH.
    tdfPDXAuthor.Connect = "Paradox 4.x;DATABASE=C:\PDX\PUBLISH"
    tdfPDXAuthor.SourceTableName = "Author"
    dbsLocal.TableDefs.Append tdfPDXAuthor    ' Attach table.
    ConnectSource = True
End Function

Example (Microsoft Access)

The following example creates a TableDef object in the specified database. The procedure then sets its Connect, Name, and SourceTableName properties and appends the object to the TableDefs collection.

Sub ConnectSource()
    Dim dbs As Database, tdf As TableDef

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.CreateTableDef("PDXAuthor")
    ' Attach Paradox table Author in database C:\PDX\Publish.
    tdf.Connect = "Paradox 4.x;DATABASE=C:\PDX\Publish"
    tdf.SourceTableName = "Author"
    dbs.TableDefs.Append tdf
End Sub

Example (Microsoft Excel)

This example attaches the table PRODUCT.DBF (a dBASE IV table located in the \Program Files\Common Files\Microsoft Shared\MSquery folder) to the NWINDEX.MDB database. (On Windows NT, PRODUCT.DBF is located in the \WINDOWS\MSAPPS\MSQUERY folder.)

To create the NWINDEX.MDB database, run the Microsoft Excel example for the CreateDatabase method.

Const sourceDir = "C:\Program Files\Common Files\Microsoft Shared\"
Dim nWindEx As Database, tDef As TableDef
Dim dataSource As String
dataSource = "dbase IV;DATABASE=" & sourceDir & "MSquery"
Set nWindEx = Workspaces(0).OpenDatabase(Application.Path _
    & "\NWINDEX.MDB")
Set tDef = nWindEx.CreateTableDef("Product")
tDef.Connect = dataSource
tDef.SourceTableName = "Product"
nWindEx.TableDefs.Append tDef
nWindEx.Close