How to extract the schema of an Access (.mdb) database?

by

It is possible to do a little with VBA. For example, here is a start on creating script for a database with local tables.

Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field
Dim ndx As DAO.Index
Dim strSQL As String
Dim strFlds As String
Dim strCn As String

Dim fs, f

    Set db = CurrentDb

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.CreateTextFile("C:\Docs\Schema.txt")
    
    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) <> "Msys" Then
            strSQL = "strSQL=""CREATE TABLE [" & tdf.Name & "] ("
            
            strFlds = ""
            
            For Each fld In tdf.Fields
                
                strFlds = strFlds & ",[" & fld.Name & "] "
                
                Select Case fld.Type
                
                    Case dbText
                        'No look-up fields
                        strFlds = strFlds & "Text (" & fld.Size & ")"
                    
                    Case dbLong
                        If (fld.Attributes And dbAutoIncrField) = 0& Then
                            strFlds = strFlds & "Long"
                        Else
                            strFlds = strFlds & "Counter"
                        End If
    
                    Case dbBoolean
                        strFlds = strFlds & "YesNo"
    
                    Case dbByte
                        strFlds = strFlds & "Byte"
                        
                    Case dbInteger
                        strFlds = strFlds & "Integer"
                    
                    Case dbCurrency
                        strFlds = strFlds & "Currency"
            
                    Case dbSingle
                        strFlds = strFlds & "Single"
            
                    Case dbDouble
                        strFlds = strFlds & "Double"
            
                    Case dbDate
                        strFlds = strFlds & "DateTime"
            
                    Case dbBinary
                        strFlds = strFlds & "Binary"
            
                    Case dbLongBinary
                        strFlds = strFlds & "OLE Object"
                        
                    Case dbMemo
                        If (fld.Attributes And dbHyperlinkField) = 0& Then
                            strFlds = strFlds & "Memo"
                        Else
                            strFlds = strFlds & "Hyperlink"
                        End If
                        
                    Case dbGUID
                        strFlds = strFlds & "GUID"
                        
                End Select
            
            Next
    
            strSQL = strSQL & Mid(strFlds, 2) & " )""" & vbCrLf & "Currentdb.Execute strSQL"
            
            f.WriteLine vbCrLf & strSQL
        
            'Indexes
            For Each ndx In tdf.Indexes
            
                If ndx.Unique Then
                    strSQL = "strSQL=""CREATE UNIQUE INDEX "
                Else
                    strSQL = "strSQL=""CREATE INDEX "
                End If
                
                strSQL = strSQL & "[" & ndx.Name & "] ON [" & tdf.Name & "] ("
                
                strFlds = ""

                For Each fld In tdf.Fields
                    strFlds = strFlds & ",[" & fld.Name & "]"
                Next
                    
                strSQL = strSQL & Mid(strFlds, 2) & ") "
                
                strCn = ""
                
                If ndx.Primary Then
                    strCn = " PRIMARY"
                End If
                
                If ndx.Required Then
                    strCn = strCn & " DISALLOW NULL"
                End If
                
                If ndx.IgnoreNulls Then
                    strCn = strCn & " IGNORE NULL"
                End If
                
                If Trim(strCn) <> vbNullString Then
                    strSQL = strSQL & " WITH" & strCn & " "
                End If
                
                f.WriteLine vbCrLf & strSQL & """" & vbCrLf & "Currentdb.Execute strSQL"
            Next
        End If
    Next
        
    f.Close

More Related Contents:

  1. How do you use version control with Access development?
  2. Microsoft Access condense multiple lines in a table
  3. Bang Notation and Dot Notation in VBA and MS-Access
  4. How do I make a stored procedure in MS Access?
  5. How can I get table names from an MS Access Database?
  6. Syntax error in insert into statement – what’s the error?
  7. Is there any way to create multiple insert statements in a ms-access query?
  8. Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb
  9. Microsoft Access DBEngine.OpenDatabase() breaks for Network-Paths – Error 3050
  10. Is it possible to create a recursive query in Access?
  11. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  12. MS Access (MDB) concurrency
  13. What is the equivalent of Select Case in Access SQL?
  14. MS Access LIMIT X, Y
  15. Prevent enabling of shift bypass – Access 2010
  16. Access 2007 – Left Join to a query returns #Error instead of Null
  17. Access query producing results like ROW_NUMBER() in T-SQL
  18. Pivot Query in MS Access
  19. Save password for ODBC connection to MS SQL server from MS Access 2007
  20. How to write Case Sensitive Query for MS Access?
  21. Setting up an MS-Access DB for multi-user access
  22. Access VBA: Find max number in column and add 1
  23. ConcatRelated function in a query
  24. Access 2007 – INSERT and instant SELECT doesn’t retrieve the inserted data
  25. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  26. Custom row source for combo box in continuous form in Access
  27. VBA – Run Time Error 3271 using DAO object
  28. Working with multiple programmers on MS Access
  29. Trouble with InputBoxes
  30. Get Name of Current VBA Function

Leave a Comment