Autonumber in Database?

[JamieWarren09]

Hi guys,

So basically I have a form on VB that adds data to a database... one of the fields in the database is called 'leadid' in the database structure it is not an autonumber.

throughout my project it makes reference to the field 'leadid' and other tables have fields that are identified by leadid.

Unfortunately when building the database i forgot to set leadid as autonumber... and then imported my sql database into microsoft access (this was to have all the existing data on the new database)

When using my form to add a new lead to the database, it needs to add a number to leadid. Is there anyway of doing it without having autonumber and without using an existing number being used?

As microsoft access wont let me change it to autonumber...

Thanks
Jamie

[TysonLPrice]

This would work in MS SQL. Maybe you can convert it to Access.

declare @leadid int

set @leadid = isnull((select max(leadid) from YourTable with (updlock)),0) + 1

insert into Yourtable(leadid, other stuff)
values (@leadid, other stuff)

It doesn't address "and without using an existing number being used". But I don't now exactly what you mean by that.

[si_the_geek]

Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[techgnome]

go into the table design, add a new field called LeadID2 and make it an autonumber... save changes, close the database... backup (copy) the database ... then go back into the table design and delete the leadid field, rename LeadID2 to LeadID ... and then save changes again...

That should do it. I know that when I make similar kinds of changes, behind the scenes that's what SQL Server does... so I can only imagine that Access would work in a similar fashion.
It certainly can't hurt to try... just make sure you have a backup/copy in case something does go south.

-tg

[TysonLPrice]

Make sure the new LeadID has the same values as the old to start since it is a foreign key. You don't want to orphan the children

[techgnome]

ooooooh.... snap... I didn't think about that... good catch... Now I'm not sure how to handle that properly.

-tg

[TysonLPrice]

ooooooh.... snap... I didn't think about that... good catch... Now I'm not sure how to handle that properly.

-tg

Off the top of my head ...speed thinking, which can kill you

Copy off the table, add the new column (not identity), update the new column to the old leadid, delete the contents of the current table, add the new LeadID as identity , set identity insert on, load the current table using the new LeadID in the new identity column from the copied table, set identity insert back off.

That is MS SQL and I would definately test it first.

[smendoza]

I'm thinking in MSSQL but can't you turn on the identity and manually next the next seed?

[gibra]

You have to implement a 'custom counter'.

HOW TO Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1
http://support.microsoft.com/default...b;EN-US;240317

[TysonLPrice]

You have to implement a 'custom counter'.

HOW TO Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1
http://support.microsoft.com/default...b;EN-US;240317

That's pretty much doing what I posted but locking the table for the time it takes to generate the key and then come back and update the record. I think putting the code I posted in a stored procedure would do the same thing. Or using a start and end trans in sequence.

None of that is as clean as taking the time to make the column auto number.