?
Solved

Listing tables in Access Database

Posted on 2003-11-26
11
Medium Priority
?
1,133 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

800 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