Create ID Autonumber Field with SQL Statement
I am creating a table from various sources and combining them into 1 main table. All the code works fine but I am missing adding the ID Field as Autonumber.
The table name is "DUA Combined
Thank you for taking the time to report an issue.
What's wrong... Please write below.
CREATE TABLE [tmpTable] ([ID] AutoNumber);
but I get a COM error stating that there's an error in the field description. If I use Long as the data type it accepts it, but according to w3schools online AutoNumber is a perfectly valid Access data type.
I've tried it with all lowercase, with PRIMARY KEY and a few other combinations but it simply refuses.
I'm using ADO (#importing msado15.dll).
This example will use the SQL statement to create an Access query, but you can use the statement in a macro or VBA as well.
The SQL statement is a nested SELECT and the value selected in the combo box is the selection criteria in the inner select statement.
The problem is how to pass the value selected in the combo box to the SQL query.
Also, I have the following problem . When I call the SQL query by clicking a button, a window popups asking me to enter the value for the parameter. I don't want this popup to appear. What I want to achieve is that after the user selects a row in the combo box, I want the field value to be passed to the SQL statement's WHERE clause and the SQL should execute and display the rows selected.
The problem is that the AutoNumber field becomes "0." I believe this is happening because of the statement in the code "On Error Go To 0"
Can someone explain to me why this going to error instead of creating a new AutoNumber in the Auto field?
of a sudden when I try to add a new record the autonumber field starts using numbers that already exist so then I get an error message and can not save the record.
Is there a way I can copy a record line from a table and paste it in the same table with a new 'autonumber' ID? Manually, its a fairy easy task. I would think there should be a fairly simple SQL statement to accomplish this without have to copy individual fields.