Error trying to ALTER TABLE in an Access database

[WMR1]

I have a simple problem: I am trying to add a column to an existing table using the ALTER TABLE command. I pulled the code off the MS documentation:

ALTER TABLE Notes_file
ADD DateOfBirth date;

However, when I do this I get an error: "Query must have at least one destination field." Not sure what this means at all; a search didn't do much to enlighten me. I've tried using different tables/queries and adding different columns and datatypes. Not sure where I am going wrong.

I want to embed this is a VB.net program and also have a question as to how I execute the. To create the query I have been using the .CreateQueryDef command. Would I use this or perhaps .Execute?

Thanks for any help.

[sapator]

For future reference SQL questions goes to Database forums.

https://www.sqlservertutorial.net/sq...le-add-column/

[WMR1]

Well, the link just repeats all the other documentation. When I use this command I get the error indicated, hence the post. I also want guidance concering implementation within VB.net, hence the post.

[OptionBase1]

https://learn.microsoft.com/en-us/of...oft-access-sql

If you look at the example statement on this page, you see that you need to use ADD COLUMN, not just ADD.

[WMR1]

Thanks for answering, but I think you misread the directions. Column is in lowercase; it is the (variable) name to be assigned the column. If you scroll down a bit to the code examples, you will see what I mean.

[wes4dbt]

Thanks for answering, but I think you misread the directions. Column is in lowercase; it is the (variable) name to be assigned the column. If you scroll down a bit to the code examples, you will see what I mean.

It seems you've misread or probably went to the wrong link. COLUMN is not lower case in the link provided by OP1.

[OptionBase1]

Thanks for answering, but I think you misread the directions. Column is in lowercase; it is the (variable) name to be assigned the column. If you scroll down a bit to the code examples, you will see what I mean.

Wrong. Good luck.

[ChrisE]

here a sample

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DB2010.accdb" & ";Persist Security Info=True")
        conn.Open()
        Dim cmd As New OleDb.OleDbCommand("", conn)
        cmd.CommandText = "ALTER TABLE Customer ADD DateOfBirth date;"
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub

[WMR1]

Here is another from the link provided by sapator;

ALTER TABLE table_name
ADD
column_name_1 data_type_1 column_constraint_1,
column_name_2 data_type_2 column_constraint_2,


Note the absence of COLUMN following ADD. So I am perplexed why you insist I am wrong.
...,
column_name_n data_type_n column_constraint_n;

[wes4dbt]

here a sample

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DB2010.accdb" & ";Persist Security Info=True")
        conn.Open()
        Dim cmd As New OleDb.OleDbCommand("", conn)
        cmd.CommandText = "ALTER TABLE Customer ADD DateOfBirth date;"
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub

Interesting. It seems to work with or without "COLUMN".

[OptionBase1]

Here is another from the link provided by sapator;

ALTER TABLE table_name
ADD
column_name_1 data_type_1 column_constraint_1,
column_name_2 data_type_2 column_constraint_2,


Note the absence of COLUMN following ADD. So I am perplexed why you insist I am wrong.
...,
column_name_n data_type_n column_constraint_n;

Because your response to mine was just wrong. The link I provided showed the following example:

ALTER TABLE Employees ADD COLUMN Notes TEXT(25)

How, exactly, is "column" in lowercase in that example?

[Shaggy Hiker]

Because your response to mine was just wrong. The link I provided showed the following example:

ALTER TABLE Employees ADD COLUMN Notes TEXT(25)

How, exactly, is "column" in lowercase in that example?

True, but as Wes seemed to show, the word COLUMN appears to be optional, though I always include it.

[OptionBase1]

True, but as Wes seemed to show, the word COLUMN appears to be optional, though I always include it.

That seems to be the case, yes. I just tested directly in Access and it adds a column with or without COLUMN in the ADD statement.

[OptionBase1]

OP, I apologize.

So, perhaps it would be best to verify some of the obvious assumptions. Do you have a table named "Notes_file" in the database that you are running this query against?

Edit: There is more information given by the OP on SO, including the actual VB.NET code used.

https://stackoverflow.com/questions/...base-using-sql

[WMR1]

No problem and thanks for listening. Just now I pasted:

ALTER TABLE Notes_file
ADD COLUMN DateOfBirth date;

in as a new query in Access and I am still getting that "Query must have at least one destination field." error. Is it because it is not a query?

And thanks for the point to the VB code to both of you.

[wes4dbt]

You need to post the relevant code. That one line doesn't tell us enough.

[OptionBase1]

No problem and thanks for listening. Just now I pasted:

ALTER TABLE Notes_file
ADD COLUMN DateOfBirth date;

in as a new query in Access and I am still getting that "Query must have at least one destination field." error. Is it because it is not a query?

And thanks for the point to the VB code to both of you.

No idea then. I ran your query under two problematic scenarios:

1. The table didn't exist
2. The field already existed

Both generated errors, but different errors than what you are getting.

Good luck.

[wes4dbt]

After rereading your last post it sounds like you are trying to run this query using Access. So I created a new query in Access. I ran it with and without COLUMN, both ran fine and added the column.

This is a .Net forum, it doesn't sound like your using .Net. But ChrisE provided you with a good .Net example to follow if you want to try.

With the information provided there's not much else help I can provide.

[OptionBase1]

After rereading your last post it sounds like you are trying to run this query using Access. So I created a new query in Access. I ran it with and without COLUMN, both ran fine and added the column.

This is a .Net forum, it doesn't sound like your using .Net. But ChrisE provided you with a good .Net example to follow if you want to try.

With the information provided there's not much else help I can provide.

I believe the OP was initially using VB.NET to run the query, but I think they may have decided to remove VB.NET from the equation and try running the query directly in Access as I did when I was testing it.

At this point it probably should be moved to the Database forum or the Office forum, since the error persists outside of anything VB.NET related.

[Zvoni]

https://learn.microsoft.com/en-us/of...tination-field

Workaround

To work around this problem, use one of the following methods:

• For each database that is affected by this problem, open the database in Access, click Options on the File menu, select Current Database, and then clear the Track name AutoCorrect info check box.
• Make sure that each query is opened and saved while in you are in Design View.

[WMR1]

Thanks Zvoni; that did clear up the error I was getting, though I have no idea why unchecking Track name AutoCorrect would do that. On to the VB implementation!

[Zvoni]

Thanks Zvoni; that did clear up the error I was getting, though I have no idea why unchecking Track name AutoCorrect would do that. On to the VB implementation!

https://support.microsoft.com/en-us/...4-a93c10a9d98b

Track name AutoCorrect info

When you enable name AutoCorrect to track name changes for a database, Access does the following:

1. Attempts to gain exclusive access to the database.
2. Searches for objects that are open.
3. Prompts and closes open objects.
4. Updates name maps by opening, saving, and closing all tables, queries, forms, and reports.
5. Returns the database to whatever state it was in before it was promoted to Exclusive.

At a guess:
Your table is open somewhere,
or you have a VIEW based on "SELECT * FROM Notes_file",
or you have exclusive access to the Database, meaning access itself fails to claim exclusive access (see point 1)