code to evaluate if a table is exist

[Guest]

I have written a code to create a table using SQL statement, but what is the code to evaluate if a table hasbeen existed before it execute table creation ?

 

[TC]

Why not try to create it anyway, & trap the error if it already exists?

HTH,
TC

I have written a code to create a table using SQL statement, but what is

the code to evaluate if a table hasbeen existed before it execute table
creation ?

 

[Joe Fallon]

You can use code to test if a table exists:

If TableExists("SomeTable") = True Then
'it exists
Else
' it does not exist
End IF

Put this code in a module:

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

Bonus:
Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function

===========================================================
This may be the fastest technique. It relies on the use of the system
tables:
(Credit for this idea goes to Peter Miller.)

Function TableExists(sTblName As String) As Boolean
Dim rs As Recordset
Set rs = CurrentDb.openrecordset("Select id From msysobjects Where type=1
and name='" & sTblName & "';", dbOpenSnapshot)
If Not rs.EOF Then TableExists = True
rs.Close
Set rs = Nothing
End Function

--
Joe Fallon
Access MVP

I have written a code to create a table using SQL statement, but what is

the code to evaluate if a table hasbeen existed before it execute table
creation ?

 

[Chris]

Can I modify the code just a touch?


Function TableExists(sTblName As String) As Boolean
Dim rs As DAO.Recordset
Set rs = CurrentDb.openrecordset("Select Count(name)
From msysobjects Where type in (1,4,6) and name='" &
sTblName & "';", dbOpenSnapshot)
TableExists=Not rs(0)=0
rs.Close
Set rs = Nothing

End Function

Type 1= Regular Table
Type 4 = Linked ODBC Table
Type 6 = Linked Access/Excel Table


Chris