Multiuser on Access database
Tommy2326
workstations that require access to the database and be able to update it
simultaneously. This is the first time I've worked on anything like this and
was wondering if it would work ok, or if there was a special way the database
has to be set up to allow this to happen. I'm using Microsoft Access 2000.
Thanks.
Jeff Boyce
settings. That said, you will need to create a "back-end" (tables/data
only) and keep it on a network server that ALL of your workstations can
connect to. And a "front-end" (all the queries, forms, ...), one copy of
which you will put on each and every workstation.
Search on "split database" for more info.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Tommy2326" <Tomm...@discussions.microsoft.com> wrote in message
news:6954CF18-2323-4C29...@microsoft.com...
Albert D. Kallal
read up on splititng...
here:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
david@epsomdotcomdotau
Access is not a real time network communication service.
It uses local caching with asynchronous write back.
Still, if you have an automated process, you should probably
avoid auto-numbers.
(david)
"Tommy2326" <Tomm...@discussions.microsoft.com> wrote in message
news:6954CF18-2323-4C29...@microsoft.com...
Jeff Boyce
primary key IDs (or their SQL-Server counterpart, Identity columns).
Why do you suggest avoiding auto-numbers?
Regards
Jeff Boyce
Microsoft Office/Access MVP
<david@epsomdotcomdotau> wrote in message
news:ejTBoqH...@TK2MSFTNGP05.phx.gbl...
Tony Toews [MVP]
>"simultaneously" ?
>Still, if you have an automated process, you should probably
>avoid auto-numbers.
Why would using an automated process make a difference in using
autonumbers?
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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
david@epsomdotcomdotau
ask about how to avoid auto-number clashes.
ACC2000: How to Create a Multiuser Custom Counter
http://support.microsoft.com/kb/210194
(david)
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:3dsc93p5u594ogrg9...@4ax.com...
Tony Toews [MVP]
>Because people that use an automated process frequently
>ask about how to avoid auto-number clashes.
>
>ACC2000: How to Create a Multiuser Custom Counter
>http://support.microsoft.com/kb/210194
Ah, gotcha. That problem.
Tommy2326
used for anything in the database, just to help sort data and possibly to see
how many downtime incidents have occurred. Is this going to affect the
multi-user aspect of the database? Is there a possibility that two or more
machines will use the same 'auto number'?
Thanks for the help
Tony Toews [MVP]
>Ive got an auto-number field in my database, 'Record Number', its not really
>used for anything in the database, just to help sort data and possibly to see
>how many downtime incidents have occurred. Is this going to affect the
>multi-user aspect of the database? Is there a possibility that two or more
>machines will use the same 'auto number'?
Access (actually Jet) handles autonumbers very, very well in a multi
user environment. One client had 25 users in all day long adding
likely thousands of records per day into 40 or so tables in a MDB with
160 tables in total. We never, ever had a problem with autonumber
fields.
david@epsomdotcomdotau
take what it thinks is the next autonumber. But it won't
write that back to the database until after it you save the
record.
And any other client won't see the new autonumber until
next time it reads the database.
So you want to minimise the time between when a process
opens a new record, and when it saves the new record.
That amount of time needs to be much smaller than the
time between adding new records, so that there is not
much chance of a clash.
If there is a clash, saving the new record will fail. If that
is likely, you need to be able to re-enter the new record.
But how critical is it to loose a record? Don't spend more
time worrying about it than it is worth.
(david)
"Tommy2326" <Tomm...@discussions.microsoft.com> wrote in message
news:45017B68-661D-4DB8...@microsoft.com...
Tony Toews [MVP]
>When you create a new record, your client process will
>take what it thinks is the next autonumber. But it won't
>write that back to the database until after it you save the
>record.
I respectfully disagree with this and thus the rest of your posting.
Try it yourself. Create an MDB with one table with an autonumber
field and another field such as a text field. Open it from two
instances of Access.
In one instance of Access put any character in that field and do not
tab or enter in the field. You will see the autonumber set to 1.
Notice the triangle in the record selector area on the left hand side
of the screen. It will change to a pencil indicating that "You are
editing this record; changes to the record aren't yet saved." (from
Access 97 help.)
In the other instance of Access against the same MDB also enter a
character in the text field. You will note that the autonumber is now
2. And also that the triangle in the record selector has changed to
a pencil.
The appearance of the 1 and 2 of course assume that the NewValues
property of the autonumber field is set to Increment and not Random.
DAVID
However, the updated autonumber is not written back
to the server until the jet cache interval expires
or you issue a write through or flush instruction.
You will of course see nothing if you use a common
dbengine (global instance) with a common cache,
but I take your point that it was not an issue
with your client.
This can be an issue with automated processes
trying to do 'simultanous' updates. An automated
process should issue a write through or flush
instruction quickly, rather than waiting for the
jet cache interval to expire.
Also, all my tests were done with Jet 3.5. I
assume from the KB article that the situation
has not changed.
(david)
Tony Toews [MVP]
>You are correct that .Addnew updates the autonumber.
>However, the updated autonumber is not written back
>to the server until the jet cache interval expires
>or you issue a write through or flush instruction.
That behavior is possible. However I wouldn't think that Microsoft
would write Jet in such a fashion. Although I sure could be wrong.
>This can be an issue with automated processes
>trying to do 'simultanous' updates. An automated
>process should issue a write through or flush
>instruction quickly, rather than waiting for the
>jet cache interval to expire.
I can appreciate that for the record data themselves but I'm not so
sure about the autonumber value as that is stored elsewhere by Jet.
My copy of the Microsoft Jet Database Engine Programmer's Guide is
remarkably brief on how the autonumber works internally. There's
only a single paragraph stating it's stored on the 'table-header page
of the database file."
>Also, all my tests were done with Jet 3.5. I
>assume from the KB article that the situation
>has not changed.
I don't see anything in the 210194 KB article talking about autonumber
logic and how that all works. So I don't think you can make the
assumption that Jet 3.51 and Jet 4.0 work differently based on that KB
article. IMO MS is illustrating how to solve a different problem.
Tony Toews [MVP]
>You are correct that .Addnew updates the autonumber.
>However, the updated autonumber is not written back
>to the server until the jet cache interval expires
>or you issue a write through or flush instruction.
>
>You will of course see nothing if you use a common
>dbengine (global instance) with a common cache,
>but I take your point that it was not an issue
>with your client.
>
>This can be an issue with automated processes
>trying to do 'simultanous' updates. An automated
>process should issue a write through or flush
>instruction quickly, rather than waiting for the
>jet cache interval to expire.
So I figured what the heck. I'd try to duplicate that problem and I
setup the following code against a table on another computer with
three fields. An autonumber field, an instance field which I changed
for the second copy of the FE MDB and a date/time field.
Sub appendrecords()
Dim rs As DAO.Recordset, i As Long, CountOfLocks3218 As Long,
CountOfLocks3260 As Long
On Error GoTo appendrecords_Error
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
Stop
For i = 1 To 10000
rs.AddNew
rs!Instance = 2
rs!TimeAppended = Now()
rs.Update
DoEvents
Next i
rs.Close
Set rs = Nothing
Debug.Print "Added " & i & " records with " & CountOfLocks3218 & ", "
& CountOfLocks3260 & " locks"
On Error GoTo 0
Exit Sub
appendrecords_Error:
Select Case Err.Number
Case 3218 ' Could not update; currently locked.
DoEvents
CountOfLocks3218 = CountOfLocks3218 + 1
Resume
Case 3260 ' Could not update; currently locked by user 'Admin' on
machine 'LAPTOP4'.
DoEvents
CountOfLocks3260 = CountOfLocks3260 + 1
Resume
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure appendrecords of Module Module1"
End Select
Resume
End Sub
I ran the above code from two different copies of the same FE MDB.
And I couldn't get them to cause a problem. Instead I kept getting
the locking message.
When I was running the same FE MDB in two different instances of
Access I got the 3218 message. When I ran a copy of the FE MDB in the
second instance of Access I got the 3260 lock message.
And I got 7 locks in the time period while the second one was waiting
for the first one to finish. Once I added the DoEvents it took about
seven seconds to complete.
So this code doesn't prove or disprove anything.
Tommy2326
The database I am setting up is to monitor machine downtime, there will be
seven machines entering data into this and one possibly two looking at the
information for reports. The downtime information will be entered through a
form. when the form opens there is 9 fields with data required:
record number - auto number
fault code - entered by user
fault message - displayed automatically, linked to fault code
action - displayed automatically, linked to fault code
date occurred - entered automatically, =date()
time occurred - entered automatically, =time()
shift - entered automatically, different form for each shift
machine number - entered by user
description of problem - entered by user if necessary
Thanks for the help.
Tommy
David W. Fenton
><david@epsomdotcomdotau> wrote:
>
>>When you create a new record, your client process will
>>take what it thinks is the next autonumber. But it won't
>>write that back to the database until after it you save the
>>record.
>
> I respectfully disagree with this and thus the rest of your
> posting.
It is unquesionably WRONG.
The whole point of the seed value is that it is set as a property of
the TableDef and is set as soon as the Autonumber is requested.
Jet 2.5 was the last version of Access that did *not* work that way
(i.e., Access 2).
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton
news:eaXSN2By...@TK2MSFTNGP05.phx.gbl:
> You are correct that .Addnew updates the autonumber.
> However, the updated autonumber is not written back
> to the server until the jet cache interval expires
> or you issue a write through or flush instruction.
But the seed value for the Autonumber field for the TableDef is
updated as soon as a new value is requested, and remains in effect
even if the new record is never, ever committed back to the
underlying table.
You obviously do not understand how Autonumbers work.
David W. Fenton
> I don't think you can make the
> assumption that Jet 3.51 and Jet 4.0 work differently based on
> that KB article.
I think in regard to the multi-user Autonumber issue, it was Jet 3.0
that fixed the old problem in Jet 2.5 and earlier where the table
was locked until the new record with the new Counter field value was
abandoned or saved.
It was in Jet 3.0, I believe, that Jet was given the table-level
seed value for each Autonumber field that was then exposed to
programmatic manipulation in Jet 4.0.
Jet 3.5 does not exhibit the problems alleged by David (the other
one!).
Tony Toews [MVP]
>So, basically my database should work ok on a multi-user platform?
Yes.
David is talking about apps entering many records in a very short
period of time.
>The database I am setting up is to monitor machine downtime, there will be
>seven machines entering data into this and one possibly two looking at the
>information for reports. The downtime information will be entered through a
>form. when the form opens there is 9 fields with data required:
That's trivial and won't be the slightest problem.
David W. Fenton
> David is talking about apps entering many records in a very short
> period of time.
Which "David" would that be? Not me!
Tony Toews [MVP]
>> David is talking about apps entering many records in a very short
>> period of time.
>
>Which "David" would that be? Not me!
The other David. <smile> Which may or may not be Australian David.
News
Why should you avoid "auto-numbers"???
Thank you,
Charles L. Phillips
<david@epsomdotcomdotau> wrote in message
news:ejTBoqH...@TK2MSFTNGP05.phx.gbl...
Jeff Boyce
Access Autonumber data type does a pretty good job of what it is intended to
do, provide a unique row identifier.
Trying to use it to do other things (e.g., provide users a sequential record
ID) is asking for trouble.
Trying to write a novel using a car is asking for trouble, too.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"News" <msnews.microsoft.com> wrote in message
news:OSfjXVJA...@TK2MSFTNGP05.phx.gbl...
lloydsturge
Thanks!
Jeff Boyce wrote:
That's a little like asking "why should I avoid cars?
28-Sep-07
That's a little like asking "why should I avoid cars?"
Access Autonumber data type does a pretty good job of what it is intended to
do, provide a unique row identifier.
Trying to use it to do other
Previous Posts In This Thread:
On Tuesday, July 10, 2007 8:44 AM
Tommy232 wrote:
Multiuser on Access database
I am creating a database that will monitor machine downtime. There are seven
workstations that require access to the database and be able to update it
simultaneously. This is the first time I have w
On Tuesday, July 10, 2007 1:21 PM
Jeff Boyce wrote:
Microsoft Access, out of the box, can handle multi-user without special
Microsoft Access, out of the box, can handle multi-user without special
settings. That said, you will need to create a "back-end" (tables/data
only) and keep it on a network server that ALL of your w
On Tuesday, July 10, 2007 4:42 PM
Albert D. Kallal wrote:
Sonds like a ideal project for ms-access.read up on splititng...
Sonds like a ideal project for ms-access.
read up on splititng...
here:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Cana
On Wednesday, July 11, 2007 4:06 AM
Tommy232 wrote:
Thanks for that guys, think I'm just about ready to try splitting the database.
Thanks for that guys, think I am just about ready to try splitting the database.
On Thursday, July 12, 2007 7:51 AM
<davi wrote:
"simultaneously" ?
"simultaneously" ?
Access is not a real time network communication service.
It uses local caching with asynchronous write back.
Still, if you have an automated process, you should probably
avoid aut
On Thursday, July 12, 2007 11:28 AM
Jeff Boyce wrote:
I've not noticed either a corruption or a performance hit using autonumber
I have not noticed either a corruption or a performance hit using autonumber
primary key IDs (or their SQL-Server counterpart, Identity columns).
Why do you suggest avoiding auto-numbers?
Regards
J
On Thursday, July 12, 2007 2:24 PM
Tony Toews [MVP] wrote:
Re: Multiuser on Access database
Why would using an automated process make a difference in using
autonumbers?
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread o
On Saturday, July 14, 2007 8:26 PM
<davi wrote:
Because people that use an automated process frequentlyask about how to avoid
Because people that use an automated process frequently
ask about how to avoid auto-number clashes.
ACC2000: How to Create a Multiuser Custom Counter
http://support.microsoft.com/kb/210194
(david)
On Sunday, July 15, 2007 3:11 PM
Tony Toews [MVP] wrote:
Re: Multiuser on Access database
Ah, gotcha. That problem.
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 &
On Monday, July 16, 2007 11:18 AM
Tommy232 wrote:
Ive got an auto-number field in my database, 'Record Number', its not really
Ive got an auto-number field in my database, 'Record Number', its not really
used for anything in the database, just to help sort data and possibly to see
how many downtime incidents have occurred. I
On Monday, July 16, 2007 6:44 PM
Tony Toews [MVP] wrote:
Re: Multiuser on Access database
Access (actually Jet) handles autonumbers very, very well in a multi
user environment. One client had 25 users in all day long adding
likely thousands of records per day into 40 or so tables in a MD
On Monday, July 16, 2007 7:32 PM
<davi wrote:
When you create a new record, your client process willtake what it thinks is
When you create a new record, your client process will
take what it thinks is the next autonumber. But it will not
write that back to the database until after it you save the
record.
And any other cli
On Monday, July 16, 2007 8:58 PM
Tony Toews [MVP] wrote:
Re: Multiuser on Access database
I respectfully disagree with this and thus the rest of your posting.
Try it yourself. Create an MDB with one table with an autonumber
field and another field such as a text field. Open it from two
On Monday, July 16, 2007 10:52 PM
DAVID wrote:
You are correct that .Addnew updates the autonumber.
You are correct that .Addnew updates the autonumber.
However, the updated autonumber is not written back
to the server until the jet cache interval expires
or you issue a write through or flush instru
On Tuesday, July 17, 2007 12:45 AM
Tony Toews [MVP] wrote:
Re: Multiuser on Access database
That behavior is possible. However I would not think that Microsoft
would write Jet in such a fashion. Although I sure could be wrong.
I can appreciate that for the record data themselves but I a
On Tuesday, July 17, 2007 1:03 AM
Tony Toews [MVP] wrote:
Re: Multiuser on Access database
So I figured what the heck. I'd try to duplicate that problem and I
setup the following code against a table on another computer with
three fields. An autonumber field, an instance field which I cha
On Tuesday, July 17, 2007 4:38 AM
Tommy232 wrote:
So, basically my database should work ok on a multi-user platform?
So, basically my database should work ok on a multi-user platform?
The database I am setting up is to monitor machine downtime, there will be
seven machines entering data into this and one possibly
On Tuesday, July 17, 2007 7:28 AM
David W. Fenton wrote:
Re: Multiuser on Access database
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in
It is unquesionably WRONG.
The whole point of the seed value is that it is set as a property of
the TableDef and is set as soon as the Autonumbe
On Tuesday, July 17, 2007 7:29 AM
David W. Fenton wrote:
Re: Multiuser on Access database
DAVID <da...@nospam.nspam> wrote in
But the seed value for the Autonumber field for the TableDef is
updated as soon as a new value is requested, and remains in effect
even if the new record is never
On Tuesday, July 17, 2007 7:32 AM
David W. Fenton wrote:
Re: Multiuser on Access database
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in
I think in regard to the multi-user Autonumber issue, it was Jet 3.0
that fixed the old problem in Jet 2.5 and earlier where the table
was locked
On Tuesday, July 17, 2007 3:56 PM
Tony Toews [MVP] wrote:
Re: Multiuser on Access database
Yes.
David is talking about apps entering many records in a very short
period of time.
That's trivial and will not be the slightest problem.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond
On Thursday, July 19, 2007 8:15 AM
David W. Fenton wrote:
Re: Multiuser on Access database
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in
Which "David" would that be? Not me!
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton
On Wednesday, September 26, 2007 7:31 PM
News wrote:
Hello,Why should you avoid "auto-numbers"???
Hello,
Why should you avoid "auto-numbers"???
Thank you,
Charles L. Phillips
it
this
On Friday, September 28, 2007 12:49 PM
Jeff Boyce wrote:
That's a little like asking "why should I avoid cars?
That's a little like asking "why should I avoid cars?"
Access Autonumber data type does a pretty good job of what it is intended to
do, provide a unique row identifier.
Trying to use it to do other
EggHeadCafe - Software Developer Portal of Choice
Generate Server Side Dropdown Menues
http://www.eggheadcafe.com/tutorials/aspnet/21393e18-af39-4df3-938b-248c4cb18755/generate-server-side-drop.aspx
Tony Toews [MVP]
>I have a custom counter in my application that has to be redesigned now that I have replicated the backend database for use with a laptop that will be taken on the road. The solution was to use direct synchronization. Which should work great. The problem is the custom counter is a variation of the DMAX() solution will cause duplicate counter numbers to be generated in the replicas. I'm looking for advice or alternative code that I can use/modify to work for my solution. This problem has to be fairly common for replica databases but I can't seem to find much on it on the net. So I'm asking the Access gurus out there what would be their solution? If anybody could point me in the right direction I'd appreciate it!
You'll need to read the work station name, there's an API call at
www.mvps.org/access to determine that, and then figure out some kind
of custom number system that suits. You can put the workstation
names in a table in the replicated database as you encounter them when
the app starts and set a flag indicating if they have a custom
numbering system. As well as a field with the custom number. That
is that workstation now starts with 8000000 or something like that.
Another laptop on the road might start with 8100000.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/