[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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:


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 Sub

Open in new window


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
0
ThomasFoege
Asked:
ThomasFoege
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Pratima PharandeCommented:
Sub ListTbls()
   
   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(adSchemaTables)

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
0
 
ThomasFoegeAuthor Commented:
Thanks! I get a "Expected End With" when i run your code?

If i insert that just below Wend, i get a "Object Required"?
0
 
UnicornBoyCommented:
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!
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Pratima PharandeCommented:
tryt his

Sub ListTbls()
   
   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 = cnn.OpenSchema(adSchemaTables)

rsSchema.MoveFirst
With rsSchema
While Not .EOF
If .Fields("TABLE_TYPE") = "TABLE" Then
MsgBox  .Fields("TABLE_NAME")
End If
.MoveNext
Wend
End With

   Set cnn = Nothing

End Sub

refer
http://www.xtremevbtalk.com/showthread.php?threadid=69391
0
 
mbizupCommented:
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

Open in new window

0
 
Pratima PharandeCommented:
0
 
mbizupCommented:
Just realized you're working from Excel, not Access.  My code will run from Access but not Excel - sorry.
0
 
Rory ArchibaldCommented:
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.
0
 
ThomasFoegeAuthor Commented:
Works perfectly! Thanks a lot!

Is there anyway to list all the columns once a table has been chosen btw?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now