Hi,
I need to rename columns in an Access database file programatically (OLEDB provider). There is a way though (create a new
column, copy data from the old column into the new one and delete the old column), but I was wondering if there is a simpler
technique.

Thanks in advance...

With regards
nvx

On Fri, 04 Aug 2006 10:07:28 +0200, nvx <n**@somewhere.com> wrote:

¤ Hi,
¤ I need to rename columns in an Access database file programatically (OLEDB provider). There is a way though (create a new
¤ column, copy data from the old column into the new one and delete the old column), but I was wondering if there is a simpler
¤ technique.

If you are using OLEDB this would require ADO/ADOX (Microsoft ADO Ext 2.x for DDL and Security):

        Dim ADOXCatalog As New ADOX.Catalog
        Dim ADOConnection As New ADODB.Connection

            ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=e:\My Documents\db1.mdb;" & _
                "Jet OLEDB:Engine Type=5;")

            ADOXCatalog.ActiveConnection = ADOConnection
            ADOXCatalog.Tables("Table1").Columns("Col1").Name = "NewColumnName"

            ADOXCatalog.ActiveConnection.Close()
            ADOXCatalog.ActiveConnection = Nothing


Paul
~~~~
Microsoft MVP (Visual Basic)

Hi Paul,
thank you very much for your reply. I code in C#.NET (VC# 2005 Express Edition) and I'm a beginner, so it took me a while even
to reference the ADOX, ADODB and define the catalog and connection. Opening the connection required four parameters,
meaning of three of them was obvious, but I didn't know what to supply as Options (integer value) so I've put zero there.
Could you please clarify the meaning of this parameter a bit?

Two more things:
1. Do I need to add the tables etc. into the catalog myself or will that be done automatically after executing something similar to
DataAdapter.Fill()?
2. Do I have to install any additional software on the machine my application will run on?

Thank you in advance.

Have a nice day...
nvx


Paul Clement napsal(a):
Show quoteHide quote

On Fri, 04 Aug 2006 18:16:10 +0200, nvx <n**@somewhere.com> wrote:

¤ Hi Paul,
¤ thank you very much for your reply. I code in C#.NET (VC# 2005 Express Edition) and I'm a beginner, so it took me a while even
¤ to reference the ADOX, ADODB and define the catalog and connection. Opening the connection required four parameters,
¤ meaning of three of them was obvious, but I didn't know what to supply as Options (integer value) so I've put zero there.
¤ Could you please clarify the meaning of this parameter a bit?
¤
¤ Two more things:
¤ 1. Do I need to add the tables etc. into the catalog myself or will that be done automatically after executing something similar to
¤ DataAdapter.Fill()?
¤ 2. Do I have to install any additional software on the machine my application will run on?
¤
¤ Thank you in advance.
¤

OK, I'm not sure if I understand your questions. The connection string is rather basic. The only
thing you need to change is the database location (Data Source). You don't need to change the
Provider string or the Jet OLEDB Engine Time value. As a matter of fact you can omit the Jet
OLEDB:Engine Type if your wish.

Regarding your additional questions, I'm not sure what you're asking in question 1 and how it
relates to renaming a column. Could you be a bit more specific?


Paul
~~~~
Microsoft MVP (Visual Basic)

Paul, thank you for such a prompt response. Firstly, here's the code in C#:

    ADOX.Catalog cat = new ADOX.Catalog();
    ADODB.Connection ADOconn = new ADODB.Connection();
    ADOconn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Engine Type=5", "admin", "", 0);
    cat.ActiveConnection = ADOconn;
    cat.Tables["tablename"].Columns["oldcolname"].Name = "newcolname";
    ADOconn.Close();
    cat.ActiveConnection = null;

The thing is the Connection.Open has really four parameters here, intellisense tooltip says this:

    "void _Connection.Open(string ConnectionString, string UserID, string Password, int Options)"

No overloads, just this one with four parameters. Also, Catalog.ActiveConnection does not contain Close() here. That's why I had to
close the connection directly through ADODB.Connection.

As for the question 1: since I could successfully build the solution a few moments later I was able to check how the above-mentioned
code works. Everything was fine and no table had to be added into the Catalog. The zero supplied as Options is probably OK. Anyway,
I will google it once more and hopefully find some information about this parameter.

Question 2: I've noticed two DLLs appeared in the bin folder with compiled solution. These DLLs are Interop.ADODB.dll and
Interop.ADOX.dll. Correct question would probably be: Do I have to copy these two DLLs to the folder with my application everywhere
I install it? Isn't there a way to "embed" them into the compiled EXE? In case I copied the application to some folder without those
two files and run it, everything was fine, except when I tried to rename some column an error message saying "... component
Interop.ADOX.dll <version info> could not be loaded." appeared and (obviously) column was not renamed.

I'm sorry for my English, it probably makes the text much more confusing.

With regards
nvx


Paul Clement napsal(a):
Show quoteHide quote

On Fri, 04 Aug 2006 22:01:42 +0200, nvx <n**@somewhere.com> wrote:

¤ Paul, thank you for such a prompt response. Firstly, here's the code in C#:
¤
¤     ADOX.Catalog cat = new ADOX.Catalog();
¤     ADODB.Connection ADOconn = new ADODB.Connection();
¤     ADOconn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Engine Type=5", "admin", "", 0);
¤     cat.ActiveConnection = ADOconn;
¤     cat.Tables["tablename"].Columns["oldcolname"].Name = "newcolname";
¤     ADOconn.Close();
¤     cat.ActiveConnection = null;
¤
¤ The thing is the Connection.Open has really four parameters here, intellisense tooltip says this:
¤
¤     "void _Connection.Open(string ConnectionString, string UserID, string Password, int Options)"

Just specify the connection string (first parameter). All the other arguments are optional if you
have a valid connection string.

¤
¤ No overloads, just this one with four parameters. Also, Catalog.ActiveConnection does not contain Close() here. That's why I had to
¤ close the connection directly through ADODB.Connection.
¤

Yes, your approach is correct.

¤ As for the question 1: since I could successfully build the solution a few moments later I was able to check how the above-mentioned
¤ code works. Everything was fine and no table had to be added into the Catalog. The zero supplied as Options is probably OK. Anyway,
¤ I will google it once more and hopefully find some information about this parameter.
¤
¤ Question 2: I've noticed two DLLs appeared in the bin folder with compiled solution. These DLLs are Interop.ADODB.dll and
¤ Interop.ADOX.dll. Correct question would probably be: Do I have to copy these two DLLs to the folder with my application everywhere
¤ I install it? Isn't there a way to "embed" them into the compiled EXE? In case I copied the application to some folder without those
¤ two files and run it, everything was fine, except when I tried to rename some column an error message saying "... component
¤ Interop.ADOX.dll <version info> could not be loaded." appeared and (obviously) column was not renamed.
¤

Just distribute the interop files with your application assembly (typically in the \bin folder). Yes
they are required.

¤ I'm sorry for my English, it probably makes the text much more confusing.

Your English is perfectly understandable. I just needed a bit more information.


Paul
~~~~
Microsoft MVP (Visual Basic)

SQL: ALTER COLUMN...  afaik.
You can do a lot with SQL


Show quoteHide quote

Hi Edwin,
I'm afraid this is not working. I'm able to change the column type etc., but not it's name. Nevertheless, maybe I'm doing
something wrong... Anyway, thank you for your response.

Have a nice day...
nvx


Edwin Knoppert napsal(a):
Show quoteHide quote

¤ SQL: ALTER COLUMN...  afaik.
¤ You can do a lot with SQL
¤

This isn't supported by Jet SQL.


Paul
~~~~
Microsoft MVP (Visual Basic)

Timeout - max pool size reached
Create MDB in ASP.NET 2
Table Adapter and Xml DataType
Setting the order of rows stored in a DataTable
SqlClient Connections created don't get reused in the pool and cause error in ASP.NET Page