Access 無 INFORMATION_SCHEMA.TABLES,需查詢系統資料表 MSysObjects 判斷表是否存在。但預設隱藏且無權限,需先開啟系統物件顯示並授權。本文提供完整設定步驟與 SQL/DAO 兩種查詢方式。
MSysObjects → 勾選 **「讀取資料」** 權限。SELECT COUNT(*) AS RTab
FROM MSysObjects
WHERE (MSysObjects.Name = '目標表名')
AND (MSysObjects.Type = 1 OR MSysObjects.Type = 4 OR MSysObjects.Type = 6);
Type = 1:本地資料表;4:連結表 (ODBC);6:連結表 (其他)。1 存在,0 不存在。= 而非 LIKE 進行精確比對;若用 LIKE 需配合萬用字元 *。Function TableExists(db As DAO.Database, tableName As String) As Boolean
On Error Resume Next
Dim tdf As DAO.TableDef
Set tdf = db.TableDefs(tableName)
TableExists = (Err.Number = 0)
End Function
' 或直接查詢 MSysObjects
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT COUNT(*) FROM MSysObjects WHERE Name = '" & tableName & "' AND Type IN (1,4,6)")
TableExists = (rs.Fields(0).Value > 0)
using (var conn = new OleDbConnection(connStr))
{
conn.Open();
string sql = "SELECT COUNT(*) FROM MSysObjects WHERE Name = ? AND Type IN (1,4,6)";
using (var cmd = new OleDbCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@Name", tableName);
int count = (int)cmd.ExecuteScalar();
return count > 0;
}
}
若不想動權限,可嘗試直接查詢,捕捉例外:
BEGIN TRY
SELECT TOP 1 * FROM [目標表名];
PRINT '存在';
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 208 -- Invalid object name
PRINT '不存在';
ELSE THROW;
END CATCH
此法在 Access (Jet/ACE OLEDB) 中**不適用**,Access 不支援
TRY...CATCH,需用ON ERROR RESUME NEXT或 DAO 方式。
掌握 MSysObjects 查詢技巧,能在 Access 遷移、動態建表、版本升級腳本中靈活判斷物件存在性。