Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Are 2 tables with one autonumber possible?

Status
Not open for further replies.

nunan

Technical User
Feb 11, 2004
41
GB
Hi

I have 2 tables, each have an autonumber as the primary key.

Different data will be entered into each table by different users and this all works fine.
But
On occassion, I will want to append the data for a record from tabel 1 into table 2. Currently this will cause a problem as it will create duplicate entries in the primary key.
Is there a way that the autonumber can be linked somehow so that when a new record is created in table 1, it will ensure that that number cannot be used in table 2?

For example:
If table 1 has 3 records, then the next record created in table 2 will have the autonumber 4, then if another record is created in table 1, it will have autonumber 5?

Thanks in advance for any help x
 
you would have to have a table that stores the next number to be used and not use the automatic "autonumber" but always create your own based on the number stored in the table. However that has it's own issues. What if one person is trying to put a record in Potentials and someone else is trying to put a record in Existing at the same time. The program goes and finds that the next number to use for both people is 4.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi
 
Thanks very much, I may try that, will think it over.
 
You can use your 'homegrown' autonumber, and avoid any issue with giving the same number out by simply using a procedure that locks the table pessimistically and denies read. I'm not sure in ADO the exact options, in DAO you open the recordset using the below syntax:
Code:
Set rs = db.OpenRecordset("tbllastnum", dbOpenDynaset, dbDenyRead, dbPessimistic)
Then when the other user tries to get that record, this same code throws an error (which you of course trap and do a wait for a few ticks and try again), and then you're guaranteed no contention.
--Jim
 
there is a faq on hte Autonumner which encapsulatesd the locking search the faqs for "Autonumber"




MichaelRed


 
Hi

I think the autonumber coming from a table will work for me but I have searched the forum and cannot find any guidance on how to do this, could someone point me in the right direction please?

Thanks x
 
nunan,
For pseudocode, create a function that returns a long.

Use the OpenRecordset line in my previous post to open the 'tblLastNumber' (or whatever you name it). This table can optionally have several records where the key in each record is the target table or which will recieve the autonumber. Ie, record 1 is 'Customer', record 2 is 'Order', etc. Then pass that ID to the fucntion so you grab the correct number.

(Optionally, if you have many autonumbers and a busy system, with a JET dbengine you'll want to place numberous 'dummy' records in-between each true record, to fill up the 2k pages so only one record in that table is locked at a time.)

Anyway, have the error handler trap the 'locked' error (I forgot which it is but that's easy to find). Pick a wait interval, maybe a second, maybe use the GetTickCount API and count ticks, whatever. Then Resume and try to edit the recordset again. Give it maybe 3 trys then bail returning zero, and notify the user that he must wait and try later. In reality, that scenario should be rare, unless it's a very heavy transactional system.

Obviously you increment the number and return it. The tblLastNum always contains the last number used. I guess you could make it the 'next' number, whatever.
--Jim
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top