?
Solved

Programmatically insert AutoNumber field

Posted on 2005-03-06
10
Medium Priority
?
1,455 Views
Last Modified: 2010-08-05
Is there any way to:

insert value for an AutoNumber field programmatically via code ?

Thnx
0
Comment
Question by:rajesh_khater
  • 6
  • 3
10 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 13470377
Only when the table doesn't have such a field with a query like:
 Create Unique Index ClientID On POST (ClientID)

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13470380
From the helpfile:
CREATE [ UNIQUE ] INDEX index
    ON table (field [ASC|DESC][, field [ASC|DESC], ...])
    [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13470390
Hmm, guess the autonumber needs:

  ALTER TABLE tblX ADD COLUMN RecCounter AUTOINCREMENT

Reference:  

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Nic;o)
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13470396
If your table already has an autonumber field, and you want to put a value into it via VBA, you can treat it as a normal numeric field:

CurrentDb.Execute "INSERT INTO MyTable (MyAutonumberField) VALUES(1000)"

Alternatively, to create a new autonumber field in an existing table (as opposed to modifying an existing field):

CurrentDb.Execute "ALTER TABLE MyTable ADD COLUMN MyAutonumber Field COUNTER"
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13470419
Hmm, could indeed be a question to fill the value into an autonumber field, but won't recommend to insert a value by code into such a field as A97 and A2000 will work differently in such a case and even a "deadlock" can occur when a wrong value (not the max) is inserted.

An autonumber is only to be used "as is" and only a reset to zero can be advised by emptying the table and run a database Compact & Repair.

Nic;o)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13470433
> will work differently in such a case

How so? I haven't noticed any differences in behaviour when inserting values through an SQL INSERT statement.

However, I *have* seen several instances of Jet 4.0 databases losing the autonumber seed value, and therefore generating incorrect sequential numbers. This was probably due to an early Jet service pack on one of the workstations, and the field needed to be reseeded with this fix:
http://support.microsoft.com/kb/287756

It is also a useful method of changing the seed value without inserting and deleting a phantom record.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13470468
Without the SP's installed A2000 will change the seed value to the last inserted value + 1 (thus the possible deadlock when it reaches the next used value) while A97 will create a "gap" as it will increment the seed value without having a row written for the specific value.
In general inserting autonumber values should be avoided.
When some logical value/operation is to be preformed on an autonumber it shouldn't be defined as an autonumber.

Nic;o)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13470497
That's probably why I've never seen it - I've never used A2K at all, and I've never used A2K2 with anything less than SP3/Jet SP8.
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 13471086
nico,

> Without the SP's installed A2000 will change the seed value to the last inserted value + 1

Pls tell me which SP should I install to fix this ? Also, if I install the SP in my machine, and distribute the database (.dat file) along with my app, will it run properly on the user's machine ?

I need to insert values in an AutoNumber field in the following scenario:

I am having a lot of records in a table which I am displaying in a VB application. Whenever the user changes any of the values, i dont keep track of which record(s) have been modified, coz its too complex.

Instead, I delete ALL the old records for a particular MASTER key, and insert the fresh values. This is in a single TRANSACTION.

I was thinking, that while inserting these records, could I use the Autonumber field values of the deleted records by first doing a SELECT.


0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 13471440
Service packs:
SR1a/2:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q276367
SR3:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;326585

Better to switch to a controlled key in such a case.
Just change the autonumber to a normal number and use when inserting the MAX(keyfield) + 1
The delete/insert won't give any trouble that way.
Just a bit hard to understand the MASTER key as it looks like you just delete a selection of the rows...

Nic;o)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question