Opening dBase Files Directly
In the earlier Article Opening External Data Sources we have learned how to open another Microsoft Access Database and work with its Tables using Code. I have made a revision of the Code given there to display the Database Names loaded in WorkSpace(0) also 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.
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 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 NorthWind.mdb sample database for our example. If you would like to try the Code given below without change you may Export the Employees Table from 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.
- 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 Names uses only 8 characters and 3 characters for File Name Extensions. When you Export the Employees Table it will shorten the name to 8 characters and saves as Employee.dbf. The exported Table's Field Names also will be truncated after the 10th character, if they are longer.
When the Employees Table 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 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 are exporting the Table in dBase IV or 5.0 Version 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 (upto 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 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 file then change the Code to insert your Table Name and Field Name.
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 extra character s after the 8th character and will open the file correctly. You may enable the SQL statements given in the Code for other version 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 uses DSN based Syntax.