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???
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???
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