SQL statement to determine whether a known table exists

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

A: The solution is as follows:

Note: The following code is a module that typically references 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

End If

Next I

Set db = Nothing

End Function

Private Sub command 0_Click ()

FExistTable

End Sub

This event causes the 'parameter not option' error. After careful research, we found that fExistTable lacks a parameter, that is, the known table name is not reflected in the Code.

To:

Private Sub command 0_Click ()

FExistTable ("names of known tables to be determined ")

End Sub

End Sub no longer reports an error. After careful analysis, we use Dao to determine whether the database exists. If the value of fExistTable is True, it does not exist.

After the problem was solved, I suddenly remembered that the Access database also had a system table with an object name. Do you want to make a query to determine whether it was used?

After verification, the requirements can be met. If the value of Qty is greater than 0, the table already exists. Otherwise, the table does not exist.

SELECT Count (*) AS Qty

FROM MSysObjects

WHERE (MSysObjects. Name) Like "Name of a known table to be judged "));