The SQL statement determines whether a known table exists

Source: Internet
Author: User
Tags sub command table name access database how to use sql

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 ()


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 (( like "a known table name to be judged");

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.