Retrieve Table Names From Access .mdb File

[BigDaddyO]

Hello,

I am writing a little script where I need to do a LOT of searches in an Access .mdb file on all tables stored in that file. I have everything working great except I have had to hard code the Table names into the searches.

My issue is that the table names can very and the number of tables can very.

I need a way to retrieve all the table names that are in the file so I can use that for searching later on.

I have been searching google for 2 days and I haven't found anything, probably because I don't know the command to search for.

Any help is Greatly appreciated,

Mike

[BigDaddyO]

YEA!!!!

Func _getTables($DBsource)
    $adoxConn = ObjCreate("ADOX.Catalog") 
    $adodbConn = ObjCreate("ADODB.Connection") 
    $adodbConn.open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $DBsource)
    $adoxConn.activeConnection = $adodbConn 
    for $table in $adoxConn.tables 
        if $table.type="TABLE" then 
            MsgBox(0, "Table", $table.name)
        endif 
    next 
    $adodbConn.close
    $adodbConn = ""
    $adoxConn = ""
EndFunc

God that was hard to find...

Mike

[billmez]

YEA!!!!

Func _getTables($DBsource)
    $adoxConn = ObjCreate("ADOX.Catalog") 
    $adodbConn = ObjCreate("ADODB.Connection") 
    $adodbConn.open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $DBsource)
    $adoxConn.activeConnection = $adodbConn 
    for $table in $adoxConn.tables 
        if $table.type="TABLE" then 
            MsgBox(0, "Table", $table.name)
        endif 
    next 
    $adodbConn.close
    $adodbConn = ""
    $adoxConn = ""
EndFunc

God that was hard to find...

Mike

Thanks for sharing.

FYI SQL server has a sp_tables stored procedure that can be executed via ado to find this.

[SmOke_N]

I thought I remembered seeing also that there was an .mdb to .sql converter somewhere... (that may have been somewhere else though).