Advanced Search

list of tables and queries using the Database Object

/ Published in: Visual Basic
Save to your folder(s)
' create the list of tables and queries using the Database Object and its QueryDef and TableDef collections
'http://www.techrepublic.com/article/how-to-create-a-list-of-tables-or-queries-from-access/5047664

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. ' create the list of tables and queries using the Database Object and its QueryDef and TableDef collections
  2. 'http://www.techrepublic.com/article/how-to-create-a-list-of-tables-or-queries-from-access/5047664
  3. Public Sub GetMyObjectsList()
  4.     Open "c:\Tables and Queries.txt" For Output As #1
  5.     Dim db As Database
  6.     Dim Qry As QueryDef
  7.     Dim QryNames As String
  8.     Dim QryCount As Integer
  9.     Dim Tbl As TableDef
  10.     Dim TblNames As String
  11.     Dim TblCount As Integer
  12.     Set db = CurrentDb
  13.     QryCount = 0
  14.     TblCount = 0
  15.     For Each Qry In db.QueryDefs
  16.         QryNames = QryNames & Qry.Name & ", "
  17.         QryCount = QryCount + 1
  18.         Print #1, charpos; Qry.Name
  19.     Next
  20.     For Each Tbl In db.TableDefs
  21.      If Tbl.Attributes = 0 Then 'Ignores System Tables
  22.         TblNames = TblNames & Tbl.Name & ", "
  23.         TblCount = TblCount + 1
  24.         Print #1, charpos; Tbl.Name
  25.      End If
  26.     Next
  27.     QryNames = QryNames & "TOTAL Query Count: " & QryCount
  28.     TblNames = TblNames & "TOTAL Table Count: " & TblCount
  29.    ' MsgBox QryNames
  30.    ' MsgBox TblNames
  31.     db.Close
  32.     Set db = Nothing
  33.     Close #1
  34. End Sub

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.