Update, AddNew

[AHopper]

I have two tables "Batch" and "OneTwoPackage" that
information is entered into by different users. When all
information is entered in "Batch" and "OneTwoPackage" and
they are in balance, on a form I have a command
button "Apply". In the "OnClick" event is a procedure that
takes information from several fields in each record from
the "Batch" table and updates records(SQL statement)
and/or adds new records to the "OneTwoPackage" table. The
procedure worked well when I had it only on my
workstation. Since I have put it in the multiuser
environment I believe the procedure is causing
the "OneTwoPackage" table to be corrupted. (Records
including autonumber with #Error in them and message "Not
a valid bookmark".) I am having to restore the database in
order to correct these. I believe it happens because users
are making entries into the "OneTwoPackage" table at the
same time as the procedure is trying to updating or adding
new records to the "OneTwoPackage" table.

Presently I have 7 workstations running Access Run time.
The other workstations are making entries into other
tables in the database.
Since I have not encountered a problem like this before I
am not certain if I have the right cause and I do not know
how to avoid the problem.

Questions:

Is there a way to know if someone is using the forms that
enter information into these two tables?

Can I lock the two tables until the procedure is finished?


Can I send users a message to let them know when the
procedure is running and when it is finished?

Do I need to lock the whole database? If so how do I do
that?
(Will running a procedure like this cause problems with
other parts of the database?)

At what point (how many users) and what database size will
I need to consider Upsizing?

As the database continues to grow and we add more users I
am increasing aware of my need to grow in my understanding
of how to design for these situations. I am open to
suggestions and I definately need help. Due to space I am
only including some of the code from the OnClick event of
the "Apply" button (see end of posting). If more detail is
required I will gladly supply it.

Thank you very much for your help.
Allan

'Update an existing record.'
DoCmd.SetWarnings False
SQL = "UPDATE OneTwoPackage SET OneTwoPackage.Applied = -
1,OneTwoPackage.Machine = " & NewMachine
& ",OneTwoPackage.UnitNumber = " & Unit
& ",OneTwoPackage.RollNumber = " & Roll
& ",OneTwoPackage.PartNumber = " & Part
& ",OneTwoPackage.Carton = " & NewCarton & " " & "WHERE
((OneTwoPackage.UsedWithJob)=" & Me.UsedWithJob & "And
(OneTwoPackage.BatchNumber)=" & Me.BatchNumber & "And
(OneTwoPackage.PackEntryNumber)=" & PackEntry & ");"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True


'Add a new record and update the existing record.'

Set CurDb = CurrentDb
Set NewBatchRecord = CurDb.OpenRecordset
("OneTwoPackage")
With NewBatchRecord
.AddNew
!JobNumber = NewJobNumber
!UsedWithJob = NewUsedWithJob
!PackagingTypeID = NewPackagingTypeID
!BatchNumber = NewBatchNumber
!PackerNumber = NewPackerNumber
!PackDate = NewPackDate
!PackLastSaved = Now()
!Machine = NewMachine
!UnitNumber = Unit
!RollNumber = Roll
!PartNumber = Part
!UniqueLabel = NewUnique
!QtyPacked = NewQtyPacked
!Applied = True
!Surplus = True
.Update
End With
NewBatchRecord.Close
Set NewBatchRecord = Nothing
DoCmd.SetWarnings False
SQL = "UPDATE OneTwoPackage SET OneTwoPackage.FullUnits
= " & UnitDifference & ",OneTwoPackage.QtyPacked = " &
QtyPackedDifference & " " & "WHERE
((OneTwoPackage.UsedWithJob)=" & Me.UsedWithJob & "And
(OneTwoPackage.BatchNumber)=" & Me.BatchNumber & "And
(OneTwoPackage.PackEntryNumber)=" & PackEntry & ");"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

 

[TC]

Access (really, Jet) is a >multi-user< database. It does not get corrupted
just because you have several people updating the same table (or record) at
the same time. It contains features that specifically detect these
situations & handle them in a controlled & predictable manner.

It is probable that there are other causes of your db getting corrupted. For
example, have you split the database into a so-called front-end/back-end
structure? Does each user have their own copy of the front-end?

If you answered no to either of those questions (or don't understand what
they mean), there's your #1 probable cause.

HTH,
TC

 

[AHopper]

The database is split and all users have copies of the Mde
front-ends on their workstations. (To update the frontends
I use Tony Toews Auto FE Updater)
So far in my research I have followed through on the
following suggestions:
1) turned off all cache on the workstations and network
2) added a form that is linked to the back end which
opens invisibly when the database opens and remains open
until the database is closed.

Thank you for your support and I appreciate any help to
bring stability to my database.

Allan

 

[TC]

Ok, it sounds like you've covered the front-end/back-end bases properly!

Here's what Bruce M. Thompson said about the "Not a valid bookmark" problem,
recently:

<quote>
Make sure that your Access installation on each workstation is up to date on
the patches and service packs and the same goes for "Jet", the actual
database
engine. See the following page at Tony Toews' web site for the latest info
on
these:
http://www.granite.ab.ca/access/msfixes.htm

See, also, Tony Toews' page on database corruption at
http://www.granite.ab.ca/access/corruptmdbs.htm.
</quote>

I've also seen some other suggestions that turning off anti-virus software
(or removing .ndb & .mde files from the scan) might help.

Maybe those suggestions will help.

PS. Allan, could you please include the text of all previous messages, when
you reply. Some of us answer quite a few questions, & it is difficult to
remember what we are discussing, unless the whole previous communication is
retained within the text of each new post.

Cheers,
TC