How to now which user is using database!

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

HI!!

I've recently been having problems my database.
I've got a DB with two users on it.
My DB is not split, because it just slows down the
application to a near alt. So I've got the two users
pointing to the same DB on a shared drive.

Now my problem:
One the 2 users on entering data on the same form at
almost the same time. Each time they select my new button,
a number is generaded(via VBA code) and asign to the order
there about to fill with data. Now my other user might
enter a new record as well and the code so far prevents
the other user from using an newly created number by the
other user and vice-versa.

Today one of them tried to fill-in the blanks of one newly
created order and the DB let the user to-it. after leaving
and returning to that same order number they notice that
the data was corrupted due to the fact that one of the
users was working(at the same time) on the other users
newly created order number. Can I prevent that from ever
happening again ether programmaticaly or using the FE and
BE approach..

Any help will be appreciated, I realy want to know how to
do this the right way...

thanx in advance for all your help and explainations!!
Patrick
 
Patrick said:
My DB is not split, because it just slows down the
application to a near alt.
Click to expand...

See the Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm. These will almost
always solve your performance problems.
One the 2 users on entering data on the same form at
almost the same time. Each time they select my new button,
a number is generaded(via VBA code) and asign to the order
there about to fill with data. Now my other user might
enter a new record as well and the code so far prevents
the other user from using an newly created number by the
other user and vice-versa.
Click to expand...

How does your code generate the number? Do you store the next
available number in a table somewhere? Please post your code.

BTW this problem likely has nothing to do with your MDB being split or
not.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Without knowing how you go about he numbver allocation its a bit difficult
to comment.
but off hand, i'd say, instead of "allocating" a number, create a record
instead, and issue that unique number, then when the updates occur, each
user will be using a unique number.
 
Thanx for replying, here's how I alocate a new Id number.
Set dbs = CurrentDb
CurRec = Me.CurrentRecord

Set rsnew = dbs.OpenRecordset("SELECT max(ID) " _
& " FROM tableName")


cpt = rsnew.Fields(0)

' new record.
cpt = cpt + 1
CompterToErase = cpt

'return value back to form as a new form with blank
fields and ID filled with 'cpt'.

ID.Value = cpt
[PI Type].SetFocus

flagRepasser = False ' code used in another section.

Me.Refresh
Me.Repaint

This part seams to work fine, the other user will never
have access to this number, BUT lets say user1 just
created a new ID number, User2 sees this(on is side or on
is machine) has an empty form with the ID number showing
on the form, every other field on this form is empty. The
kicker is that if user2 decides to enter data on this
empty form and that user1 is also on this form(finishing
entering data) user2 has no warning not to play on this
form. And I end-up having a corupted record because of
that. Is there anyway to to warn user2 (programmaticaly)
to not enter data on this form because another user is
presently using-it.

That's my problem, I hope this will give you a better
understanding of whats not working in my DB. I wasn't sure
if it was clear the first time around.

thanx again for any help and clarifications you can give
me!!
 
Patrick said:
Thanx for replying, here's how I alocate a new Id number.
Set dbs = CurrentDb
CurRec = Me.CurrentRecord

Set rsnew = dbs.OpenRecordset("SELECT max(ID) " _
& " FROM tableName")


cpt = rsnew.Fields(0)

' new record.
cpt = cpt + 1
CompterToErase = cpt

'return value back to form as a new form with blank
fields and ID filled with 'cpt'.

ID.Value = cpt
Click to expand...

The above line actually doesn't do anything useful because at no time
are you saving the record. In fact you are updating the current
record with a new value.

So I don't even know how this would work in the first place. There's
probably some code somewhere else.

I'd rework all the above code to do something like
- query to select all records in descending order
- movefirst to grab the first record ID
- add one to the ID
- add an empty record to the table with the new ID
rsnew.addnew
rsnew!ID = cpt
rsnew.update
- then go to that record in the form

Thus you are adding a new record with the next ID absolutely as fast
as possible so as there will be a minimum chance of conflicting with
another user.

What I actually do in a similar situation is track the ID in a one
record table which I read and update. Then I add the record in the
main table.

And finally any problems with the existing code have nothing to do
with the MDB being split or not.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Thanx again for your help, I will put your ideas to good
use and post any other problems(if I encounter any).
 
Back
Top