Solved

Listing tables in Access Database

Posted on 2003-11-26
11
1,123 Views
Last Modified: 2007-12-19
Could someone please tell me how to list the table names that exist in an access database and stick them in an array? I am using DAO at present.

Thanks,
0
Comment
Question by:Ice123
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 3

Expert Comment

by:ashoooo
Comment Utility
I can give you a DAO example if you want.
0
 
LVL 3

Expert Comment

by:ashoooo
Comment Utility
Sorry, I mean I can give you an ADO example
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
select foreignname from mSysObjects where Type = 6

This query will return All of the Table names, in a recordset.

what you do with them after that is up to you.

dim Tables() as String
dim db as Database
dim rs as Recordset
dim strSQL as String
dim iCount as Integer
strSQL = "select foreignname from mSysObjects where Type = 6"

set db = currentdb
set rs = db.OpenRecordset(strSQL)

do while not rs.eof
   icount = icount + 1
   Redim Preserve Tables(icount)
   Tables(icount) = rs.fields('ForeignName")
   rs.MoveNext
Loop

AW
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
dim db as database
dim tbl as tabledef

set db=dbengine.workspaces(0).opendatabase("c:\mydatabase.mdb",false,false)

for each tbl in db.tabledefs
     debug.print tbl.name
next
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
Sub other()
Dim db As database
Dim tbl As tabledef
Dim tbls() As String, i As Integer

Set db = dbengine.workspaces(0).opendatabase("C:\FMC\Register\centros.mdb", False, False)
ReDim tbls(db.TableDefs.Count - 1)
For Each tbl In db.TableDefs
    tbls(i) = tbl.Name
    i = i + 1
Next

End Sub
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 25 total points
Comment Utility
the first post what INTERNAL to Access, from an external VB 6 parogram, it would read as:

dim Tables() as String
dim db as Database
dim rs as Recordset
dim strSQL as String
dim iCount as Integer

strSQL = "select foreignname from mSysObjects where Type = 6"

Set db = dbengine.workspaces(0).opendatabase(<path to your mdb>,False, False)
set rs = db.OpenRecordset(strSQL)

do while not rs.eof
   icount = icount + 1
   Redim Preserve Tables(icount)
   Tables(icount) = rs.fields('ForeignName")
   rs.MoveNext
Loop
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
Arthur, as you surely know, i am not against you but just a couple of question regarding your code:

One more object (recordset)
One more variable (stringsql one)
Redim statement (a little expensive with no gain)
one more function call/set statement (set rs= db.openrecordset....)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
Richie, I did not claim that it was necessarily the most efficient.  It just makes the code a 'bit' more transparent, and gives an insight to the use of the built-in mSysObjects table, which also lists all the REPORTS, FORMS, MODULES, and Queries, as well as the Tables. No biggie  LOL :-)
0
 

Author Comment

by:Ice123
Comment Utility
Arthur

Thanks you for your code, however I get the following error:

Error: '3112'
Record(s) can't be read; no read permission on 'mSysObjects'

any ideas?

Thanks,
0
 
LVL 14

Expert Comment

by:waty
Comment Utility
' #Mandix Repository#***************************************************
' * Programmer Name  : d2dSources
' * Web Site         : http://www.d2dsources.com
' * E-Mail           :
' * Date             : 02/08/2002
' **********************************************************************
' * Comments         : ADO List Access tables
' *
' * ADO List Access tables
' *
' **********************************************************************

Sub ListAccessTables(strDBPath As String)
   Dim catDB            As ADOX.Catalog
   Dim tblList          As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open the Catalog object.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strDBPath

   ' Loop through all the    tables, but not queries, and
   ' print their names and types.
   For Each tblList In catDB.Tables
      If tblList.Type <> "VIEW" Then
         Debug.Print tblList.Name & vbTab & tblList.Type
      End If
   Next

   Set catDB = Nothing
End Sub

Sub ListAccessTables2(strDBPath As String)
   Dim cnnDB            As ADODB.Connection
   Dim rstList          As ADODB.Recordset

   Set cnnDB = New ADODB.Connection

   ' Open the Connection object.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   ' Open the tables schema Recordset object.
   Set rstList = cnnDB.OpenSchema(adSchemaTables)

   ' Loop through the recordset and print the names
   ' and types in the Immediate pane.
   With rstList
      Do While Not .EOF
         If .Fields("TABLE_TYPE") <> "VIEW" Then
            Debug.Print .Fields("TABLE_NAME") & vbTab & .Fields("TABLE_TYPE")
         End If
         .MoveNext
      Loop
   End With

   cnnDB.Close

   Set cnnDB = Nothing
End Sub
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
open the databse, then on the Tools/Options menu item. check the System Objects check-box.

AW
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now