当我们将Access数据库通过升迁或导入的方式到SQL Server数据库时,需要检查每一个表有没有主键,因为如果没有主键,将不能给表添加新数据,这里,我们就可以采用下面的代码来进行检查,具体代码如下:
Sub 检查主键()
On Error GoTo CreateKeyError
Dim cat As New ADOX.Catalog '需要引用ado ext
Dim i As Byte
Dim tbl As Object
Dim fld As Object
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In CurrentDb.TableDefs
If tbl.Name Like "MSys*" = False And tbl.Connect = "" Then
With cat.Tables(tbl.Name)
If .Keys.Count < 1 Then
Debug.Print tbl.Name & "没有主键"
GoTo N
End If
For i = 0 To .Keys.Count - 1
If .Keys.Item(0).Type = adKeyPrimary Then GoTo N
Debug.Print tbl.Name & "没有主键":
N:
Next
End With
End If
Next
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Exit Sub
CreateKeyError:
Set cat = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub