?
Solved

Listing tables in Access Database

Posted on 2003-11-26
11
Medium Priority
?
1,137 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
ID: 9825628
I can give you a DAO example if you want.
0
 
LVL 3

Expert Comment

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

Expert Comment

by:Arthur_Wood
ID: 9825679
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9825740
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
ID: 9825813
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
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 100 total points
ID: 9825943
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
ID: 9826012
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
ID: 9826489
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
ID: 9830528
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
ID: 9830893
' #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
ID: 9831748
open the databse, then on the Tools/Options menu item. check the System Objects check-box.

AW
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month9 days, 18 hours left to enroll

571 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