tbuendorf
asked on
ADOX Add Column Multi-Step OLE DB errors
I am trying to add a column to an Access DB table using ADOX. The column s/b an Autonumber-ReplicationID that autogenerates new values when records are added.
Here is my code:
Dim tbl As ADOX.Table
Dim col As New ADOX.Column
Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OL
& "Data Source=" & sDBPath & ";"
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn
Set tbl = cat.Tables(sTblName)
Set col = New ADOX.Column
With col
.Name = sColName
.Type = adGUID
.ParentCatalog = cat
End With
tbl.Columns.Append col
Everything works fine up to this point.
When the next line executes, I receive a -2147217887 Multiple-step OLD DB operation generated errors.
col.Properties("jet oledb:autogenerate").Value
This actually happens no matter what datatype I try to append or what Property I'm trying to set.
I've seen multiple examples of this same code all over the internet, but can't seem to get it to work.
Any help is greatly appreciated.
Here is my code:
Dim tbl As ADOX.Table
Dim col As New ADOX.Column
Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OL
& "Data Source=" & sDBPath & ";"
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn
Set tbl = cat.Tables(sTblName)
Set col = New ADOX.Column
With col
.Name = sColName
.Type = adGUID
.ParentCatalog = cat
End With
tbl.Columns.Append col
Everything works fine up to this point.
When the next line executes, I receive a -2147217887 Multiple-step OLD DB operation generated errors.
col.Properties("jet oledb:autogenerate").Value
This actually happens no matter what datatype I try to append or what Property I'm trying to set.
I've seen multiple examples of this same code all over the internet, but can't seem to get it to work.
Any help is greatly appreciated.
Try this:
Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim clx As ADOX.Column
Dim cat As ADOX.Catalog
Dim tblnam As ADOX.Table
Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Properties("Data Source") = "C:\Temp\Db1.mdb" '<== CHANGE TO YOUR DESIRED DB NAME AND PATH
cn.Open
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
Set tblnam = New ADOX.Table
Set clx = New ADOX.Column
tblnam.Name = "ReplicationIDTest"
clx.ParentCatalog = cat
clx.Type = adGUID
clx.Name = "IDField"
clx.Properties("AutoIncrem
clx.Properties("Fixed Length") = True
clx.Properties("Jet OLEDB:AutoGenerate") = True
clx.Properties("Jet OLEDB:Allow Zero Length") = True
tblnam.Columns.Append clx
tblnam.Columns.Append "DataField2", adInteger
cat.Tables.Append tblnam
Set clx = Nothing
Set cat = Nothing
cn.Close
Set cn = Nothing
End Sub
Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim clx As ADOX.Column
Dim cat As ADOX.Catalog
Dim tblnam As ADOX.Table
Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Properties("Data Source") = "C:\Temp\Db1.mdb" '<== CHANGE TO YOUR DESIRED DB NAME AND PATH
cn.Open
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
Set tblnam = New ADOX.Table
Set clx = New ADOX.Column
tblnam.Name = "ReplicationIDTest"
clx.ParentCatalog = cat
clx.Type = adGUID
clx.Name = "IDField"
clx.Properties("AutoIncrem
clx.Properties("Fixed Length") = True
clx.Properties("Jet OLEDB:AutoGenerate") = True
clx.Properties("Jet OLEDB:Allow Zero Length") = True
tblnam.Columns.Append clx
tblnam.Columns.Append "DataField2", adInteger
cat.Tables.Append tblnam
Set clx = Nothing
Set cat = Nothing
cn.Close
Set cn = Nothing
End Sub
ASKER
Reply to amit_g:
AutoIncrement is used for incrementing integer fields. I am using ReplicationID, which is a unique datatype. I'm still getting the error listed - I think it may have to do with the Connection object.
AutoIncrement is used for incrementing integer fields. I am using ReplicationID, which is a unique datatype. I'm still getting the error listed - I think it may have to do with the Connection object.
ASKER
Reply to jkaios:
I found this same solution at Microsoft. It applies to Creating a new table with an autogenerating ReplicationID. I'm trying to add the field to an Existing table and this is causing me problems. I can add the ReplicationID - I just can't seem to get the Autogenerate property set.
I found this same solution at Microsoft. It applies to Creating a new table with an autogenerating ReplicationID. I'm trying to add the field to an Existing table and this is causing me problems. I can add the ReplicationID - I just can't seem to get the Autogenerate property set.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jkaios -
It turns out that the positioning of the .ParentCatalog in the With statement also was critical in getting this working properly. Once I moved it to the beginning and moved the .Properties back in the With everything began working just fine.
Thanks for your help.
It turns out that the positioning of the .ParentCatalog in the With statement also was critical in getting this working properly. Once I moved it to the beginning and moved the .Properties back in the With everything began working just fine.
Thanks for your help.
With col
.Name = sColName
.Type = adGUID
.ParentCatalog = cat
.Properties("Autoincrement
End With
tbl.Columns.Append col