?
Solved

Programmatically insert AutoNumber field

Posted on 2005-03-06
10
Medium Priority
?
1,450 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

777 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