Ice123
asked on
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,
Thanks,
I can give you a DAO example if you want.
Sorry, I mean I can give you an ADO example
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
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
dim db as database
dim tbl as tabledef
set db=dbengine.workspaces(0). opendataba se("c:\myd atabase.md b",false,f alse)
for each tbl in db.tabledefs
debug.print tbl.name
next
dim tbl as tabledef
set db=dbengine.workspaces(0).
for each tbl in db.tabledefs
debug.print tbl.name
next
Sub other()
Dim db As database
Dim tbl As tabledef
Dim tbls() As String, i As Integer
Set db = dbengine.workspaces(0).ope ndatabase( "C:\FMC\Re gister\cen tros.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
Dim db As database
Dim tbl As tabledef
Dim tbls() As String, i As Integer
Set db = dbengine.workspaces(0).ope
ReDim tbls(db.TableDefs.Count - 1)
For Each tbl In db.TableDefs
tbls(i) = tbl.Name
i = i + 1
Next
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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....)
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....)
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 :-)
ASKER
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,
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,
' #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.OL EDB.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(strDBPat h 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(adSchemaT ables)
' 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
' * 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
Dim catDB As ADOX.Catalog
Dim tblList As ADOX.Table
Set catDB = New ADOX.Catalog
' Open the Catalog object.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OL
' 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(strDBPat
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(adSchemaT
' 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
open the databse, then on the Tools/Options menu item. check the System Objects check-box.
AW
AW