Using Random Number as Primary Key - need help!!

[Gambler089]

I have come to this project a little after the fact so I will explain as best I can the problem.

We are using SQL 7.0 as backend to Access 97. The Db is replicated to another site, both sites can enter data. Had problems with autonumber if 2 people entered data at the same time. The solution was apparently to assign a random long integer as the primary key. (I can't find any code that does this.)

The problem seems to be the number changes at some point in the process of saving the record???

Working from enterprise manager I have entered a new record and move to the next new record (no number assiged yet), requery the table it shows a number assigned to the record. Close the db and reopen, the number has been changed???

Anybody have any ideas? I am totaly lost on this one!

Thanks in advance, Gambler089

 

[ramani]

The server database shall be having the trigger to generate the randum number at the time of appending the record. So that must be the reason for it to get changed. ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[MichaelRed]

Well, I do not have a CLUE to the exact situation you are currently having. I have some background on the duplication of autonumber in the multiuser db, and can refer you to a faq here in tek-tips. faq700-184 for some explination and a different soloution (or approach).

Further, I would advise aginst the random number approach. As the table grows, the probability of duplication also increases. Your system MUST be checking for - and 'fixing' any duplication. This process will become increasingly time consuming as the record count increases.

I would either switch to a GUID or some variation of the routine in the faq.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Gambler089]

Thanks guys;

I will check out the suggestions in the FAQ on monday morning.

Gambler089