Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

Revised Code for second Access Database Opening

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
msg = msg & vbCr
With rst
x = 1
Do While x < 6 And Not .EOF
    msg = msg & ![LastName] & vbCr
   x = x + 1
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 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.

Exporting Employees Table as dBase Table

  1. Create a Folder on your Disk C:\MydBase.
  2. Open the Northwind.mdb database.
  3. Select the Employees Table.
  4. Select Export from File Menu.
  5. Select dBase III or dBase IV or dBase 5 in the Save as Type Control in the Common Dialog Box.
  6. Browse to the Folder C:\MydBase.
  7. Type the File Name Employee in the File Name Control and Click Export.

Note: dBase Application File uses only 8 characters for Name 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:

  1. EMPLOYEE.INF (contains the Index File Details)
  2. EMPLOYEE.DBF (the data except the Memo Field Values)
  3. EMPLOYEE.DBT (the Memo Field contents)
  4. LastName.NDX (LastName Field Index information if saved as dBase III)
  5. Postalco.NDX (PostalCode Field Index information if saved as dBase III)
  6. 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.

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
MsgBox msg
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.

Displaying Excel Value directly on Access Form is next.


No comments:

Post a Comment

Comments subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports Downloads msaccess tips Accesstips Objects Collection Object Property Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work VBA msaccess How Tos Combo Boxes Dictionary Object Graph Charts List Boxes Query msaccessQuery Calculation Command Buttons Form Report Command Button Data Emails and Alerts RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Key msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Macros Menus Recordset SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Database Records Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload