Topics Search

Inserting records into tables with autonumber

Inserting records into tables with autonumber
Views: 44
how do I enter data into tables with columns which have datatypes of Autonumber?

Here is the code column that has a datatype autonumber when I created it in SQL Management Studio

CaseNumber int IDENTITY(1,1) NOT NULL PRIMARY KEY

When I imported the tables into Access, the column CaseNumber was set to Autonumber. I've done some research but I can't get it to work.

What I found goes like this:
INSERT INTO (col2,col3) values ('value2','value3');
col1 needs not to be included because it is autonumber.
Sponsored Links:

More topics

 

Inserting records into tables with autonumber

Preview
How do I enter data into tables with columns which have datatypes of Autonumber? Here is the code column that has a datatype autonumber when I created it in SQL Management Studio

CaseNumber int IDENTITY(1,1) NOT NULL PRIMARY KEY

When I imported the tables into Access, the column CaseNumber was set to Autonumber. I've done some research but I can't get it to work.
 

Autonumber not working

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

Autonumber

Preview
I designed a database and enter some test records. On some of the tables I decided I would be better to have an autonumber. I deleted the records in the table and did away with the relationships. Changed the field to an autonumber. When I entered the first record it started the autonumber at 3. If I deleted that record it starts at the next number (4). I would like for it to start at 1 and go up in sequence (1, 2, 3 ect). Can I do this?
 

Duplication check on tables. AutoNumber

Preview
I have two master tables set up.

Right now in both tables, I have the PK set on a particular field(so the PKs are not the AutoNumber)

If I manually change the PK to be the AutoNumber for each of the two tables, is there any way to prevent adding the same records twice in each table?

I am assuming this can be done in a little VBA code? Some kind of duplication check?

Could someone guide me on how to go about implmenmenting this feature
 

Find next incremental value for an AutoNumber field

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

Make autonumber consecutive

Preview
I have an autonumber field in a table which is linked to fields in multiple other tables.

These existing numbers cannot be changed without having to change all the corresponding data in the other tables.

I have been testing the database by adding test records to check queries, new pieces of code etc. are working. However, this naturally creates new autonumbers, and after deletion of thesetest records the database 'remembers' the last false record and assigns new real records with a number which is not consecutive to the previous real record.

I had thought that compacting the database after testing and deleting the test records would reset the autonumber field so that new records would have consecutive numbers, but this does not happen.
 

dynamically add ID field to query

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

Restart from 1 AutoNumber field

Preview
I have more related tables:
T_1, T_2 . T_k, T_k+1 . T_n
Every table has a primary key defined as AutoNumber.
For k = 2 to n every table has a field defined as LookUp on T_k-1 primary key.
In the test phase I make new records and I remove records so, the primary key (AutoNumber) loose the natural order: (I.e. start at 287 and the next number is 301).

Is there an easy way (from VBA) to restore the AutoNumber field to start at 1 with step 1 ? Of course, the relation between related fields must remain the same.

maybe you can teach me how to remove and restore the relationships between tables (from VBA)

I use MS OFFICE 2007.
 

1 autonumber for 3 tables

Preview
I have three tables which each have their own uniqueID field that I really need to be unique across the three (for union at a later point). Therefore I have created a table which has two fields - an autonumber field and another indicating which table it lives on.

I basically want to work out an efficient way of adding a new record to the autonumber table and adding the number it produces to a new record within its equvalent foreign key.

It needs to be reasonably efficient as around 1000 records will be added at a time on one of the tables, whereas only 1 record will be added at a time on the other two.
 

Using 1 Autonumber field for 3 tables

Preview
I have three tables which each have their own uniqueID field that I really need to be unique across the three (for union at a later point). Therefore I have created a table which has two fields - an autonumber field and another indicating which table it lives on.

I basically want to work out an efficient way of adding a new record to the autonumber table and adding the number it produces to a new record within its equvalent foreign key.

It needs to be reasonably efficient as around 1000 records will be added at a time on one of the tables, whereas only 1 record will be added at a time on the other two.