Introduction
In the earlier article “Opening External Data Sources,” we learned how to open another Microsoft Access database and work with its tables using VBA code. I have revised that code from the previous post to include an additional feature — it now displays the database names loaded in Workspace(0) at the top of the list of employee names in the message box.
The revised code is provided below. You can copy it, replace the earlier version, and try it 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 Returns the full path of the database file. To make the display in the message box more concise, I have wrapped it in a Dir() function, which extracts only the file name from the full path. The Dir() function also checks for the existence of the specified file in the given folder, and if it is found, returns just the file name.
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. To try the Code given below without change, you may export the Employees Table from the Northwind.mdb sample database. If you don't know where to find this file, visit the link Saving Data on Forms Not in Table for location references.
Exporting Employees Table as a dBase Table
Create a Folder on your Disk C:\MydBase.
Open the Northwind.mdb database.
Select the Employees table.
Select Export from the 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 save it as a dBase table Employee.dbf. The exported Table's Field Names will also 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 a table in dBase IV or 5.0 format, the information contained in the last three files will be consolidated into a single multiple-index file with the MDX extension. The export, import, and link operations are managed by the dBase driver, known as ISAM (Indexed Sequential Access Method), a standard mechanism used by database systems such as dBase and FoxPro (up to version 3.0).
When you attempt to link a dBase table to your MS-Access database, Access will look for all the associated files to load the information correctly. For example, if the file EMPLOYEE.DBT has been deleted from the folder; the table import or link operation will fail with the error: “Cannot locate the XBase memo file.”
You can export the Employees table to dBase IV or 5.0 formats to experiment with SQL syntax for these versions. However, dBase III syntax can also be used to open tables from the later versions.
Having covered all the necessary fundamentals of dBase files, it’s now time to open and work with the data. Copy and paste the code below into a Global Module in your database and save it via File → Save. Place the cursor inside the code and press F5 to run it.
A message box will appear displaying the contents of the LastName field from the Employee.dbf file. If you are using your own dBase file, modify the code to reference your table and field names accordingly.
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 the table name length is more than 8 characters (for example, Employees with 9 characters), the SQL syntax will ignore any characters beyond the eighth, and the file will still open correctly.
You can enable the SQL statements in the code for other dBase versions by removing the single quote (') at the beginning of the line and then running the code.
If you have FoxPro version 2.5 or 3.0 installed on your machine, replace it [DBASE III;] with [FoxPro 2.5;] or [FoxPro 3.0;] to work with those files. Note that later versions of FoxPro require a DSN-based syntax.
Displaying Excel Value directly on the Access Form is next.
Earlier Post Link References:
Download Demo Database
