Some Access Tools

Act One

And behold, the phoenix rose from the ashes of its own demise. The autumn colours of its plumage still flickering with traces of the renewing flame.

Because the table has been newly created its counter field is effectively reset. This also performs the task of deleting all the contents of the table. The time taken to run this code is minimal. Compared to running a `DELETE FROM. . .' on a table of 200,000 records and 20 or so fields it is blindingly quick.

Rendering the table creation functions as SQL strings was also flexible, easy to do, and simplified the code greatly as opposed to using DAO. It also allowed me to expand on this code for some other functions, but more about that later.

As a working example let's take a look at the code that generates the SQL for each field. 

  Function GetFieldSQL (fldX As Field) As String
    Dim sSQL As String

    ' Wrap the fieldname in square braces so that spaces
    ' or special characters are irrelevant.
    sSQL = "[" & fldX.Name & "] "

    ' Examine the field's type to get the appropriate SQL keyword.
    Select Case fldX.Type
      Case DB_Date
        sSQL = sSQL & "DATETIME"
      Case DB_Text
        sSQL = sSQL & "TEXT (" & CStr(fldX.Size) & ")"
      Case DB_Memo
        sSQL = sSQL & "LONGTEXT"
      Case DB_Boolean
        sSQL = sSQL & "BIT"
      Case DB_Integer
        sSQL = sSQL & "SHORT"
      Case DB_Long

      ' A special case, we need more info.
      If fldX.Attributes And DB_AUTOINCRFIELD Then
        sSQL = sSQL & "COUNTER"
      Else
        sSQL = sSQL & "LONG"
      End If
      Case DB_Currency
        sSQL = sSQL & "CURRENCY"
      Case DB_Single
        sSQL = sSQL & "SINGLE"
      Case DB_Double
        sSQL = sSQL & "DOUBLE"
      Case DB_Byte
        sSQL = sSQL & "BIT"
      Case DB_LongBinary
        sSQL = sSQL & "LONGBINARY"
    End Select
    GetFieldSQL = sSQL
  End Function
Simple isn't it? We gather a couple of important pieces of information and add it to a string. We do this iteratively to each field, then prefix it with some more SQL to finish the job. Of course, I wouldn't want to write it again. The SQL for each index is built up in a similar fashion. Now I always keep this function (that I call Reincarnate) handy in a text file, so that I can easily import it into any given Access database as a module, and reincarnate any counter laden tables (or any other tables) that I decide deserve it.


[HOME] [TABLE OF CONTENTS] [SEARCH]