Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Opening dBase Files Directly

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

  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 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:

  1. EMPLOYEE.INF (contains the Index File Details)
  2. EMPLOYEE.DBF (the data except for 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 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:

Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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