Topics Search

AutoNumber Data Type

AutoNumber Data Type
Views: 146
The AutoNumber data type stores an integer that Microsoft Access increments (adds to) automatically as you add new records. You can use the AutoNumber data type as a unique record identification for tables having no other unique value
Sponsored Links:

More topics

 

CREATE TABLE and AutoNumber fields

Preview
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

Preview
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

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

Using 2 Autonumbers in 1 table

Preview
I have 2 tables (Registrations and Courses), where each table has a Primary Key with a data type of Autonumber (RegID and CourseID). I need to add the PK from the Courses table to the Registration table which causes problems (only 1 Autonumber per table).

In order to avoid this problem, I would like to change the Data Type for the PK in the Courses table, but with allowing the user to automatically create the value of each record (generating a custom CourseID - I.e. Course01, Course02, Course03, etc).

Any ideas on how to create the custom field for data entry purposes?
 

Autofill field based on autonumber field

Preview
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
 

Create an AutoNumber field that starts with a number greater than 1

Preview
In Microsoft Access tables, the AutoNumber field type allows you to assign a unique sequential number to each row in a table. AutoNumber fields are often used as the Primary Key, since they are unique and permanent (i.e. the value cannot be changed once it is assigned).
For new tables, AutoNumbering begins with 1, and is incremented by 1 with each new row. However, we occasionally want to start the AutoNumber at a number higher than 1.
 

Reset autonumber in a concatenate field

Preview
I would like to build a concatenate field using Year, ID Number, and a 3-digit autonumber. Year is a combo box in a form (YYYY), NumID is the two or three digit ID Number for a person, and then the three digit autonumber. I would also like the three digit autonumber to follow in sequence based on Year and NumID. For example, when starting a new year I would like the autonumber to reset to 000. When entering data for multiple people, I would like the autonumber to advanced based on the last autonumber for that person, not the next autonumber in the list.
 

Importing existing Autonumber fields

Preview
I have been using an online database and having issues with it. I might have to migrate my existing database to Access. The problem is I don't know how to migrate my existing autonumber fields into Access without messing my database up. I need to be able to use my existing autonumbers as an autonumber data type
 

Importing existing Autonumber fields

Preview
I have been using an online database and having issues with it. I might have to migrate my existing database to Access. The problem is I don't know how to migrate my existing autonumber fields into Access without messing my database up.

I need to be able to use my existing autonumbers as an autonumber data type.
 

Help with Imports..

Preview
I have an excel database in which I am trying to import into the access db I am working on, but gave up as I had some field type mismatches (CUSTOMER ID).

* In access I tried using Autonumber. (for the CUSTOMER ID field), when importing I bumped into trouble, the field in excel had general type and it is of format UKP#####. So it is not being imported.

so I decided to copy and paste the details field by field. and finally once it is done I added another field CUSTOMER ID, set it autonumber with format UKP#####. It worked fine for the first 172 records, but the 173rd record suddenly jumped up to 5495.

Or is there another way to convert the type of the excel db to the autonumber while importing to access?