how to check for existence of a table in a database? 2

[barny2006]

hi,
is there anyway to check to see if a certain table exists in a database?

 

[Remou]

Look for it in the system table MSysObjects. DlookUp should suit.

 

[PHV]

In a standard code module create the following function:

Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = (CurrentDb.TableDefs(strTableName).Name = strTableName)
End Function

And now anywhere in VBA:
If TableExists("myTable") Then
MsgBox "Table exists"
Else
MsgBox "Table must be created"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barny2006]

dlookup doesn't check for a table to exist. it checks for data to exist in a column.

 

[Remou]

That's right and the table I suggested you look in is MSysObjects.

To quote my post:
Look for it in the system table MSysObjects

 

[barny2006]

this is all done in vb script. and i'm not sure i have the syntax for vbscript for dlookup.

 

[PHV]

How do play with the DB in your script ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

barny2006,
No offense but you didn't mention "vbscript" until your third post. I would think this should have been included in your very first post along with some other specifications.

Remou and PHV spend a lot of time answering questions in Tek-Tips and they shouldn't have to waste their time prying for information...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[barny2006]

dhookm,
my bad. but,
phv posted in post #2, a vb script. i'm wondering how he figured it out? i'm sure you know, that one does not need to "check" for existence of a table in a database using access application gui interface. you just click the tables tab, and bingo. but i think phv did understood the point. and posted that way.
in anyway, i haven't been able to resolve this issue. nothing that has been suggested, seems to work.

 

[Remou]

I think you will find that PHV posted VBA, as would be expected in an Access forum.

You can use SQL
[tt]strSQL="Select Name From Msysobjects Where Name='" & tblName & "'"[/tt]

Or if you answer PHV's question "How do play with the DB in your script ?", it may be possible to suggest other methods.

 

[barny2006]

remou,
thanks. i think i will give your suggestion a try. it looks like it would work.
question:
after executing this command, how would i check the result of this "select" to see if the table exists? move next? field items? recordset?
i'm not sure if understand the last part. "how do play".
the vbscript check to see if the table exists. if it exists, it prompts the user to choose another name, if it doesn't exist, it creates the table using the input file which is an excel file.

 

[barny2006]

i have this code:

str_SQL="Select Name From Msysobjects Where Name='" & table_name & "'"
objRecordSet.Open str_SQL, objConnection, adOpenStatic, adLockOptimistic	   
objRecordSet.MoveFirst
tbl = objRecordset.Fields.Item("name")
msgbox tbl

i'm getting nothing.

 

[Remou]

If there are no records in the recordset, the table does not exist.
"How do you play" = "How do you work with the database at the moment?" Some people enjoy work.

 

[barny2006]

the table does exist. i can see it when i open access database. but i get nothing when i execute that command.
the database is an access database. i import excel files into it as i described above, if the table doesn't exist.

 

[Remou]

Sorry, our posts crossed.

Try this:

Dim catTables 
Dim tblTable

Set catTables = CreateObject("ADOX.Catalog")
Set tblTable = CreateObject("ADOX.table")

Set cn = CreateObject("ADODB.Connection")
dbfile = "C:\Tek-Tips.mdb"

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & dbfile & ";"

Set catTables.ActiveConnection = cn

For Each tblTable In catTables.Tables
    If tblTable.Name = table_name Then
         TableExist=True
    End If
Next 
MsgBox TableExist

Set catTables = Nothing
Set tblTable = Nothing

 

[barny2006]

remou,
it worked like magic. you did it.
i tried this with a table that i knew existed. it returned true.
then i tried it with a table that i knew it didn't exist. it returned false.
thanks so much.
i have to tell you. i had searched all over the net for this. everybody has posted some "code". i found out that none worked.
this one worked like a charm. you may want to just put it as a faq or a sticky in tek-tips for people to benefit.
thanks again.