Multiple Step OLE DB operation generated errors

[Duck]

I am very new to VBA and ADOX and in trying to come up with a simple
procedure to create a small table I get the error message:

"Multiple Step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done"

I don't know what I'm doing wrong...Here's my code:

Public Sub CreateTable()

'This procedure will create a table
'Declare variables
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

'Define catalog object
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

'Define table object
Set tbl = New ADOX.Table
tbl.Name = "tblClient2"

'Define column object
Set col = New ADOX.Column
col.Name = "CustID"
col.Type = adInteger
Set col.ParentCatalog = cat
col.Properties("Autoincrement") = True
col.Properties("Description") = "Client ID number"
tbl.Columns.Append col

Set col = New ADOX.Column
col.Name = "FirstName"
col.Type = adVarWChar
Set col.ParentCatalog = cat
col.Attributes = adColNullable
col.Properties("Description") = "Client First Name"
tbl.Columns.Append col

Set col = New ADOX.Column
col.Name = "LastName"
col.Type = adVarWChar
Set col.ParentCatalog = cat
col.Attributes = adColNullable
col.Properties("Description") = "Client Last Name"
tbl.Columns.Append col

cat.Tables.Append tbl

End Sub

Can anyone shed any light on the error of my ways???

 

[Douglas J. Steele]

What line of code raises the error?

See whether setting col to Nothing after you've appended it to the Columns
collection but before you try to reinstantiate it makes a difference:

tbl.Columns.Append col
Set col = Nothing

Set col = New ADOX.Column

 

[Duck]

What line of code raises the error?

See whether setting col to Nothing after you've appended it to the Columns
collection but before you try to reinstantiate it makes a difference:

   tbl.Columns.Append col
  Set col = Nothing

  Set col = New ADOX.Column

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)


















- Show quoted text -

When I click "Debug" on the Error message box the last line of code is
highlighted:
cat.Tables.Append tbl

 

[Duck]

What line of code raises the error?

See whether setting col to Nothing after you've appended it to the Columns
collection but before you try to reinstantiate it makes a difference:

   tbl.Columns.Append col
  Set col = Nothing

  Set col = New ADOX.Column

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)


















- Show quoted text -

I forgot to mention also that setting the col object to Nothing:
Set col = Nothing
had no effect, I'm still getting the same error

 

[Douglas J. Steele]

Sorry, nothing pops out as being incorrect. Why not use DAO instead? Since
you're dealing with a Jet database, that's probably more appropriate anyhow:
DAO was developed specifically for Jet, whereas ADO is a more generic
approach.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


On Apr 19, 5:36 am, "Douglas J. Steele"

 

[Tom Wickerath]

Hi Duck,

Using a process of elimination, I was able to determine that this line of
code, which appears in the 2nd and 3rd column definitions, is the offending
line:

col.Attributes = adColNullable

I tried using the equivalent numeric value, 2, like this:

col.Attributes = 2

but that does not help. Hopefully this will be enough of a clue for you to
proceed. I much prefer using DAO myself.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[datAdrenaline]

Hello Duck (Tom and Doug) ...

I know I am late to the party, but .... since you are setting the
ParentCatalog in order to set the provider specific properties (ie:
Autoincrement, "Description) you need to use the following syntax ....

col.Properties("Nullable") = True


The syntax you originally used ...

col.Attributes = adColNullable

works fine if you do not set the ParentCatalog of the ADOX column.
Note that you do not need to set the ParentCatalog unless you are
setting JET/ACE specific properties.

Regards,
Brent Spaulding | datAdrenaline | Access MVP

 

[Tom Wickerath]

Hi Brent,

I see that you know a lot more about using ADO than I do. Thanks for your
input. If I'm understanding you correct (?), you are saying to use something
like the following for the FirstName and LastName fields. I'm testing within
Access (JET) so I think I still need the reference to ParentCatalog. Is this
correct? I tried one field with col.Properties("Nullable") = True and the
other with col.Properties("Nullable") = False, and it seems to work okay now.


Set col = New ADOX.Column
col.Name = "FirstName"
col.Type = adVarWChar
Set col.ParentCatalog = cat
col.Properties("Nullable") = True '<---Added
'col.Attributes = adColNullable
'<---Commented out
col.Properties("Description") = "Client First Name"
tbl.Columns.Append col

Set col = New ADOX.Column
col.Name = "LastName"
col.Type = adVarWChar
Set col.ParentCatalog = cat
col.Properties("Nullable") = False '<---Added
'col.Attributes = adColNullable
'<---Commented out
col.Properties("Description") = "Client Last Name"
tbl.Columns.Append col


It was nice meeting you in person at the summit!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[ludivine lisa coline]

What line of code raises the error?

See whether setting col to Nothing after you've appended it to the Columns
collection but before you try to reinstantiate it makes a difference:

tbl.Columns.Append col
Set col = Nothing

Set col = New ADOX.Column