?
Solved

Problem  using ADOX to add fields

Posted on 2006-04-20
22
Medium Priority
?
1,127 Views
Last Modified: 2013-12-25
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)

0
Comment
Question by:robertjmackay
  • 13
  • 9
22 Comments
 
LVL 11

Expert Comment

by:leclairm
ID: 16497853
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
 

Author Comment

by:robertjmackay
ID: 16497914
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
 
LVL 11

Expert Comment

by:leclairm
ID: 16497933
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:leclairm
ID: 16498011
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
 

Author Comment

by:robertjmackay
ID: 16498066
The above code produces:

Additional information: Exception from HRESULT: 0x80040E3D.
Unhandled Exception: System.Runtime.InteropServices.COMException (0x80040E3D): Exception from HRESULT: 0x80040E3D.
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16498105
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
 

Author Comment

by:robertjmackay
ID: 16498125
Yes, it doesn't like the :

cat.Tables(tablename.Name).Columns.Append col
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16498137
Is it possible the column already exists??
0
 

Author Comment

by:robertjmackay
ID: 16498192
nope - only 1 field named AUTO
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16498321
Is this field a key?
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16498336
Okay forget previous question.  Column hasn't been added yet...
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16498429
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
 

Author Comment

by:robertjmackay
ID: 16498551
No luck, same error
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16498666
Does it work without the:

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

line??
0
 

Author Comment

by:robertjmackay
ID: 16498686
no
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16498842
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
 

Author Comment

by:robertjmackay
ID: 16498982
Still no go
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16499047
Okay can you post the code exactly as you have it in your app please??
0
 

Author Comment

by:robertjmackay
ID: 16499094
       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
 
LVL 11

Accepted Solution

by:
leclairm earned 2000 total points
ID: 16499135
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
 

Author Comment

by:robertjmackay
ID: 16499370
great job

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

thanks

Rob Mackay
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16499386
Glad to get it working and thanks for the points.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question