?
Solved

ADOX Add Column Multi-Step OLE DB errors

Posted on 2007-07-31
6
Medium Priority
?
645 Views
Last Modified: 2013-12-25
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.OLEDB.4.0;" _
                & "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 = True

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.
0
Comment
Question by:tbuendorf
  • 3
  • 2
6 Comments
 
LVL 58

Expert Comment

by:amit_g
ID: 19604137
You should be using Autoincrement instead of autogenerate...

    With col
        .Name = sColName
        .Type = adGUID
        .ParentCatalog = cat
        .Properties("Autoincrement") = True
    End With
    tbl.Columns.Append col
0
 
LVL 12

Expert Comment

by:jkaios
ID: 19604775
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("AutoIncrement") = False
   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
0
 

Author Comment

by:tbuendorf
ID: 19609147
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:tbuendorf
ID: 19609171
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.
0
 
LVL 12

Accepted Solution

by:
jkaios earned 2000 total points
ID: 19613012
I notice in your original code that you call the "col.Properties("jet oledb:autogenerate").Value = True" AFTER you called the "tbl.Columns.Append". This is why you get the error.  You should initialize all field properties BEFORE appending the new field into the table.

I've test this on an existing table, and it worked fine.  You should try it.

Private Sub Command1_Click()
   CreateField "C:\Temp\db1.mdb", "tblTest", "RepIDTest"
End Sub

Private Sub CreateField(sDBPath As String, sTblName As String, sColName As String)

    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.OLEDB.4.0;" & _
             "Data Source=" & sDBPath & ";"

    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = cnn
    Set tbl = cat.Tables(sTblName)
    Set col = New ADOX.Column

    With col
        .ParentCatalog = cat
        .Name = sColName
        .Type = adGUID
        .Properties("jet oledb:autogenerate").Value = True
    End With

    tbl.Columns.Append col

End Sub
0
 

Author Comment

by:tbuendorf
ID: 19617029
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question