Listing tables in Access Database

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,
Ice123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ashooooCommented:
I can give you a DAO example if you want.
0
ashooooCommented:
Sorry, I mean I can give you an ADO example
0
Arthur_WoodCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Richie_SimonettiIT OperationsCommented:
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
Richie_SimonettiIT OperationsCommented:
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
Arthur_WoodCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richie_SimonettiIT OperationsCommented:
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
Arthur_WoodCommented:
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
Ice123Author Commented:
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
watyCommented:
' #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
Arthur_WoodCommented:
open the databse, then on the Tools/Options menu item. check the System Objects check-box.

AW
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.