ThomasFoege
asked on
Listing Access Tables Through VBA?
Hello EE!
I'm trying to do a macro that lists all tables in a accessdb, so far I've got to this which doesnt work:
I get an error on "For Each myTable In cnn.Tables". Am i missing something here?
What I'll be trying next is once a table has been selected that all columns in that table is shown
Does anyone have any suggestions to how to fix this?
Thank you in advance!
- Thomas
I'm trying to do a macro that lists all tables in a accessdb, so far I've got to this which doesnt work:
Sub ListTbls()
Dim cnn As ADODB.Connection
'Dim myTable As ADODB.Table
Set cnn = New ADODB.Connection
With cnn
.CursorLocation = adUseServer
.ConnectionTimeout = 500
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=C:\Database.accdb"
.Open
.CommandTimeout = 500
End With
For Each myTable In cnn.Tables
If myTable.Type <> "VIEW" And _
myTable.Type <> "SYSTEM TABLE" And _
myTable.Type <> "ACCESS TABLE" Then MsgBox myTable.Name
Next myTable
Set cnn = Nothing
End SubI get an error on "For Each myTable In cnn.Tables". Am i missing something here?
What I'll be trying next is once a table has been selected that all columns in that table is shown
Does anyone have any suggestions to how to fix this?
Thank you in advance!
- Thomas
ASKER
Thanks! I get a "Expected End With" when i run your code?
If i insert that just below Wend, i get a "Object Required"?
If i insert that just below Wend, i get a "Object Required"?
as far as this page shows: http://www.w3schools.com/ado/ado_ref_connection.asp
ADODB.connection does not have a Tables property to use!
ADODB.connection does not have a Tables property to use!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Give this a try...
Sub ListTbls()
Dim db As Database
Dim myTable As TableDef
Set db = OpenDatabase("C:\Database.accdb")
For Each myTable In db.TableDefs
If Left(myTable.Name, 4) <> "mSys" Then
msgbox myTable.Name
End If
Next myTable
set db = nothing
End Sub
Just realized you're working from Excel, not Access. My code will run from Access but not Excel - sorry.
NOT FOR POINTS
Just an FYI re mbizup's code: you need to set a reference to the Microsoft Office 12.0 (or 14.0 for 2010) Access database engine objects' library as it uses DAO rather than ADO.
Just an FYI re mbizup's code: you need to set a reference to the Microsoft Office 12.0 (or 14.0 for 2010) Access database engine objects' library as it uses DAO rather than ADO.
ASKER
Works perfectly! Thanks a lot!
Is there anyway to list all the columns once a table has been chosen btw?
Is there anyway to list all the columns once a table has been chosen btw?
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
With cnn
.CursorLocation = adUseServer
.ConnectionTimeout = 500
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=C:\Database.accdb"
.Open
.CommandTimeout = 500
End With
Dim rsSchema As New ADODB.Recordset
Set rsSchema = cn.OpenSchema(adSchemaTabl
rsSchema.MoveFirst
With rsSchema
While Not .EOF
If .Fields("TABLE_TYPE") = "TABLE" Then
MsgBox .Fields("TABLE_NAME")
End If
.MoveNext
Wend
Set cnn = Nothing
End Sub
refer
http://www.xtremevbtalk.com/showthread.php?threadid=69391