The SQL statement determines whether a known table exists

How to use SQL statements to determine whether a known table exists

Answer: The specific solution is as follows:

Note: The following code is a module for the usual reference DAO

Function fexisttable (strTableName as String) as Integer

Dim DB as Database

Dim I as Integer

Set db = dbengine.workspaces (0). Databases (0)

Fexisttable = False

Db. Tabledefs.refresh

For i = 0 to db. Tabledefs.count-1

If strtablename = db. TableDefs (i). Name Then

' Table Exists

Fexisttable = True

Exit for

End If

Next I

Set db = Nothing

End Function

Private Sub Command 0_click ()

Fexisttable

End Sub

A ' parameter not selectable ' error occurred with this event. After careful study, we found that fexisttable is missing the parameter, that is, the table name is not reflected in the code.

Modified to:

Private Sub Command 0_click ()

Fexisttable ("Known table name to be judged")

End Sub

End Sub no longer complains. Careful analysis, in fact, with "known table name" through DAO to determine whether there is a database, if the value of fexisttable is true is there, otherwise it does not exist.

Solve the problem, suddenly remembered that Access database also has system tables, stored with object name, whether to make a query to determine it

It is verified that the requirements can be realized. If qty>0, that means that the table already exists, otherwise it means that it does not exist.

SELECT Count (*) as Qty

From Msysobjects

WHERE ((msysobjects.name) like "a known table name to be judged");