Microsoft Office 2000/Visual Basic Programmer's Guide

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.