MoebiusIT

Motivation

If you ever had to deal with legacy apps, you must have come across data stored in DBF files; and  if you are  like me, you find it frustrating  that support for  DBFs will depend on factors such as which service packs are installed wherever it is your program must run - and this is often out of your  control, since the “old” system must remain  operative, at least until you migrate the data…or maybe you have to exchange data with such an app on a regular basis, since your client only wants to move part of his business to a new platform.

After one too many such frustrations, I decided to write a small utility that would kill this problem for me once and for all - well, at least as far as clients using VB6 go. No rocket science, but it gets the job done, and I can forget about dll-activex-SP swamps and concentrate on doing my job.

Even though I wrote this for myself, the code is pretty clear (I was planning on reusing it, and I did), so you  can simply download it from here, add it to your VB6 project as an existing module and you’re ready to go.

DBF files  - a brief note on an old technology

A DBF file is essentially a self-contained database table, complete with both field definitions and data.

Each field definition is persisted in a structure (type dbfFieldDef in the code) where the main features are field name, type (I only consider Character (string), Numeric, Date and Logical (Boolean)), size, and number of decimals. Most other properties, interesting though they may be, are not used in my code and if you don’t plan on changing it you may basically ignore them.

As opposed to modern relational databases, DBFS have a “record number” property (that may change if you delete and expunge some other  record), which is simply a pointer to the physical position of the record in the table.

There is also a global header that takes up the first 32 bytes in the file (type dbfHeader).  Field cIsDbf must be 0×03 if we are talking about a DBF 3 file, the kind used by CA-Clipper.  ymlLUpdate  holds the date the file was last updated. The rest of the fields are both useful (e.g.to compute the fieldCount property when you open a new file) and self-explanatory. Note that lRecCount is actually redundant, you could compute it from iHeaderSize , iRecordSize and the physical file size…(or you could figure one of the other parameters reading lRecCount), so you can easily “fix” a DBF file if the record count is lost - I saw this happen quite a few times.

So what does this code do?

As you would expect it lets you:

Open and close the DBF
Public Function dbfOpen(ByVal fn As String, mgr As dbfmanager) As Integer
Public Function dbfClose(mgr As dbfmanager) As Integer

Move around the table (when you ‘go’ to a record, you also read it and
you can get its field using dbField())
Public Function dbGoTo(i As Integer) As Integer  ‘Go to record nr I
Public Function dbGoTop() As Integer             ‘Go to 1st record
Public Function dbfMoveFirst()                   ‘actually an alias for dbGoTop()
Public Function dbfMoveNext()                    ‘move to next record, (test EOF)
Public Function dbGoBottom() As Integer          ‘go to last record

Write data
Public Function dbAppendBlank() As Integer ‘append a blank record at the bottom,
‘it becomes the current record
Public Function dbReplace(ByVal fieldName As String, v As Variant) As Integer
’set field value for current record
‘the record will not be written until you commit()
Public Function dbCommit() As Integer      ‘write changes to disk

Read  data
Property Get dbField(ByVal fieldName As String) As Variant  ‘read a field (column) value
Property Get EOF() As Boolean
Property Get MyBookmark() As Integer                        ‘bookmark a record
Property Let MyBookmark(i As Integer)                       ‘go to bookmarked record

Utility funcions
Function ZFormat(nNro, nIntegers, nDecimals)                ‘I find this formatting function more user friendly than VBs strings
Public Function stripnulls(ByVal s As String) As String     ‘deal with c like null terminators

A note on implementation

The code includes a 2nd class module ‘dbfmanager.cls’  - The idea is that you may want to manage a collection of dbfs as a single object, so I provided this class to do things like closing a bunch of dbf’s in a single command, etc. I never got around to doing it…

Tags: DBF, legacy apps, VB6

This entry was posted on Wednesday, July 1st, 2009 at 9:36 pm and is filed under MoebiusIT. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.