Topics Search

Create ID Autonumber Field with SQL Statement

Create ID Autonumber Field with SQL Statement
Views: 141
I have a requirment to create an ID Field and use AutoNumber via SQL but not sure what I should use.

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
Sponsored Links:

Similar posts...


Find next incremental value for an AutoNumber field

Find next incremental value for an AutoNumber field Icon
I'm using Access 2003/XP/2000/97 and VB6 as the front-end. I need to get the next value in the AutoNumber field before inserting the next record using SQL. How can I find out what the next value will be in an AutoNumber field?

CREATE TABLE and AutoNumber fields

CREATE TABLE and AutoNumber fields Icon
I'm trying to create a table that contains an AutoNumber field using the following SQL:

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).

Create Access Table in VB

Create Access Table in VB Icon
Allows the programmer to create an MS Access table in Visual Basic where the primary key's field data type is set to AutoNumber. It is not like creating the primary key field in Access where you can select AutoNumber date type. In VB 5 and 6 you can't request AutoNumber for a field type in the SQL Create Table string, it does not exist. So to create a table in VB where primary key's numeric field type will be AutoNumber, you have to do it the way the included source code shows. Hope this helps.

Use a SQL statement to delete data

Use a SQL statement to delete data Icon
Writing SQL to delete data from an MS Access table is just about the easiest SQL statement you can write.
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.

Pass field value selected in Combo Box to SQL Query

Pass field value selected in Combo Box to SQL Query Icon
My form displays a combo box populated by a SQL SELECT statement. The user will select a row from the combo box. I then want the value in a field of the row selected in the combo box to be used as the selection criteria in a SQL statement.

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.

Autonumber not working

Autonumber not working Icon
I created a table in a budget database without a autonumber field. I then inserted a autonumber field after creating the form by inserting a row in the table which works fine in the table, now I want to use a text box on the form with BudgetID from the autonumber field to give me the total amount of records in the database but the autonumber field is not in the record sourcedropdown.

Use a control in an SQL statement in Access

Use a control in an SQL statement in Access Icon
In Access 2003/XP/2000/97, I've created a form where the user enters a value in a textbox. I want to use this value to create an SQL statement. How do I use a control's value in an SQL statement in VBA code?

Duplicating Current Record

Duplicating Current Record Icon
Below is the following code that I have used to duplicate a record in a table where there is a Primary Key that is a data type of AutoNumber. My form does have subforms, but that informationis not necessary to duplicate.
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?

tbale- autonumber field

tbale- autonumber field Icon
I have a database (version 2002) that uses an autonumber filed to create unique records.

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.

Create Duplicate Record

Create Duplicate Record Icon
SQL Buffs,

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.