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.

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

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

  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.

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


Your email address:

Delivered by FeedBurner


Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


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

Featured Post

Passing Two Dimensional Array to Function

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS has been selected by https://blog.feedspot.com pa...


Blog Archive

Recent Posts