Chapter 16: Working with External Data
601
You receive a zero-length string (““) as the returned value. If, however, you type the following:
? CurrentDB.TableDefs(“xlsProducts”).Connectyou receive a much different result. Access returns a long string that looks something like this
(although the path indicated at the end of this string may point to a different location):
Excel 8.0;HDR=NO;IMEX=2;DATABASE=C:\Data\CollectibleMiniCars.xlsIn the first case, tblContacts is part of the current database, and Access finds it without any
trouble. xlsProducts, on the other hand, is linked to an external Excel file. The Connect prop-
erty of the xlsProducts contains information that Access uses to physically locate the Excel file
and form a link to it.
The difference between the Connect property for tblContacts and xlsProducts is where the
tables originate. The Connect property of an Access table found within the current database is a
zero-length string because the table originates in the local database. There’s nothing to connect to, or
more appropriately, by default, the connection always exists. However, your ODBC, Excel, and
linked Access data sources will always have a Connect property that explicitly tells Access what type
of data is contained in the linked data source, and where the data source file can be found.
The Connect property string is composed of a number of different parameters, some of which are
required, depending on the type of external data you’re using. If you’re accessing one of the
Indexed Sequential Access Method (ISAM) formats that Access directly supports (Excel, dBASE,
FoxPro, and so on), the Connect string is much more abbreviated, taking this form:
Object.Connect=“Type;DATABASE=Path”where Object is the name of the object variable for your TableDef, and Type is the type of data-
base you’re connecting to, such as dBASE IV, Excel 8.0, Text, and so on.
The Path parameter can be the complete path to the file, not including the filename itself, or it
might include the filename as well, depending on the type of data source. For example, when con-
necting to another Access table, you include the entire path, like C:\Access\Samples\Nwind.
mdb. The same is true of an Excel file.
When connecting to a dBASE file, however, you have to tell Access only the path to the file, not the
.dbf file itself. The difference is in whether the object you’ll be connecting to exists within
another object, or whether the table is the file that you’re going after. That’s where the
SourceTableName property comes in.
The SourceTableName property tells Access which object to take data from. If you want to con-
nect to a dBASE file, you want your table definition to come from the .dbf itself. If, however,
you’re connecting to an Excel file, you might want the table to be based on a range of cells or a sin-
gle worksheet within the .xls file, not the entire spreadsheet. Connecting to another Access
.accdb or .mdb is the same way. To link to the Customers table in Northwind.accdb, your
Connect string tells Access that the value of the DATABASE parameter is C:\Access\
Samples\Northwind.mdb and that the SourceTableName property of your TableDef is