Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / May 2008

Tip: Looking for answers? Try searching our database.

Multiple Step OLE DB operation generated errors

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Duck - 19 Apr 2008 12:48 GMT
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 - 19 Apr 2008 14:36 GMT
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

Signature

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

>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:
[quoted text clipped - 50 lines]
>
> Can anyone shed any light on the error of my ways???
Duck - 19 Apr 2008 15:04 GMT
On Apr 19, 5:36 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> What line of code raises the error?
>
[quoted text clipped - 66 lines]
>
> - Show quoted text -

When I click "Debug" on the Error message box the last line of code is
highlighted:
  cat.Tables.Append tbl
Douglas J. Steele - 19 Apr 2008 17:28 GMT
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.

Signature

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

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

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

<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> What line of code raises the error?
>
[quoted text clipped - 5 lines]
>
> Set col = New ADOX.Column
Duck - 19 Apr 2008 16:05 GMT
On Apr 19, 5:36 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> What line of code raises the error?
>
[quoted text clipped - 66 lines]
>
> - 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
ludivine lisa coline - 14 May 2008 14:47 GMT
> What line of code raises the error?
>
[quoted text clipped - 60 lines]
>>
>> Can anyone shed any light on the error of my ways???
Tom Wickerath - 20 Apr 2008 00:22 GMT
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
__________________________________________

> 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:
[quoted text clipped - 50 lines]
>
> Can anyone shed any light on the error of my ways???
datAdrenaline - 29 Apr 2008 05:52 GMT
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 - 29 Apr 2008 06:31 GMT
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
__________________________________________

> Hello Duck (Tom and Doug) ...
>
[quoted text clipped - 14 lines]
> Regards,
> Brent Spaulding | datAdrenaline | Access MVP
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.