Solved

Listing tables in Access Database

Posted on 2003-11-26
11
1,126 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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 25 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
String manipulation in Visual Basic 7 73
Add and format columns in vb6 7 63
Set email body to html using vbscript 6 49
Set WorkSheet  not Working 9 39
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
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…

856 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