Can't create a Auto Increment Field in MS Access

Question

4.00/5 (1 vote)

See more:

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[^]"

VB

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

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.

VB

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

VB

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

Posted 8-Oct-12 5:14am

hansoctantan

Updated 8-Nov-12 23:07pm

v3

Add a Solution

Comments

ZurdoDev 8-Oct-12 11:34am

Make sure you are referencing the same versions of ADO.

hansoctantan 8-Oct-12 11:50am

What did you mean same versions?
I just add this reference to my Project "Microsoft ADO Ext. 2.8 for DLL and Security"

keniks143 8-Oct-12 13:05pm

Try to add this reference 'Microsoft ActiveX Data Accss Objects 2.8 Library

hansoctantan 9-Oct-12 4:18am

that reference is already added to my project but same happens

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2012-10-08T10:46:00

Solution 1

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?

Posted 8-Oct-12 10:46am

Maciej Los

hansoctantan · Accepted Answer · 2012-10-08T23:00:00

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

VB

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