Adaptable User Defined TYPE - Allow End User to Add and Subtract Database Fields

[Jeffrey Mahoney]

I have created a system that stores all of the database records into a text file. The workbook reads the database on startup and populates the data into the database sheet. There is a complete management system to add, update, and remove records from the Text database file.

I use a User Defined Type (UDT) for the fields in the database. This gives me the ability to have a constant record length when I write new records or retrieve the records.

I want to be able to create the ability for other administrators to change the fields in the database. I would provide a table showing the current list of fields with:
Name Type Length

I know that I would have to rewrite the database if the number of fields or the lengths of the fields changed.

-------
What I'm really asking is if there is a way to add code to a UDT that would allow it to read the table and create the fields based on that?
--------


The only other way I can think of accomplishing this is to create separate variables and pad them with spaces. I like the UDT method because I can create an Array of all the records.

Jeff

Public DBF As DBFields
Public aDBF As DBFields
Public zDBF As DBFields


Type DBFields  Record As String * 12
  ObsName As String * 50
  Login As String * 50
  DBDate As Date
  Task As String * 100
  A1 As String * 10
  A2 As String * 10
  A3 As String * 10
  A4 As String * 10
  B1 As String * 10
  B2 As String * 10
  B3 As String * 10
  B4 As String * 10
  B5 As String * 10
  B6 As String * 10
  B7 As String * 10
  C1 As String * 10
  C2 As String * 10
  C3 As String * 10
  C4 As String * 10
  C5 As String * 10
  C6 As String * 10
  C7 As String * 10
  C8 As String * 10
  C9 As String * 10
  D1 As String * 10
  D2 As String * 10
  D3 As String * 10
  D4 As String * 10
  E1 As String * 10
  E2 As String * 10
  E3 As String * 10
  E4 As String * 10
  E5 As String * 10
  E6 As String * 10
  E7 As String * 10
  E8 As String * 10
  F1 As String * 10
  F2 As String * 10
  F3 As String * 10
  F4 As String * 10
  F5 As String * 10
  F6 As String * 10
  F7 As String * 10
  H1 As String * 10
  SafeCom As String * 1000
  RiskCom As String * 1000
  DBYear As Integer
  DBMonth As Integer
  Seen As String * 4
  SeenDate As Date
  FollowUp As String * 500
End Type

FF = FreeFile    RecLen = LenB(DBF)
    
    Open PathFile For Random Lock Read Write As FF Len = RecLen  'open file and lock it
  End If
  
  Rec = 0
  Found = False
  Seek #FF, 1
  Do Until EOF(FF)                                            'Look for a deleted record
    Rec = Rec + 1
    Get #FF, Rec, aDBF
    A = RTrim(aDBF.Record)
    If A = "" Or TrimZero(aDBF.Record) = "" Then                        'Previously deleted record
      Put #FF, Rec, DBF
      Found = True
      Exit Do
    End If
  Loop
  If Found = False Then                                       'Add a new record
    If RTrim(DBF.Record) <> "" Then
      Put #FF, Rec + 1, DBF
    End If
  End If