Create ID Autonumber Field with SQL Statement

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:

Related Topics

Find next incremental value for an AutoNumber field
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
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 TABLE and AutoNumber fields
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. Can anyone tell me what I'm doing wrong here?

I'm using ADO (#importing msado15.dll


Create Access Table in VB
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
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
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.


Nest a query inside another query or in an expression by using a subquery
Sometimes you may want to use the results of a query as a field in another query, or as a criterion for a query field. For example, suppose that you want to see the interval between orders for each of your products. To create a query that shows this interval, you need to compare each order date to other order dates for that product. Comparing these order dates also requires a query. You can nest this query inside of your main query by using a subquery (subquery: An SQL SELECT statement that is inside another select or action query.).
You can write a subquery in an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) or in a Structured Query Language (SQL) statement in SQL view (SQL view: A window that displays the SQL statement for the current query or that is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, Access constructs the SQL equivalent in SQL view.).


Use a control in an SQL statement in Access
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?


Autonumber not working
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.


Create Duplicate Record
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.


Duplicating Current Record
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.
[CODE]
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?


SQL Code to Make Query / Report
I have created a form that will generate a SQL statement based on many user choices regarding columns to be in the query/report, and filters. I have tested my code and it generates a valid SQL statement that I can enter into a query SQL View.

I am trying to use this SQL statement to either alter a pre-made query so that it can be exported to Excel, or create a temporary query so that it can be exported to Excel.

I would prefer to do query instead of report since there are 40 columns that can be chosen. I have been doing some research and have not found a close match.


tbale- autonumber field
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.


SQL Code to Make Query / Report
I have an interesting challenge right now.

I have created a form that will generate a SQL statement based on many user choices regarding columns to be in the query/report, and filters. I have tested my code and it generates a valid SQL statement that I can enter into a query SQL View. I am trying to use this SQL statement to either alter a pre-made query so that it can be exported to Excel, or create a temporary query so that it can be exported to Excel. I would prefer to do query instead of report since there are 40 columns that can be chosen.

Has anyone seen this done before? I have been doing some research and have not found a close match


Create a SQL Query
To create a SQL (Structured Query Language) query in Access 2003, you’ll need enough information to create a SQL statement. Fortunately, the program will create the statement for you, so it’s not necessary to write it.


Generate Autonumber in existing table
I have an Access 2007 database that I need to upsize to SQL Server 2008. One of the tables does not have a primary key and SQL Server does not like this. I thought about adding an autonumber field to the existing table but when I add the field and hit "save" I get a combination of errors about file sharing and editing registry for file locks. The table has approximately 1,500,000 records.


SQL statement to run on the OnClick event
Below is some code that contains a SQL statement to run on the OnClick event of a button. When copying the SQL statement produced by the "stSQL" variable, and pasting into the SQL view of a new query, I get results just fine.
But I keep getting error 2342: "A RunSQL action requires an argument consisting of an SQL statement.", when it runs in code.
Variable stSQL, after being assigned returns in the immediate window as:

Code...


dynamically add ID field to query
I have a request to implement some kind of row number for some reports and other things, and I am trying to figure out an easy way to do this that does not require inserting or creating some kind of autonumber field to a table.

what I have in mind is, in a query, create a virtual column that does the same thing autonumber does without actually having to append an autonumber field.

I think this might come in handy and am curious as to if this is possible. sure would be nice!


Autofill field based on autonumber field
I have an add-only form that has an autonumber field. This autonumber gets a new number once you fill any other field on the form.
I need to have on the same form an autofill field that has the autonumber included on it, besides other data from the same form.
This autofill field should be stored on a table.

Let's say
1. Opened Form
2. Autonumber populated once I type on any field (ie 5623)
3. Autofill field empty but once ?I click on it? I get this:
DCA/TX-09-5623 (This field should be stored on a table)
where:
'DCA/TX' is the department I work for (combo box on form)
'09' is the last two digits of the current year (date on form)
'5623' is the autonumber given by MS Access

The objective is to generate a code name on things that come to the departments based on Access autonumber feature


Autonumber Problem?
Here is my issue. I have 3 Access dbs that all use a field called "product id" that uses autonumber. The person who set this up is using the auto number like a part number.

I need to combine all three to together but when I do it will not let continuing using autonumber because their is data in the field now.

Is their anyway to get around this. I still need to use autonumber after combining.