OpenSchema 方法示例 (VB)
适用于:Access 2013、Office 2013
此示例使用 OpenSchema 方法显示 Pubs 数据库中每个表的名称和类型。
'BeginOpenSchemaVB
'To integrate this code
'replace the data source and initial catalog values
'in the connection string
Public Sub OpenSchemaX()
On Error GoTo ErrorHandler
Dim Cnxn As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnxn As String
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Cnxn.Open strCnxn
Set rstSchema = Cnxn.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
' clean up
rstSchema.Close
Cnxn.Close
Set rstSchema = Nothing
Set Cnxn = Nothing
Exit Sub
ErrorHandler:
' clean up
If Not rstSchema Is Nothing Then
If rstSchema.State = adStateOpen Then rstSchema.Close
End If
Set rstSchema = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
'EndOpenSchemaVB
此示例在 OpenSchema 方法 Criteria 参数中指定TABLE_TYPE查询约束。 因此,仅返回 Pubs 数据库中指定的视图的架构信息。 本示例随后显示每个表的名称和类型。
'BeginOpenSchema2VB
Public Sub Main()
On Error GoTo ErrorHandler
Dim Cnxn2 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnxn As String
Set Cnxn2 = New ADODB.Connection
strCnxn = "Provider=sqloledb;" & _
"Data Source=MySqlServer;Initial Catalog=Pubs;Integrated Security=SSPI; "
Cnxn2.Open strCnxn
Set rstSchema = Cnxn2.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "VIEW"))
Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
' clean up
rstSchema.Close
Cnxn2.Close
Set rstSchema = Nothing
Set Cnxn2 = Nothing
Exit Sub
ErrorHandler:
' clean up
If Not rstSchema Is Nothing Then
If rstSchema.State = adStateOpen Then rstSchema.Close
End If
Set rstSchema = Nothing
If Not Cnxn2 Is Nothing Then
If Cnxn2.State = adStateOpen Then Cnxn2.Close
End If
Set Cnxn2 = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
'EndOpenSchema2VB