Obtain Exclusive Use Of An Access Database Table From VB6

I am using VB6/Sp6 and running under Windows XP Pro.

I have a Tabled Named SLDNUMBER which has only one record and One Entity also named SldNumber. I am executing my application in a Multi-User environment and I need to allocate a unique number to a record in another table using SldNumber. But despite my code below I am generating a Duplicate Number. I hope you can help me.

Option Explicit

Dim rsSld as DAO.Recordset

Load Event

Call Get NextSld

Private Sub GetNextSld()

    On Error GoTo LocalError

     Set rsSld = DbSupmCon.OpenRecordset("SLDNUMBER".dbOpenTable,    dbDenyWrite+dbDenyRead)

     TxtRef.Text = rsSld!SldNumber + 1  (I now have the next available number)
     
     rsSld.Edit
     rsSld!SldNumber = TxtRef.Text
     rsSld.Update

     rsSld.Close
     set rsSld = Nothing

     Exit Sub

  LocalError

     I display an error message here to trap an error if any, but nothing is reported.

End Sub
 
NolancAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nirojexpertCommented:
this depends on which database you are using.
if you are using access, then AutoNumber is the only way. If oracle it iscalled sequence.
for access, you addNew, then update and then read the value of autoNUmber

for oracle, you
create sequence myseq start with1 increment by 1
and to get the value of seq, select myseq.nextVal from dual.
For other db, look for the ideal way.

select filed+1 will never work out well enough in multi user environment.
0
NolancAuthor Commented:
Thanks for your comment. I am using an Access DB. I have never used Autonumber for any reason before. I will have to research it and come back to you. So at the moment I must conclude that I don't understand. A few lines of code would be a huge help.

Regards
Nolan.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.