VB.Net中如何检测Access数据库内是否存在指定查询

下面提供两种可直接复用的实现方案，都兼容你现有的ADO连接逻辑： #

方案1：使用ADO原生OpenSchema方法（推荐，兼容性最好） #

Access中保存的查询属于ADO定义的视图类型，可直接调用连接对象的OpenSchema方法筛选匹配的查询对象，不需要额外调整数据库权限：

Dim dbCustSpex_ADO As New ADODB.Connection
Dim rsCheck As ADODB.Recordset
Const TARGET_QUERY_NAME As String = "test_upt"
' 未引用ADODB类型库时可手动赋值常量：Const adSchemaViews = 23

dbCustSpex_ADO.ConnectionString = CommonAccessMethods.GetConnectionString(PATH_CUSTSPEX)
dbCustSpex_ADO.Open()

' 筛选匹配名称的查询对象
Set rsCheck = dbCustSpex_ADO.OpenSchema(adSchemaViews, Array(Empty, Empty, TARGET_QUERY_NAME))

If Not rsCheck.EOF Then
    ' 查询存在再执行操作
    dbCustSpex_ADO.Execute TARGET_QUERY_NAME
End If

' 资源清理
rsCheck.Close
Set rsCheck = Nothing
dbCustSpex_ADO.Close
Set dbCustSpex_ADO = Nothing

方案2：查询Access系统表MSysObjects #

Access系统表MSysObjects存储了所有数据库对象的元数据，类型为5的对象就是保存的查询：

Dim dbCustSpex_ADO As New ADODB.Connection
Dim rsCheck As ADODB.Recordset
Const TARGET_QUERY_NAME As String = "test_upt"
Dim checkSql As String

dbCustSpex_ADO.ConnectionString = CommonAccessMethods.GetConnectionString(PATH_CUSTSPEX)
dbCustSpex_ADO.Open()

' 转义查询名中的单引号避免语法错误
checkSql = "SELECT Count(*) AS cnt FROM MSysObjects WHERE Name = '" & Replace(TARGET_QUERY_NAME, "'", "''") & "' AND Type = 5"
Set rsCheck = dbCustSpex_ADO.Execute(checkSql)

If rsCheck!cnt > 0 Then
    dbCustSpex_ADO.Execute TARGET_QUERY_NAME
End If

' 资源清理
rsCheck.Close
Set rsCheck = Nothing
dbCustSpex_ADO.Close
Set dbCustSpex_ADO = Nothing

注意：如果使用方案2遇到权限报错，需要手动调整Access数据库的系统表访问权限，优先选择方案1即可避免该问题。

内容的提问来源于stack exchange，提问作者MK01111000