无法在MS Access中创建自动增量字段 [英] Can't create a Auto Increment Field in MS Access

问题描述

我有一个使用ADOX引用创建数据库的代码.
我在互联网上搜索了如何创建具有自动增量"属性的字段，这就是我发现的"

I have a code that create a database using ADOX reference.
I search in the internet on how to create a field with Auto Increment properties, this is what i found "link[^]"

With tbl
    .Columns.Append "ContactId", adInteger
    .Columns("ContactId").Properties("AutoIncrement") .Value = True
End With

但是，当我尝试将其添加到代码中时，出现错误消息:
Item cannot be found in the collection corresponding to the requested name or ordinal.

But when I try adding this to my codes I got an error message:
Item cannot be found in the collection corresponding to the requested name or ordinal.

Public Sub CreateDatabaseADO(ByRef dbCon As DatabaseConn)
    Dim newDB As ADOX.Catalog
    Set newDB = New ADOX.Catalog
    newDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & dbCon.path & _
        ";Jet OLEDB:Engine Type=5;"
      ' Engine Type=5 = Access 2000 Database
      ' Engine Type=4 = Access 97 Database
    Set newDB = Nothing
    
    Dim DBX As ADOX.Catalog
    Set DBX = New ADOX.Catalog
    DBX.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & dbCon.path
    
    Dim i As Integer
    For i = 0 To UBound(dbCon.TableArr)
        Dim tblNew As ADOX.Table
        Set tblNew = New ADOX.Table
        tblNew.Name = dbCon.TableArr(i).Name
        
        Dim j As Integer
        For j = 0 To UBound(dbCon.TableArr(i).FieldArr)
            Dim DataType As Integer
            If dbCon.TableArr(i).FieldArr(j).FieldDataType = DataTypes.Memo Then
                DataType = adLongVarWChar
            ElseIf dbCon.TableArr(i).FieldArr(j).FieldDataType = DataTypes.Number Then
                DataType = adInteger
            ElseIf dbCon.TableArr(i).FieldArr(j).FieldDataType = DataTypes.Date Then
                DataType = adDate
            Else
                DataType = adVarWChar
            End If
            
            Dim col As ADOX.Column
            Set col = New ADOX.Column
            col.Name = dbCon.TableArr(i).FieldArr(j).Name
            col.Type = DataType
            col.Attributes = adColNullable
            If DataType = adVarWChar Then col.DefinedSize = dbCon.TableArr(i).FieldArr(j).Size

            'ADDED CODES
            If dbCon.TableArr(i).FieldArr(j).AutoIncrement And DataType = adInteger Then col.Properties("AutoIncrement").Value = True
            'OR THIS SAME ERROR
            tblNew.Columns("SYSTEM_ID").Properties("AutoIncrement").Value = True

            tblNew.Columns.Append col
            Set col = Nothing
            
            If UCase(dbCon.TableArr(i).FieldArr(j).Name) = UCase(dbCon.TableArr(i).IndexName) Then
                Dim ind As ADOX.Index
                Set ind = New ADOX.Index
                ind.Name = dbCon.TableArr(i).FieldArr(j).Name
                ind.PrimaryKey = dbCon.TableArr(i).PrimaryKey
                ind.Columns.Append dbCon.TableArr(i).FieldArr(j).Name
                tblNew.Indexes.Append ind
                Set ind = Nothing
            End If
        Next j
        DBX.Tables.Append tblNew
        Set tblNew = Nothing
    Next i
    Set DBX = Nothing
End Sub

我真的希望有人能提供帮助
在此先感谢...

附加:
我也尝试使用此方法，但会发生相同的错误

I''m really hoping someone could help
Thanks in Advance...

ADDITIONAL:
Also I try using this but same error happens

MsgBox tblNew.Columns("SYSTEM_ID").Properties(0).Name

推荐答案

您是否看到过: ^ ]-部分:设置其他字段属性和属性?

Have you seen this: http://msdn.microsoft.com/en-us/library/office/aa164917%28v=office.10%29.aspx[^] - section: Setting Additional Field Attributes and Properties?

我真的不知道为什么我的问题中的代码无法运行，但是我尝试使用它它可以工作...

I really don''t know why the codes in my question won''t run but I try using this an it works...

Public Sub CreateDatabaseADO(ByRef dbCon As DatabaseConn)
    Dim newDB As ADOX.Catalog
    Set newDB = New ADOX.Catalog
    newDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & dbCon.path & _
        ";Jet OLEDB:Engine Type=5;"
      ' Engine Type=5 = Access 2000 Database
      ' Engine Type=4 = Access 97 Database
    Set newDB = Nothing
    
    Dim DBX As ADOX.Catalog
    Set DBX = New ADOX.Catalog
    DBX.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & dbCon.path
    
    Dim i As Integer
    For i = 0 To UBound(dbCon.TableArr)
        Dim tblNew As ADOX.Table
        Set tblNew = New ADOX.Table
        Set tblNew.ParentCatalog = DBX
        tblNew.Name = dbCon.TableArr(i).Name
        
        Dim j As Integer
        For j = 0 To UBound(dbCon.TableArr(i).FieldArr)
            Dim DataType As Integer
            If dbCon.TableArr(i).FieldArr(j).FieldDataType = DataTypes.Memo Then
                DataType = adLongVarWChar
            ElseIf dbCon.TableArr(i).FieldArr(j).FieldDataType = DataTypes.Number Then
                DataType = adInteger
            ElseIf dbCon.TableArr(i).FieldArr(j).FieldDataType = DataTypes.Date Then
                DataType = adDate
            Else
                DataType = adVarWChar
            End If
            
            'PUT AN IF ELSE HERE WHETHER ITS AUTOINCREMENT
            If dbCon.TableArr(i).FieldArr(j).AutoIncrement And DataType = adInteger Then
                tblNew.Columns.Append dbCon.TableArr(i).FieldArr(j).Name, adInteger
                tblNew.Columns.Item(dbCon.TableArr(i).FieldArr(j).Name) .Properties("AutoIncrement").Value = True
            Else
                Dim col As ADOX.Column
                Set col = New ADOX.Column
                col.Name = dbCon.TableArr(i).FieldArr(j).Name
                col.Type = DataType
                col.Attributes = adColNullable
                If DataType = adVarWChar Then col.DefinedSize = dbCon.TableArr(i).FieldArr(j).Size
                tblNew.Columns.Append col
                Set col = Nothing
            End If
            
            If UCase(dbCon.TableArr(i).FieldArr(j).Name) = UCase(dbCon.TableArr(i).IndexName) Then
                Dim ind As ADOX.Index
                Set ind = New ADOX.Index
                ind.Name = dbCon.TableArr(i).FieldArr(j).Name
                ind.PrimaryKey = dbCon.TableArr(i).PrimaryKey
                ind.Columns.Append dbCon.TableArr(i).FieldArr(j).Name
                tblNew.Indexes.Append ind
                Set ind = Nothing
            End If
        Next j
   
        DBX.Tables.Append tblNew
        Set tblNew = Nothing
    Next i
    Set DBX = Nothing
End Sub

这篇关于无法在MS Access中创建自动增量字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！