Select the product you need help with

ACC2000: How to Use the OpenDatabase Method to Open Third-Party Database Files

Article ID: 210259 - View products that this article applies to.

This article was previously published under Q210259

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

SUMMARY

The OpenDatabase method in Visual Basic for Applications enables you to open an external database file and to work with Data Access Objects (DAO) in that database. The OpenDatabase method supports Microsoft Access databases and a number of third-party databases, including any database that supports Open Database Connectivity (ODBC).

The OpenDatabase method supports opening third-party databases in their native file formats in much the same way that you can link (attach) a table in Microsoft Access.

Back to the top | Give Feedback

MORE INFORMATION

The Connect Property topic in Help states that you use the following settings to connect to the supported database formats.

NOTE: The following table is an excerpt from Help. Please see the Help topic above in your version of Microsoft Access for a full list.

Collapse this tableExpand this table

Database type	Specifier	Example
dBASE III	dBASE III;	`drive:\path`
dBASE IV	dBASE IV;	`drive:\path`
dBASE 5	dBASE 5.0;	`drive:\path`
Paradox 5.x	Paradox 5.x;	`drive:\path`
Text	Text;	`drive:\path`
ODBC	ODBC; DATABASE=database; UID=user; PWD=password; DSN=datasourcename [LOGINTIMEOUT=seconds]	`drive:\path`

The Example column lists the drive and the path, but not the file name. A file name is not required for these database formats because the folder (directory) in which the files are stored is considered to be the database. If you do list a file name, you may receive unexpected results.

For example, to open a Paradox file named Employee.db located in the C:\ParadoxNwind folder, use the following syntax

Set db = OpenDatabase("C:\ParadoxNwind", True, False, "Paradox 5.x;")

instead of:

Set db = OpenDatabase("C:\ParadoxNwind\EMPLOYEE.DB", True, False, "Paradox 5.x;")

The first example (without the file name) is the correct one to use. It sets the C:\ParadoxNwind folder as a database and sets the db variable to a valid Database object. The DBF files contained in the directory are considered in the database's TableDef objects.

Be aware that the second example (with the file name) is successful if you use it. The OpenDatabase method opens a database and returns a valid value without returning any errors or warnings. You can even use the database; however, the database has no TableDefs collection.

Example

To use the OpenDatabase method to open a Paradox 5.x database, follow these steps:

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Create a C:\ParadoxNwind folder.
Start Microsoft Access and open the sample database Northwind.mdb.
Export the Employees table as a Paradox 5.x file to the C:\ParadoxNwind folder and name it Employee.db. (The Employees table contains memo fields and indexes, and creates other supporting files in the same directory.)
Export the Customers table as a Paradox 5.x file to the C:\ParadoxNwind folder and name it Customer.db.
Create a module and type the following line in the Declarations section if it isn't already there:
Option Explicit

Type the following procedure:

Sub OpenDatabaseTest ()
   On Local Error GoTo OpenDatabaseTest_Err
   Dim i As Integer
   Dim db As DAO.Database
   ' Open the database with the directory specified.
   Set db = OpenDatabase("C:\ParadoxNwind", True, False, "Paradox 5.x;")
   ' Loop and print the TableDefs collection.
   For i = 0 To db.tabledefs.count - 1
      Debug.Print db.tabledefs(i).name
   Next i
OpenDatabaseTest_End:
   Exit Sub
OpenDatabaseTest_Err:
   MsgBox Error$
   Resume OpenDatabaseTest_End
End Sub

To test the procedure, type the following line in the Immediate window, and then press ENTER:
OpenDatabaseTest
Note that "EMPLOYEE" and "CUSTOMER" appear in the Immediate window.

Back to the top | Give Feedback

REFERENCES

For more information about the Connect Property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type connect property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the OpenDatabase Method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type opendatabase method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Back to the top | Give Feedback