ALTER Queries to Access trough VB.NET

[penkomitev]

Hello,
I managed to execute queries like SELECT * FROM a, but I cannot execute the ones with ALTER, because the following error appear: Cannot execute data definition statements on linked data sources.

I read something about Back-end & Front-end queries(they are different), but at end I didn't succeed. I've tried 15 different source codes and each with problems.
The last I tried is:

        Dim strConnectionString As String

        strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file_path

        Dim oConnection As New System.Data.OleDb.OleDbConnection(strConnectionString)

        oConnection.Open()

        Dim oCommand As System.Data.OleDb.OleDbCommand

        oCommand = oConnection.CreateCommand()

        oCommand.CommandText = "Alter Table procProductsList Add NewColumn Text"

        oCommand.ExecuteNonQuery()

It is just very strange how it does not want to work and what would be the other sollution, that I haven't tried. I've tried this one too:

  Cn = New ADODB.Connection
        Cat = New ADOX.Catalog
        objTable = New ADOX.Table

        'Open the connection
        Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file_path)

        'Open the Catalog
        Cat.ActiveConnection = Cn

        'Create the table


        objTable.Name = "procProductsList"
        objTable.Columns.Append("LastName", ADOX.DataTypeEnum.adVarWChar, 40)
        objTable.Columns.Append("ID", ADOX.DataTypeEnum.adInteger)
        objTable.Columns.Append("Department", ADOX.DataTypeEnum.adVarWChar, 20)

        ''append tables to database
        ADOXcatalog.Tables.Append(ADOXtable)
        Append the newly created table to the Tables Collection
        Cat.Tables.Append(objTable)


        ' clean up objects
        'objKey = Nothing
         objTable = Nothing
         Cat = Nothing
         Cn.Close()
        Cn = Nothing

The problem with it is that it is used only for new table to the current database and I cannot decide how to change it so it can be used to update a database, which
already exists before that moment. The error in this case is "Database already exists", which is enough proof that the script tries to create the database, not to update it.

I forgot to say(maybe only in the title) that I am using VB.NET.

[penkomitev]

I am a bit surprised nobody answered my topic, but I can at last say that I managed to solve the problem. After reading in the ADO specification on the Microsoft site, I found out that it is known that ALTER queries are not supported. Therefore, I started to look for different codes, which can solve it. After whole, I had to use ADOX for creating new table with the old fields and the ones I need. Then to transfer all the data from the old to the new. After that, I have to delete the old one and rename the new one with the name of the old. This operation does not take long as my tables are not with more than 250-300 records. If someone is interested in the snippet I used, just write me.