先引用Microsoft ADO Ext. 2.x for DDL and Security Private Function IsPriKey(ByRef cnn As Connection, ByVal TableName As String, ByVal FieldName As String) As Boolean Dim cat As ADOX.Catalog Dim k As ADOX.Key Dim c As ADOX.Column
Set cat = New Catalog Set cat.ActiveConnection = cnn FieldName = UCase$(FieldName) For Each k In cat.Tables(TableName).Keys If k.Type = adKeyPrimary Then For Each c In k.Columns If UCase$(c.Name) = FieldName Then IsPriKey = True Exit Function End If Next c End If Next k IsPriKey = False End Function
Dim cnn As ADODB.Connection Dim strCnn As String Dim Ctl As ADOX.Catalog strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" strCnn = strCnn & "Data Source=" & App.Path & "\gz.mdb;" strCnn = strCnn & "Jet OLEDB:Engine Type=5;" Set cnn = New ADODB.Connection cnn.Open strCnn Set Ctl = New ADOX.Catalog Ctl.ActiveConnection = cnn'''''''''''''''''''''''''''''''''''''''''''''' Dim s As Integer s = Ctl.Tables("固定表").Indexes.Count
Dim TF As Boolean TF = Ctl.Tables("固定表").Indexes(0).PrimaryKey
Dim ps As Integer ps = Ctl.Tables("固定表").Indexes(0).Columns.Count If TF Then MsgBox "有主键" If s > 0 Then MsgBox "主键个数: " & ps Dim i As Integer
For i = 0 To ps - 1 MsgBox "主键字段名 " & i + 1 & " : " & Ctl.Tables("固定表").Indexes(0).Columns.Item(i).Name Next i
End If Else MsgBox "无主键" End If
还可以用ADO连接对象的OpenSchema 方法来做。
Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim PK As ADOX.Index Set cat = New ADOX.Catalog set cat.ActiveConnection=cnn Set tbl = New ADOX.Table Set tbl.ParentCatalog = cat With tbl .Name = "aaa" .Columns.Append "f1", adVarWChar, 20 .Columns.Append "f2", adInteger
'设置f1为主键 Set PK = New ADOX.Index PK.Name = "PK" PK.PrimaryKey = True PK.Columns.Append "f1" .Indexes.Append PK Set PK = Nothing End With cat.Tables.Append tbl Set tbl = Nothing
Private Function IsPriKey(ByRef cnn As Connection, ByVal TableName As String, ByVal FieldName As String) As Boolean
Dim cat As ADOX.Catalog
Dim k As ADOX.Key
Dim c As ADOX.Column
Set cat = New Catalog
Set cat.ActiveConnection = cnn FieldName = UCase$(FieldName)
For Each k In cat.Tables(TableName).Keys
If k.Type = adKeyPrimary Then
For Each c In k.Columns
If UCase$(c.Name) = FieldName Then
IsPriKey = True
Exit Function
End If
Next c
End If
Next k
IsPriKey = False
End Function
Dim strCnn As String
Dim Ctl As ADOX.Catalog strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;"
strCnn = strCnn & "Data Source=" & App.Path & "\gz.mdb;"
strCnn = strCnn & "Jet OLEDB:Engine Type=5;" Set cnn = New ADODB.Connection
cnn.Open strCnn Set Ctl = New ADOX.Catalog
Ctl.ActiveConnection = cnn''''''''''''''''''''''''''''''''''''''''''''''
Dim s As Integer
s = Ctl.Tables("固定表").Indexes.Count
Dim TF As Boolean
TF = Ctl.Tables("固定表").Indexes(0).PrimaryKey
Dim ps As Integer
ps = Ctl.Tables("固定表").Indexes(0).Columns.Count If TF Then
MsgBox "有主键"
If s > 0 Then
MsgBox "主键个数: " & ps Dim i As Integer
For i = 0 To ps - 1
MsgBox "主键字段名 " & i + 1 & " : " & Ctl.Tables("固定表").Indexes(0).Columns.Item(i).Name
Next i
End If
Else
MsgBox "无主键"
End If
Dim tbl As ADOX.Table
Dim PK As ADOX.Index Set cat = New ADOX.Catalog
set cat.ActiveConnection=cnn Set tbl = New ADOX.Table
Set tbl.ParentCatalog = cat
With tbl
.Name = "aaa"
.Columns.Append "f1", adVarWChar, 20
.Columns.Append "f2", adInteger
'设置f1为主键
Set PK = New ADOX.Index
PK.Name = "PK"
PK.PrimaryKey = True
PK.Columns.Append "f1"
.Indexes.Append PK
Set PK = Nothing
End With
cat.Tables.Append tbl
Set tbl = Nothing
其实我还有问题,不过就不再这里问了(分数太少)另发一贴吧,关于如何读取表单名含空格的表单?请高手回答。