Obtain Access Table Names via Excel

[Macdave_19]

Hi all,

I am trying to create a database Puller! it will extract data using SQL via the medium of Excel lol without the user having any knowledge of SQL.

However i really need to obtain all the table names from a given Database.
The database' filepath is located in Cell B2 so given that can anyone help me with the code?

I'll get ya started lol:

Dim db as Database
Dim MyDb as string

MyDb = Range("B2").value
Set db = OpenDatabase(MyDb)

That's all i got lol!!!

I'm thinking it could make use of this SQL Script:

SELECT *
FROM all_tab_columns
WHERE Table_Name IS NOT NULL

but thats something i use in Oracle Developer to obtain table names

please hep & Ta for reading my thread

[Macdave_19]

I actually did it on my own!!!

get in for those who care here's how:

Dim db As Database
Dim MyDb As String
Dim InCol As String
Dim rs As Recordset
Dim Tbl As TableDef

MyDb = Range("C2").Value
Set TargetRange = Range("C3")
Set db = OpenDatabase(MyDb)

i = 0
For Each Tbl In db.TableDefs
    TargetRange.Offset(0, i).Value = Tbl.Name
    i = i + 1
Next Tbl

Set rs = Nothing
Set db = Nothing

Damn That feels so good!!!! get in!!!

[Stuie]

always feels better to work it out yourself, nice one

[Macdave_19]

Nice one Stuie!!!

[Macdave_19]

You actually end up getting these weird other tables too so i re-wrote it to exlcude them. Here it is:

Sub GetTableNames()

Dim db As Database
Dim MyDb As String
Dim Tbl As TableDef

MyDb = Range("C2").Value ' replace with the whole path if you want.
Set TargetRange = Range("C3")
Set db = OpenDatabase(MyDb)

i = 0
For Each Tbl In db.TableDefs
    Select Case Tbl.Name
        Case "MSysAccessObjects", "MSysAccessXML", "MSysACEs", "MSysObjects", "MSysQueries", "MSysRelationships"
            GoTo CONT
        Case Else
            TargetRange.Offset(0, i).Value = Tbl.Name
CONT:
            i = i + 1
    End Select
Next Tbl

Set rs = Nothing
Set db = Nothing


End Sub

HTH

[Stuie]

i will keep this post in mind as have a project coming up at work soon where i will need to extract a couple of data tables, i would normally just use a data import but i would rather code everything that way you have more control, so you given me a great idea for that