Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

How to Extract the Schema of an Access Database

From Wiki

Jump to: navigation, search

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 = ",[" & 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

758 Rating: 2.7/5 (20 votes cast)