Set column "Identity" property with VBA 1

[MaltaC]

I am appending a new table in an Access Project. I am trying to set the "Identity" property (autoincrement) of the first column in the table. I receive the following error message:

"run time error 3265, Item cannot be found in the collection corresponding to the requested name or ordinal.

My partial code is below:

Set cnxn = New ADODB.Connection
cnxn.Provider = "SQLOLEDB"
cnxn.Properties("Data Source").Value = "RADIANCE\PETROTEST"
cnxn.Properties("Initial Catalog").Value = "master"
cnxn.Properties("Integrated Security").Value = "SSPI"
cnxn.Open
Set cat1 = New ADOX.Catalog
Set cat1.ActiveConnection = cnxn

'Create and name the table

Set ImportTable = New ADOX.Table
With ImportTable
.Name = "zzDonors"
Set .ParentCatalog = cat1
With .Columns
.Append "Rec_Index", adInteger
.Item("Rec_Index").Properties("Identity(1,1)") = 0
.Append "Supplier", adVarWChar, 100
.Append "Terminal_Name", adVarWChar, 50
.Append "Terminal_Abbr", adVarWChar, 20
.Append "Terminal_City", adVarWChar, 50
.Append "Terminal_State", adVarWChar, 5
.Append "Product_Name", adVarWChar, 120
.Append "Brand_Type", adVarWChar, 5
.Append "Effective_Date", adDBTimeStamp
.Append "Effective_Time", adDBTimeStamp
.Append "Price", adSingle
.Append "Change", adSingle
End With
End With
'

'Append new table to Tables collection of Project
cat1.Tables.Append ImportTable


When I remove the following code the table is appended but of course the "identity" property is not set:

.Item("Rec_Index").Properties("Identity(1,1)") = 0

Please, what is the correct code to set this property?

Thanks

Gerry

 

[OnTheFly]

I tried to find an answer for you but could not. If you want another method of creating a table that makes it easy to create an IDENTITY field the following code may help.

Function CreateTheTable() As String
Dim strTableName As String
Dim cmd As ADODB.Command
Dim strCommand As String
Dim Conn As ADODB.Connection

'Creates a table
On Error GoTo CreateTheTable_Error

'Attempt to Create a Table
strTableName = "MyTableName"

Set Conn = CurrentProject.Connection

'Build the Command Text statement
strCommand = "CREATE TABLE " & strTableName & _
" (" & _
"DetailID int IDENTITY PRIMARY KEY, ExpDate smalldatetime, " & _
"ExpAmount smallmoney DEFAULT 0, ExpDetails varchar(100) " & _
") "

'Build the table
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = Conn
Restart_Here:
.CommandText = strCommand
.CommandType = adCmdText
.Execute
End With

CreateTheTable = strTableName

Exit_CreateTheTable:
Set Conn = Nothing
Set cmd = Nothing
Exit Function


CreateTheTable_Error:
If Err.Number = -2147217900 Then 'Object Exists Error
With cmd
.CommandText = "DROP TABLE " & strTableName
.Execute
End With
Resume Restart_Here
Else
MsgBox Err.Number & "-" & Error$, vbCritical, "Error...Error...Error"
Resume Exit_CreateTheTable
End If

End Function

Hope this helps.

OnTheFly