日期:2014-05-16  浏览次数:20991 次

在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