Some Access Tools

Act Two

Giving Access some of the functionality that we love in SQL Servers

Having written some code to render a whole Access TableDef and its Indexes as a series of SQL statements, it seemed there was more that could be done to it than just using it to destroy and recreate those tables.

Looking at the code, I realised it would be sheer simplicity to adjust it so that it could alternately write this SQL to a text file. A precise description of the database could then be built up, by writing all the SQL statements - for all the TableDefs and their Indexes - to the same text file. Below is the new Reincarnate function with the added text file functionality. 

  Sub Reincarnate (ByVal sTableName As String, ByVal sFileName As String)
    Dim sSQL As String
    Dim ThisDB As Database
    Dim ThisTable As TableDef
    Dim iNum As Integer
    Dim sIndex() As String
    Dim iCount As Integer
    Dim iFileNum As Integer

    ' Wrap the table name in square braces so spaces don't matter
    sSQL = "CREATE TABLE [" & sTableName & "] ("

    Set ThisDB = CurrentDB()
    Set ThisTable = ThisDB.TableDefs(sTableName)

    ' Get the SQL for each field, adding to the string
    For iNum = 0 To ThisTable.Fields.Count - 1
      sSQL = sSQL & GetFieldSQL(ThisTable.Fields(iNum)) & ", "
    Next iNum

    ' Clean up the end of the string
    sSQL = Left$(sSQL, Len(sSQL) - 2)
    sSQL = sSQL & ")"

    ' build an array of SQL statements for Indexes
    iCount = 0
    For iNum = 0 To ThisTable.Indexes.Count - 1
      iCount = iCount + 1
      ReDim Preserve sIndex(iCount + 1)
      sIndex(iCount - 1) = GetIndexSQL(ThisTable.Indexes(iNum), ThisTable.Name)
    Next iNum

    If sFileName = "" Then                        ' We actually want to reincarnate the tabledef

      ' Delete Indexes off the table
      For iNum = ThisTable.Indexes.Count - 1 To 0 Step -1
        ThisTable.Indexes.Delete ThisTable.Indexes(iNum).Name
      Next iNum
    
      ThisDB.TableDefs.Delete ThisTable.Name
    
      ' Recreate Table
      ThisDB.Execute sSQL
        
      ' Recreate indexes
      For iNum = 0 To iCount - 1
        ThisDB.Execute sIndex(iNum)
      Next iNum
    Else                                          ' We just want to write the SQL to a file
      iFileNum = FreeFile
      Open sFileName For Append Access Write As iFileNum

      ' We indicate comments in the file with a semi-colon
      Print #iFileNum, "; SQL for " & sTableName
      Print #iFileNum, sSQL
      Print #iFileNum, "; " & sTableName & " indexes"

      ' Loop through the array of Index SQL statements.
      For iNum = 0 To iCount - 1
        Print #iFileNum, sIndex(iNum)
      Next iNum

      Close #iFileNum
    End If
  End Sub
As you can see, this functionality is easy to control. If you pass a file name as the second parameter, the SQL is written to that file with a couple of comment lines, so anyone browsing the file is given some help in figuring out what is going on. By building this code to work with SQL in the first place, we have made it flexible enough to add this functionality. This is possibly the sort that all SQL manuals talk about in the first couple of chapters (sometimes known as the 'sales pitch section'). But I can't be sure - maybe they're talking about something else.

The question you're probably asking is 'Why is this useful?'. Well doubting one, as far as I can see this is useful in three ways:

  1. Anyone who administers, maintains or regularly works with a database, should know SQL better than they care to admit at dinner parties. Therefore, they can use this text file as a reference to the structure of the database. It is also useful as a record of its structure at various stages of development.
  2. If you want to upsize the database to a SQL server this gives you a good starting point (and more control than using the Upsizing wizard, which is also cool). Most SQL servers should be able to execute the file as a procedure to create a database with little modification to the file.
  3. The third I will talk about in Act III.
Having stopped to think about this for a moment I realised there was something missing - QueryDefs. I took another Jolt from the fridge and set to.

Duplicating this functionality for QueryDefs proved much easier than for TableDefs. After all, QueryDefs have a SQL property. 

  Sub DumpQueryDefs (ByVal sFileName As String)
    Dim iNum As Integer
    Dim dbThis As Database
    Dim iFileNum As Integer

    Set dbThis = CurrentDB()
    iFileNum = FreeFile
    Open sFileName For Append Access Write As iFileNum

    ' Loop through QueryDefs collection writing each SQL to the file
    For iNum = 0 To dbThis.QueryDefs.Count - 1
      Print #iFileNum, "; SQL for Querydef " & dbThis.QueryDefs(iNum).Name
      Print #iFileNum, "CREATE PROC [" & dbThis.QueryDefs(iNum).Name & "] " & dbThis.QueryDefs(iNum).SQL
    Next iNum
    Close #iFileNum
  End Sub
As you can see, I chose to prepend the 'CREATE PROC procname. . .' SQL to the string written to the file. This is for two reasons. First, because this is directly executable on many SQL servers to create stored procedures, which is good for our upsizing plan. Second, because I can.

So now we have a text file that represents all the TableDefs, Indexes, and QueryDefs, in the database. Simply by calling a single function. 

  Sub GenerateSQL (ByVal sFileName As String)
    Dim iNum As Integer
    Dim dbThis As Database

    Set dbThis = CurrentDB()
    
    ' Loop through tabledefs collection passing each tabledef to
    ' the function that builds and writes thr SQL.
    For iNum = 0 To dbThis.TableDefs.Count - 1
        
      ' Only do non system tables
      If Left$(dbThis.TableDefs(iNum).Name, 4) <> "MSys" Then
        Reincarnate dbThis.TableDefs(iNum).Name, sFileName
      End If
    
    Next iNum
    DumpQueryDefs sFileName
  End Sub
Or, we can rebuild the whole database as a brand spanking new empty one. 
  Sub RebuildDB ()
    Dim iNum As Integer
    Dim dbThis As Database

    Set dbThis = CurrentDB()
    
    ' Loop through tabledefs collection passing each tabledef to
    ' the function that builds and writes thr SQL.
    For iNum = 0 To dbThis.TableDefs.Count - 1
        
      ' Only do non system tables
      If Left$(dbThis.TableDefs(iNum).Name, 4) <> "MSys" Then
        Reincarnate dbThis.TableDefs(iNum).Name, ""
      End If
    
    Next iNum
  End Sub


[HOME] [TABLE OF CONTENTS] [SEARCH]