# Solved: VBS - display all table names in a .mdb db file



## AndyS01 (Aug 15, 2008)

I have tried several SELECT statements to select the names of all the tables in a .*mdb* file, but I cannot get anything to work. I'm running XP, in a DOS box.

Here *is* an excerpt of my VBS code:

sub *getTableNames*(*strDBName*)
Dim sql, *cn*, *rs*, names, *str*, col
Set *cn* = *CreateObject**("*ADODB.Connection*")*
Set *rs* = *CreateObject**("*ADODB.*Recordset**")* 
*str* = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source*="* _
& *strDBName* _
& *";*User Id=*admin*;Password*=;"*
*cn*.*ConnectionString* = *str*
*cn*.Open
sql = " SELECT * FROM Sys.Objects WHERE Type = 'U'"
*rs*.Open sql, *cn*
names = *""*
For col = 0 to *rs*.fields.count - 1
names = names & " " & *rs*(col).value
Next 
*WScript*.Echo "Names *==>"* & names & *"<=="*
*WScript*.Quit
End Sub

Any help would be appreciated.

AndyS01


----------



## AndyS01 (Aug 15, 2008)

After much trial and error and lots of googling, I have some code that workd for me. Here it is (if anyone sees any gotcha's here, please let me know):

=============================================================
'###########################################
'# getTableNames() - Get the names of all of the tables
'# defined in a .mdb file
'#
'# Returns an array of names
'###########################################
Function getTableNames(strDBName)
Dim oJet, oDB, names, ndx

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.openDatabase(strDBName)

' Build a "+" delimited string of names (
For ndx = 0 To oDB.TableDefs.Count - 1
' Ignore tables that start with "MSys"
If (LCase(Left(oDB.TableDefs(ndx).Name, 4))<>"msys") then
If (Len(names) > 0) Then
names = names & "+"
End If
names = names & oDB.TableDefs(ndx).Name
End If
Next

' Make the string an array for the caller
getTableNames = split(names, "+")

End Function
==========================================================
Andy


----------

