Introduction
In the earlier Article Opening External Data Sources, we learned how to open another Microsoft Access Database and work with its Tables using the code. I have made a revision of the VBA Code taken from the earlier Post and presented below, to display the Database Names loaded in WorkSpace(0), on top of the list of Employee Names in the MsgBox. The Revised Code is given below. You may copy and replace the earlier Code and try them out.
Revised VBA Code.
Public Sub OpenSecondDatabase()
Dim wsp As Workspace, db As Database
Dim rst As Recordset, msg As String, x As Integer
Dim dbcount As Integer
Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase("c:\Program Files\Microsoft Office\Office11\samples\Northwind.mdb")
Set rst = db.OpenRecordset("Employees", dbOpenDynaset)
dbcount = wsp.Databases.Count - 1
msg = ""
For x = 0 To dbcount
msg = msg & "Database(" & x + 1 & ") " & Dir(wsp.Databases(x).Name) & vbCr
Next
msg = msg & vbCr
With rst
x = 1
Do While x < 6 And Not .EOF
msg = msg & ![LastName] & vbCr
.MoveNext
x = x + 1
Loop
.Close
End With
MsgBox msg
Set rst = Nothing
Set db = Nothing
Set wsp = Nothing
End Sub
The statement wsp.Databases(x).Name gives the full Path Name of the File and I have enveloped it in the Dir() (Directory Function) to extract the Database Name alone to make it shorter in the MsgBox display. The Dir() function checks for the presence of the Database in its specified Folder and if found returns the File Name alone.
Opening dBase Table
Opening the dBase File is comparatively a simple operation. Create an SQL string with a reference to the dBase Database Folder, the Table Name, and the dBase Version (dBase III, IV, or 5.0) of the Table and open the Recordset directly. The sample SQL String is given below:
strSql = "SELECT Employee.* FROM Employee IN 'C:\MydBase'[DBASE III;];"
If you don't have a dBase file on your Machine to try this out you can Export one of your own Microsoft Access Tables to dBase III, IV, or 5.0 Versions.
I have used the exported Employees Table from the NorthWind.mdb sample database in our example. If you would like to try the Code given below without change, you may Export the Employees Table from the Northwind.mdb sample database. If you are not sure, where you can find this file, visit the Page Saving Data on Forms Not in Table, for location references.
Exporting Employees Table as dBase Table
- Create a Folder on your Disk C:\MydBase.
- Open the Northwind.mdb database.
- Select the Employees table.
- Select Export from File Menu.
- Select dBase III or dBase IV or dBase 5 in the Save as Type Control in the Common Dialog Box.
- Browse to the Folder C:\MydBase.
- Type the File Name Employee in the File Name Control and Click Export.
Note: dBase Application File uses only 8 characters for the name and 3 characters, for File Name Extensions. When you Export the Employees Table it will shorten the name to 8 characters and saves it as file Employee.dbf. The exported Table's Field Names also will be truncated after the 10th character, if they are longer.
When the Employees Table is Exported in dBase format, several files are created in the output folder depending on the Version of dBase (III, IV, or 5.0) you have selected. The list of files will look like the samples given below:
- EMPLOYEE.INF (contains the Index File Details)
- EMPLOYEE.DBF (the data except for the Memo Field Values)
- EMPLOYEE.DBT (the Memo Field contents)
- LastName.NDX (LastName Field Index information if saved as dBase III)
- Postalco.NDX (PostalCode Field Index information if saved as dBase III)
- PRIMARYK.NDX (PrimaryKey Index information if saved as dBase III)
If you export the Table, in dBase IV or 5.0 format, then the information in the last three files will be saved into a single Multiple Index file with the file extension.MDX. The Export, Import, or Link operations are influenced by the dBase Driver known as ISAM stands for Indexed System Access Method), a common method used by dBase, FoxPro (up to Version 3.0), etc.
When you attempt to link a dBase Table to your MS-Access Database it will look for all these related files to load information correctly into Access. Assume that you have deleted the File EMPLOYEE.DBT from the folder, then the Table Import or Link operation fails with the error: cannot locate the XBase memo file.
You may Export the Employees Table into a dBase IV and 5.0 Versions as well to try opening with SQL Syntax for these Versions also. But you can use dBase III Version syntax to open other Version Tables also.
We have gone through all the fundamentals that we need to know about dBase Files, and it is time to open and work with the data. Copy and paste the code given below into a Global Module of your Database and select File- - >Save to save the Module. Click in the middle of the Code and press F5 to Run the Code.
A MsgBox will open up displaying the LastName field contents from the Employee.dbf File. If you are trying with one of your own dBase files, then change the Code to insert your Table Name and Field Name.
VBA Code for Opening dBase Table
Public Sub OpenDirectDBF() 'Open DBase File directly and read contents Dim db As Database, rst As Recordset Dim strSql As String, i As Integer Dim msg As String strSql = "SELECT Employee.* FROM Employee IN 'C:\MydBase'[DBASE III;];" 'Syntax for dBase IV & dBase V 'strSql = "SELECT Employe4.* FROM Employe4 IN 'C:\MydBase'[DBASE IV;];" 'strSql = "SELECT Employe5.* FROM Employe5 IN 'C:\MydBase'[DBASE 5.0;];" Set db = CurrentDb Set rst = db.OpenRecordset(strSql, dbOpenDynaset) i = 0 With rst msg = "" Do While Not .EOF And i < 5 msg = msg & ![LastName] & vbCr i = i + 1 .MoveNext Loop MsgBox msg .Close End With Set rst = Nothing Set db = Nothing End Sub
Tips: Even if you use the Table Name with more than 8 characters in the SQL Syntax (say Employees having 9 characters), it will ignore the s character after the 8th character and will open the file correctly. You may enable the SQL statements given in the Code for other versions of dBase by removing the single quote (') character at the beginning and try running the Code. If you have FoxPro version 2.5 or 3.0 installed on your machine, then replace [DBASE III;] with [FoxPro 2.5;] or [FoxPro 3.0;] to try with these files. Later Versions of FoxPro use DSN-based Syntax.
Displaying Excel Value directly on Access Form is next.
Earlier Post Link References:
Download Demo Database
