Problem using ADOX to add fields

I am trying to add the following fields to an existing access 2000 database

Field name = "textfield"
type = text
Length = 30
Allow zero length = yes

Field name = "boofield"
type = yes/no (boolean)

I have added the reference to ADOX in my project but am stynbling on the syntax.  Below is what I have so far but it doesn't work.  I am getting an error: "Item cannot be found in the collection corresponding to the requested name or ordinal"

imports ADOX


        Dim cat As New ADOX.Catalog
        Dim col As New ADOX.Column
        Dim Cn As New ADODB.Connection
        Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my documents\consult\rmacdata\rjmdata.mdb")
        cat.ActiveConnection = Cn
        Dim tablename As New ADOX.Table
        tablename.Name = "test"

        col = New ADOX.Column
        col.Name = "test"
        col.Type = DataTypeEnum.adChar
        col.DefinedSize = 30

        cat.Tables(tablename).Columns.Append("test", DataTypeEnum.adChar, 30)

robertjmackayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leclairmCommented:
Here's an easier way:

Dim Cn As New ADODB.Connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my documents\consult\rmacdata\rjmdata.mdb")
Cn.Execute "CREATE TABLE test (test text(30))"
robertjmackayAuthor Commented:
The table already exists.  I simply want to add fields.

The problem is that I need to be able to set the "Allow zero lenth" property of the text file.   I understand in order to do this I need to use ADOX
leclairmCommented:
Or, like you originally started to do:

 Dim cat As New ADOX.Catalog
 Dim Cn As New ADODB.Connection
 Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my documents\consult\rmacdata\rjmdata.mdb")
cat.ActiveConnection = Cn
Dim tablename As New ADOX.Table
tablename.Name = "test"
tablename.Columns.Append "test", adVarWChar, 30
tablename.Columns.Append "boofield", adBoolean
cat.Tables.Append tablename

No need for the col object.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

leclairmCommented:
In that case try:

Dim cat As New ADOX.Catalog
        Dim col As New ADOX.Column
        Dim Cn As New ADODB.Connection
        Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my documents\consult\rmacdata\rjmdata.mdb")
        cat.ActiveConnection = Cn
        Dim tablename As New ADOX.Table
tablename.Name = "test"

col.Name = "ColumnName"
col.DefinedSize = 30
col.Type = adVarWChar
col.ParentCatalog = cat
col.Properties("Jet OLEDB:Allow Zero Length").Value = True
cat.Tables(tablename.Name).Columns.Append col
robertjmackayAuthor Commented:
The above code produces:

Additional information: Exception from HRESULT: 0x80040E3D.
Unhandled Exception: System.Runtime.InteropServices.COMException (0x80040E3D): Exception from HRESULT: 0x80040E3D.
leclairmCommented:
I was testing this with vb 6 using Microsoft ADO Ext. 2.8 and it's working fine..  I don't see why it would be any different with .net, although I'm not an expert in .net.  Is it on the cat.tables(tablename.name).columns.append line??
robertjmackayAuthor Commented:
Yes, it doesn't like the :

cat.Tables(tablename.Name).Columns.Append col
leclairmCommented:
Is it possible the column already exists??
robertjmackayAuthor Commented:
nope - only 1 field named AUTO
leclairmCommented:
Is this field a key?
leclairmCommented:
Okay forget previous question.  Column hasn't been added yet...
leclairmCommented:
Try this.  Just a slight change to how the column is added, although it boils down to pretty much the same thing.

Dim cat As New ADOX.Catalog
        Dim col As New ADOX.Column
        Dim Cn As New ADODB.Connection
        Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my documents\consult\rmacdata\rjmdata.mdb")
        cat.ActiveConnection = Cn
        Dim tablename As New ADOX.Table

Set tablename = cat.Tables("test")
col.Name = "NewCol"
col.DefinedSize = 30
col.Type = adVarWChar
col.ParentCatalog = cat
col.Properties("Jet OLEDB:Allow Zero Length").Value = False
tablename.Columns.Append col
robertjmackayAuthor Commented:
No luck, same error
leclairmCommented:
Does it work without the:

col.Properties("Jet OLEDB:Allow Zero Length").Value = False

line??
robertjmackayAuthor Commented:
no
leclairmCommented:
Wow.  This is proving difficult.  You could try adding the engine type to your connection string:

Cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my documents\consult\rmacdata\rjmdata.mdb;Jet OLEDB:Engine Type=5;")

I really don't know why else this isn't working.  
robertjmackayAuthor Commented:
Still no go
leclairmCommented:
Okay can you post the code exactly as you have it in your app please??
robertjmackayAuthor Commented:
       Dim cat As New ADOX.Catalog
        Dim col As New ADOX.Column
        Dim Cn As New ADODB.Connection
        Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\my documents\consult\rmacdata\rjmdata.mdb;Jet OLEDB:Engine Type=5;")

        cat.ActiveConnection = Cn
        Dim tablename As New ADOX.Table

        tablename = cat.Tables("test")
        col.Name = "NewCol"
        col.DefinedSize = 30
        col.Type = DataTypeEnum.adChar
        col.ParentCatalog = cat
        'col.Properties("Jet OLEDB:Allow Zero Length").Value = False
        tablename.Columns.Append(col)
leclairmCommented:
Not quite the same as what I posted.  You're missing the "set" for the tablename object and the type is supposed to be adVarWChar.  Hopefully this works.

...
Set tablename = cat.Tables("test")
col.Name = "NewCol"
col.DefinedSize = 30
col.Type = adVarWChar
col.ParentCatalog = cat
col.Properties("Jet OLEDB:Allow Zero Length").Value = False
tablename.Columns.Append col
...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robertjmackayAuthor Commented:
great job

Note that I had to use:
DataTypeEnum.adVarWChar as apposed to adVarWChar

thanks

Rob Mackay
leclairmCommented:
Glad to get it working and thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.