• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

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!SldNumber = TxtRef.Text

     set rsSld = Nothing

     Exit Sub


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

End Sub
2 Solutions
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.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now