Database Search

match allmatch any Look in all areasLook in current areas Check all datesPrevious dayPrevious 3 daysPrevious weekPrevious fortnightPrevious monthPrevious 2 monthsPrevious 3 monthsPrevious half-yearPrevious year

TechHome

• Access 2000 Advanced DML Transactions
• Access Recordset to CSV File UDF
• Access RunCommand Constants
• ACCESS Table or Query Exists
• Access to Excel via SQL
• Access Visible Problem
• Adding Fields to an Existing Access Table
• Advanced DML Transactions
• Advanced SQL in the Jet 4.0 Provider Example
• Another OLE with Access Example
• Automating Access Tutorial
• Check if Access Table Exists
• Commit Rollback Begin Trans Updatebatch
• Control Access Forms
• Count Access Records
• Csv to Access
• Dealing with ACCESS 97 Field Types
• execute a macro in Access
• Execute a Public function in Access
• Export Access Report in Choice of Formats
• Export MDB to Flat File
• Find Read and Insert Data in Access
• Gather ACCESS Table and Column Information Into Excel
• Get Access Version That Created MDB File
• Get Column Data From an Access Database
• Get Record Count
• How to Use the OpenTable method
• Launch Access Macro from Commandline
• Loop thru a recordset in Access
• Office 2003 Macro Security Issue
• OLE and Access
• OLE Import a table into Access
• OLE Import Table into Access
• Open Access DB with DAO
• Pack an Access Database
• Query and Export Data from Microsoft Access
• Read Write Delete Tables from Access DB
• Repair MDB
• Retrieve Data in Access
• SQL UPDATE Fails with Access
• UDF to Return Record Count
• Updating Table From Filtered Recordset
• Ways to Get Data from and Access file without Access installed

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Code to Gather ACCESS Table and Column Information Into Excel

Keywords: 	 Gathers ACCESS Table and Column Information Into Excel

Here is an example technique, only using ADO and OLE Providers (source and sample Excel Output in zip file), which is basically a "structure dump" for MS Access. In this example:

1. Table types are not restricted
2. It includes Views,Procedures,Keys,Indexes
3. Provisions are made for Users and Groups but I don't particularly need that info, and I think you can run into security and user-level problems.

This is more a Management Report, but it gives a good comparison between DAO and ADO when writing scripts.

Finally, as suggested in the notes in the script - this can be easily modified for Oracle, SQL Server, or other OLE Providers, since the ADO Objects are the same.

---

; ///////////////////////////////////////////////////////////
; Gathers ACCESS Table and Column Information Into Excel   //
;                                                          //
; Uses ADOX Collections, Opens New Workbook then fills it  //
; as you watch.                                            //
;                                                          //
; NOTE: though this script uses ACCESS, the code is generic//
;       to any OLE Provider, so if you wanted to output    //
;       the results of an ORACLE or SQL Server database,   //
;       just change the beginning of the script and make   //
;       the connection string refer to a pre-determined    //
;       UDL file                                           //
;       cConn = "File Name=C:\WBDEMO\ORA.UDL"              //
;                                                          //
;                                                          //
; Stan Littlefield 10/04/2001                              //
; ///////////////////////////////////////////////////////////

#DefineFunction clean(string)
newstring = ""
i=1
While i ""  ; use only if you include the Groups and User Objects
   cConn  = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%mdb%; jet oledb:system database=%sys%"
Else
   cConn  = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%mdb%"
Endif

cXLS   = strreplace(strupper(mdb),".MDB",".XLS")
CAT    = ObjectOpen("ADOX.Catalog")

IF FileExist(cXLS) Then FileDelete(cXLS)
BoxOpen("Transferring Table Information from %mdb% To %cXLS%","Opening Data Source")

CAT.ActiveConnection = cConn


;create a new Excel Workbook and let the user watch
DB               = ObjectOpen("Excel.Application")
DB.Visible       = @TRUE
DB.UserControl   = @TRUE
DB.DisplayAlerts = @FALSE
BoxShut()
ver              = DB.Version
oAPP             = DB.Workbooks
oXLS             = oAPP.add
oSheets          = DB.WorkSheets
oWS              = oXLS.Worksheets("Sheet3")
oSheets.Add(:: After=oWS)
oWS              = oXLS.Worksheets("Sheet4")
oWS.Activate
oCell            = oWS.Cells(1,1)
oCell.Value      = "TABLE"
oCell            = oWS.Cells(1,2)
oCell.Value      = "INDEX/KEY NAME"
oCell            = oWS.Cells(1,3)
oCell.Value      = "COLUMNS"

If sys<>""
   oWS           = oXLS.Worksheets("Sheet4")
   oSheets.Add(:: After=oWS)
   oWS           = oXLS.Worksheets("Sheet5")
   oWS.Activate
   oCell         = oWS.Cells(1,1)
   oCell.Value   = "USER(s)"
   oWS           = oXLS.Worksheets("Sheet5")
   oSheets.Add(:: After=oWS)
   oWS           = oXLS.Worksheets("Sheet6")
   oWS.Activate
   oCell         = oWS.Cells(1,1)
   oCell.Value   = "GROUP(s)"
Endif

oWS              = oXLS.Worksheets("Sheet1")
oWS.Activate
oCell            = oWS.Cells(1,1)
oCell.Value      = "TABLE NAME"
oCell            = oWS.Cells(1,2)
oCell.Value      = "TABLE TYPE"
oCell            = oWS.Cells(1,3)
oCell.Value      = "CREATED"
oCell            = oWS.Cells(1,4)
oCell.Value      = "LAST MODIFIED"
oWS              = oXLS.Worksheets("Sheet2")
oWS.Activate
oCell            = oWS.Cells(1,1)
oCell.Value      = "TABLE"
oCell            = oWS.Cells(1,2)
oCell.Value      = "COLUMN NAME"
oCell            = oWS.Cells(1,3)
oCell.Value      = "COLUMN TYPE"
oWS              = oXLS.Worksheets("Sheet1")
oWS.Activate
tbls             = CAT.Tables
n                = tbls.Count
x                = 2
y                = 2
z                = 2
For i = 0 to n-1
   t=CAT.Tables(i)
   oCell         = oWS.Cells(x,1)
   oCell.Value   = t.Name
   oCell         = oWS.Cells(x,2)
   oCell.Value   = t.Type
   oCell         = oWS.Cells(x,3)
   oCell.Value   = t.DateCreated
   oCell         = oWS.Cells(x,4)
   oCell.Value   = t.DateModified
   c             = t.Columns
   n1            = c.Count
   oWS           = oXLS.Worksheets("Sheet2")
   oWS.Activate
   For j= 0 to n1-1
      col        = t.Columns(j)
      oCell      = oWS.Cells(y,1)
      oCell.Value = t.Name
      oCell      = oWS.Cells(y,2)
      oCell.Value = col.Name
      oCell      = oWS.Cells(y,3)
      oCell.Value = enum(col.Type)
      y=y+1
   Next

   c             = t.Keys
   n1            = c.Count
   oWS           = oXLS.Worksheets("Sheet4")
   oWS.Activate
   name          = ""
   If n1>0
      For j= 0 to n1-1
         k=t.Keys(j)
         If k.Name<>name
            name       = k.Name
            oCell      = oWS.Cells(z,1)
            oCell.Value= t.Name
            oCell      = oWS.Cells(z,2)
            oCell.Value= k.Name
            oCell      = oWS.Cells(z,3)
            col        = k.Columns
            nCol       = col.Count
            cCol       = ""
            For l=0 To ncol-1
               w       = k.Columns(l)
               cCol    = StrCat(cCol,w.Name," ")
            Next
            oCell.Value= cCol
            z=z+1
         Endif
      Next
   Endif
   c             = t.Indexes
   n1            = c.Count
   name          = ""
   If n1>0
      For j= 0 to n1-1
         k=t.Indexes(j)
         If k.Name <> name  &&  k.Name <> "PrimaryKey"
            name       = k.Name
            oCell      = oWS.Cells(z,1)
            oCell.Value= t.Name
            oCell      = oWS.Cells(z,2)
            oCell.Value= k.Name
            oCell      = oWS.Cells(z,3)
            col        = k.Columns
            nCol       = col.Count
            cCol       = ""
            For l=0 To ncol-1
               w       = k.Columns(l)
               cCol    = StrCat(cCol,w.Name," ")
            Next
            oCell.Value= cCol
            z=z+1
         Endif
      Next
   Endif
   x             = x+1
   oWS           = oXLS.Worksheets("Sheet1")
   oWS.Activate
Next
oWS              = oXLS.Worksheets("Sheet2")
oWS.Activate
Gosub Autofit
oWS.Name         = "Columns"
oWS              = oXLS.Worksheets("Sheet1")
oWS.Activate
Gosub Autofit
oWS.Name         = "Tables"

oWS              = oXLS.Worksheets("Sheet3")
oWS.Activate
oCell            = oWS.Cells(1,1)
oCell.Value      = "VIEW NAME"
oCell            = oWS.Cells(1,2)
oCell.Value      = "SQL COMMAND"

tbls             = CAT.Views
n                = tbls.Count
x                = 2

If n>0
   For i = 0 to n-1
      t=CAT.Views(i)
      oCell      = oWS.Cells(x,1)
      oCell.Value= t.Name
      oCell      = oWS.Cells(x,2)
      cmd=t.Command
      oCell.Value= clean(cmd.CommandText)
      x          = x+1
   Next
Endif

tbls             = CAT.Procedures
n                = tbls.Count

If n>0
   For i = 0 to n-1
      t=CAT.Procedures(i)
      oCell      = oWS.Cells(x,1)
      oCell.Value= t.Name
      oCell      = oWS.Cells(x,2)
      cmd=t.Command
      oCell.Value= clean(cmd.CommandText)
      x          = x+1
   Next
Endif

Gosub Autofit
oWS.Name         = "Views"

oWS              = oXLS.Worksheets("Sheet4")
oWS.Activate
Gosub Autofit
oWS.Name         = "Indexes"

If sys<>""
   oWS           = oXLS.Worksheets("Sheet6")
   oWS.Activate
   tbls          = CAT.Groups
   n             = tbls.Count
   x             = 2

   If n>0
      For i = 0 to n-1
         t=CAT.Users(i)
         oCell      = oWS.Cells(x,1)
         oCell.Value= t.Name
         x          = x+1
      Next
      Gosub Autofit
   Endif
   oWS.Name      = "Groups"
   oWS           = oXLS.Worksheets("Sheet5")
   oWS.Activate
   tbls          = CAT.Users
   n             = tbls.Count
   x             = 2

   If n>0
      For i = 0 to n-1
         t=CAT.Users(i)
         oCell      = oWS.Cells(x,1)
         oCell.Value= t.Name
         x          = x+1
      Next
      Gosub Autofit
   Endif
   oWS.Name      = "Users"
Endif

oWS              = oXLS.Worksheets("Tables")
oWS.Activate

ObjectClose(CAT)
oXLS.SaveAs(cXLS)
ObjectClose(oWS)
ObjectClose(oXLS)
;oAPP.Close()
ObjectClose(oAPP)
ObjectClose(DB)

Exit

:autofit
oCell          = oWS.Cells(1,1)
oRegion        = oCell.CurrentRegion
oCols          = oRegion.Columns
oCols.AutoFit
oCell          = oWS.UsedRange
oCell.AutoFormat(:: Format=3)
Return

---

Article ID:   W14922

File Created: 2001:11:08:12:41:00

Last Updated: 2001:11:08:12:41:00