External DatabasesINETA
Home Up VSLive 2000 10 Things Stopping People From Visiting Your Site TechEd 2002 External Databases


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
   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 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;

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 5.0
Microsoft Excel Excel 3.0
Excel 4.0
Excel 5.01
Excel 8.02
FoxPro FoxPro tables are accessed by using the Microsoft FoxPro ODBC driver.
Lotus Lotus WK1
Lotus WK3
Lotus WK4
Paradox Paradox 3.x
Paradox 4.x
Paradox 5.x
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.


Send mail to vblg@xocomp.net with questions or comments about this web site.
Copyright © 1998-2003 XOCOMP, llc
Last modified: 07/01/2002