Opening Other Database File Formats by Using the Microsoft Jet 4.0 OLE DB
Provider
The Microsoft Jet database engine can be used to access data in other
database file formats, data in Excel and Lotus spreadsheets, and textual data
stored in tabular format through installable ISAM drivers. In order to open
external formats supported by the Jet database engine by using ADO and the
Microsoft Jet 4.0 OLE DB Provider, you specify the database type by using the Extended
Properties property of the Connection object. The following procedure
shows how to open a Microsoft Excel 97 or 2000 spreadsheet.
Sub OpenExcelDatabase(strDBPath As String)
Dim cnnDB As ADODB.Connection
Set cnnDB = New ADODB.Connection
' Specify Excel 8.0 by using the Extended Properties
' property, and then open the Excel file specified by
' strDBPath.
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open strDBPath
Debug.Print .ConnectionString
.Close
End With
Set cnnDB = Nothing
End Sub
The OpenExcelDatabase procedure can be found in the OpenDatabase module of
the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14
subfolder on the Office 2000 Developer CD-ROM.
For a list of Extended Properties property settings for all
database types supported by the Microsoft Jet 4.0 database engine, see
"Extended Properties Property Settings" in ADOProperties.doc in the
ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM.
| Database |
String |
| dBASE III |
dBASE III; |
| dBASE IV |
dBASE IV; |
| dBASE 5 |
dBASE 5.0; |
| Paradox 3.x |
Paradox 3.x; |
| Paradox 4.x |
Paradox 4.x; |
| Paradox 5.x |
Paradox 5.x; |
| Excel 3.0 |
Excel 3.0; |
| Excel 4.0 |
Excel 4.0; |
| Excel 5.0/Excel 95 |
Excel 5.0; |
| Excel 97 |
Excel 97; |
| Excel 2000 |
Excel 8.0; |
| HTML Import |
HTML Import; |
| HTML Export |
HTML Export; |
| Text |
Text; |
| ODBC |
ODBC;
DATABASE=database;
UID=user;
PWD=password;
DSN=datasourcename; |
Note that if you are migrating from DAO 3.5 or earlier with the FoxPro ISAM
to ADO with the Microsoft Jet Provider, you will need to use Microsoft Visual
FoxPro® ODBC Driver because Microsoft Jet 4.0 does not support the FoxPro ISAM.
More on Extended
Properties Property Settings
The following table lists the Extended Properties property values to
use for the data sources supported by the Jet database engine.
Table 14. Extended Properties property values
| Data Source |
Extended Properties Property
Value |
| dBASE |
dBASE III
dBASE IV
dBASE 5.0 |
| Microsoft Excel |
Excel 3.0
Excel 4.0
Excel 5.01
|
| FoxPro |
FoxPro tables are accessed by using the
Microsoft FoxPro ODBC driver. |
| Lotus |
Lotus WK1
Lotus WK3
Lotus WK4 |
| ODBC |
ODBC |
| Paradox |
Paradox 3.x
Paradox 4.x
Paradox 5.x |
| HTML |
HTML Import |
| Microsoft Exchange |
Exchange 4.03 |
| Text |
Text |
1 The Excel 5.0 source database type
string is used to specify both Microsoft Excel 5.0 and 7.0 workbooks.
2 The Excel 8.0 source database type
string is used to specify both Microsoft Excel 8.0 and 9.0 workbooks.
3 The Exchange 4.0 source database type
string is used to specify Microsoft Exchange 4.0, 5.0, and Outlook folders and
address books.
| 
