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?
 
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
...
0
 
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))"
0
 
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.
0
 
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
0
 
robertjmackayAuthor Commented:
The above code produces:

Additional information: Exception from HRESULT: 0x80040E3D.
Unhandled Exception: System.Runtime.InteropServices.COMException (0x80040E3D): Exception from HRESULT: 0x80040E3D.
0
 
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??
0
 
robertjmackayAuthor Commented:
Yes, it doesn't like the :

cat.Tables(tablename.Name).Columns.Append col
0
 
leclairmCommented:
Is it possible the column already exists??
0
 
robertjmackayAuthor Commented:
nope - only 1 field named AUTO
0
 
leclairmCommented:
Is this field a key?
0
 
leclairmCommented:
Okay forget previous question.  Column hasn't been added yet...
0
 
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
0
 
robertjmackayAuthor Commented:
No luck, same error
0
 
leclairmCommented:
Does it work without the:

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

line??
0
 
robertjmackayAuthor Commented:
no
0
 
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.  
0
 
robertjmackayAuthor Commented:
Still no go
0
 
leclairmCommented:
Okay can you post the code exactly as you have it in your app please??
0
 
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)
0
 
robertjmackayAuthor Commented:
great job

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

thanks

Rob Mackay
0
 
leclairmCommented:
Glad to get it working and thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.