在ACCESS中删除主键约束报错
alter table [材料入库表] Drop primary key(编号)
报错:
至少一个参数没有被指定值。
至少一个参数没有被指定值。
ALTER TABLE 语句中的语法错误。
请高手指点
[解决办法]
用sql代码生成的表中的主键与手工创建的表的主键约束名不同。
手工创建的表的主键约束名为：primarykey
而SQL代码生成表的主键约束需要用代码获取，如下：
Public Sub fnGetPrimaryKeyName()
Dim v_DB As New ADOX.Catalog
Dim v_Table As ADOX.Table
Dim v_Key As ADOX.Key
Dim objTable As New ADOX.Table
Dim strKey As String
Dim strKeyName As String
v_DB.ActiveConnection = CurrentProject.Connection
objTable.ParentCatalog = v_DB
Set objTable = v_DB.Tables( "你的表名 ")
For Each v_Key In objTable.Keys
Select Case v_Key.Type
Case adKeyPrimary
strKey = "Primary KEY "
strKeyName = v_Key.Name
End Select
Next
Debug.Print "主键约束名： " & strKeyName
End Sub
[解决办法]
引用ADOX，显示主键约束名及主键字段：
Dim v_DB As New ADOX.Catalog
Dim v_Table As ADOX.Table
Dim v_Key As ADOX.Key
Dim objTable As New ADOX.Table
Dim strKey As String
Dim strKeyName As String
v_DB.ActiveConnection = CurrentProject.Connection
objTable.ParentCatalog = v_DB
Set objTable = v_DB.Tables( "材料入库表 ")
For Each v_Key In objTable.Keys
Select Case v_Key.Type
Case adKeyPrimary
strKey = "Primary KEY "
strKeyName = v_Key.Name
For Each ff In v_Key.Columns
MsgBox ff.Name & strKeyName
Next
End Select
Next
