multi access for access database question 1

[Epsilon101]

Hi i would like to ask if there is a limit to how many people can access an Access database mdb file at the same time and make saves and changes. I know it keeps a log of the order in which things are done in the database, but when does it start to slow down if at all from too many user.

---------------------------------------
Thanks for any help!

 

[LittleSmudge]

That all depends on many issues becuase that are things that you can do which will either speed up - or slow down - the multi user experience.

I've had 15 users on an A97 database set up in single .mdb format all doing data entry and data edit.
Speed of access was not a problem but they used to get record clash problems about twice a day.


A few things you can do to speed things up is:-

1) Split the db FrontEnd and BackEnd
2) Distribute FrontEnds to users so that each user is using their own FrontEnd
3) Connect FrontEnd to DataSource via ADO, gather data in a recordset and populate unbound forms from the recordset then when user has finished editing ( or entering new ) write the data to the BackEnd using ADO recordset again.

That way you keep most of the work in the FrontEnds and only hit the BackEnd when necessary.


How many users are you thinking about?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Epsilon101]

Its only a small input database for keeping a log and printing off a monthly report, there is probably going to be about 50 people or more able to access it but not that many using it at one time, hopefully they will close it before hand, i cant do a front end on each computer unfortunately, it is in a server drive. I can split them in the drive if that will improve it at all. I also dont know much about ADO, at the moment im just doing the basics on Access at the moment, if you have a little info on this so i can understand how to code it correctly that would be helpful.

---------------------------------------
Thanks for any help!

 

[Epsilon101]

Can the 3rd stage using recordsets still be done with the one front end in the same drive?

---------------------------------------
Thanks for any help!

 

[LittleSmudge]

Number of users with the database OPEN is not really an issue - it's more to do with disk saves because access needs to make sure that a record is not being edited by more than one user at a time.

It does this by 'record locking', howevr access is a bit myopic and it cannot focus on just one record if they are 'small records' so it locks 2kByte pages.

If users are editing records that are inside hat 2k boundary then they are locked even though they are not editing the same record!
This is even more frustrating when you realise that adjacent records on the disk are not necessarily adjacent in the table ( and visa verse ).



So 50 people making how many record entries per day ?

Will there be any peak periods ?
Ie. does everyone make an entry just before lunch and just before CoB ?

How long does an entry take to make ?



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

You can do step one and step 3 leaving the FrontEnd on the server
but doing step 3 on its own just increases processor load without saving anything.

Why can't you do step one - are all these users really using the server as their local machine ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Epsilon101]

The first stage was a case of me thinking that i might not be allowed to waste the time getting these people to add the front end into their documents folder. Will it work if i put the front end in the drive and then have everyone make a copy of it and put it in their documents folder. The input page has about 22 fields to enter data that then goes into a main table, 3 of these are combo boxes linked to separate tables so they can select an option, 2 are check boxes, 1 is an auto number. I also have a default date and time that can then be altered if necessary. Some of the fields are required so i did some code to stop them from being able to save or move between records if the required fields are empty. The only security risk with that is the mouse wheel, i havent done anything to fix that.

The log doesnt have any peak times, which is lucky, its completely random on how many entries need to be made at one time, but i think on average i wont see more than 10 people in it at one time.

Also what do you mean by adjacent records on the disk may not be adjacent in the table? I can guess at what you mean but im not sure

---------------------------------------
Thanks for any help!

 

[LittleSmudge]

If you create a FrontEnd database and then link to it MAKE SURE that you use absolute referencing.
Ie. You refer to the BackEnd database by \\server\path\folder\file.mdb and not by
C:\path\folder\file.mdb

This is because some of your users might not have the save letter mapped to that drive - and that causes lots of problems!

The easiest way to achieve this is :- When you go to do the link you will get a file open type dialog box. avoid the easy letter:\path and ALWAYS use MyNetworkPlaces and navigate via there to the required file.

Once you've done that you can then get each user to copy that file to their C:\ drive and it will work fine.


As for adjacent records on the disk may not be adjacent in the table

In the old A97 db I mentioed before I'd get userA editing record number 456 and then find userB was locked out of saving record 7324.
They were saved in the same 2kByte page of the hard disk memory space but were not 'consecutive' in the sense of their record numbers.
My users had a hard time getting their heads round this one. If it had been records 456 and 457 then they would have understood.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Epsilon101]

Thanks for the info on the suggestions ill see what i can sort out

---------------------------------------
Thanks for any help!

 

[Epsilon101]

A question from me is we have over 10 servers each user logs on to 1 of 3 of these to their desktop and thats random, you can access our main drive through any of these servers and i have added the mdb back end file into this drive so will that cause any errors when people are using the front end from their documents, baring in mind all users documents are on another drive and the users use terminals not pc's.

---------------------------------------
Thanks for any help!

 

[LittleSmudge]

Ohhh - right so now I understand

It's a DT100 issue.

Okay - So I'd put the BackEnd on one server.
Put the front end copies on each of the servers and make sure that each of the front ends can link to the BE

THEN the users can use the FE that is on the server that they happen to log onto.

It shares the FE work load over the 3 servers and keeps the BE in one location.

Will that fly with your setup ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Epsilon101]

So far my testing is fine, i do need to ask though i still dont fully understand what you mean when the db locks the records. Does it lock any that are being edited and are under 2k in size ?? or does it group all the records being edited and looks to see if they are under 2kb? Or is this completely off the mark. Also could you explain a page in the hard disk memory space, i honestly have not heard this expression before. I could do with understanding this so i can work at making sure no record errors occur by using your advice on recordsets, which is also somethin i dont fully understand how to do. I know recordsets is basically an array of data from records in a table, but this bit i dont get properly:

"Connect FrontEnd to DataSource via ADO, gather data in a recordset and populate unbound forms from the recordset then when user has finished editing ( or entering new ) write the data to the BackEnd using ADO recordset again"

The only experience of using Access so far is from this great forum and the help in Access i am trying to learn as fast as possible though.

---------------------------------------
Thanks for any help!

 

[LittleSmudge]

Okay,

The disk issue is a matter of how Access ( Windoz ) does disk storage of data.

( now I'm not a hardware expert when it comes to this sort of thing so what follows is how I understand it from an MSAccess user point of view )

Hard disk memory is divided into 2kByte 'pages'
If you have small records you can get many records within one 2k boundary
Only vary rarely do you get records that are large enough so fill a 2k block all by themselves.

Therefore, if you are editing record 456 and that happens to be stored in the same 2k block of memory as record 7324 then you have locked BOTH records.
Access doesn't care about sort order at the hardware level ( thats why Indexes are important for sorting and finding etc )
So you cannot assume that records 456 and 457 will be in the same block regardless of how small they are.


As for the Recordset stuff - what version of access are you running ?



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Epsilon101]

I am using Access 2002, so it locks all records in a 2k block when they are being edited, what happens if 1 is above 2k, does that ever happen?

Could you possibly give a simple explanation on indexes as well, because i have it on fields when its required, stuff like unique ID's, but ive never made use of it.

Thanks for your time

---------------------------------------
Thanks for any help!

 

[LittleSmudge]

If a record is greater than 2k bytes ( oh and man you've got to be pushing it to get above 2k in a record ) then it takes 2 pages to store that one record.

Access starts storing records at the begining of a 2k boundary.
If the next record will fit within the same 2k page then in it goes -
else it is carried over and the new record begins at the start of the next 2k boundary ( NOT memory efficient - but nothing in Windoz is ).

So in theory if your records are over ONE kByte in size there will only ever be one record per page and no locking problems.
However, few records ever get that large.


Don't worry about indexes.
Look them up in the Help files for some background reading - they are useful but not vital for what you're doing now.

as for recordsets.

Think of a recordset as the results of a query held in RAM
so you can combine tables
select specified fields
select specified records

Then you can look at single records at a time , read them, edit them etc and then save them back to the underlying table

So what you do is have a form with all the necessary controls on them - all unbound and with the form unbound too.
Unboud Control means controlsource is blank
Unbound Form means the Recordsource is blank

Then open the form
Get user to populate controls on the form.
Then on a 'SaveData' command
Open a recordset on the target table
use the recordset.AddNew method to make a new record in the recordset
Populate the recordset on control at a time from the form
use the recordset.Update method to save the data to the table





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Epsilon101]

Thanks good explanation that, i now understand a lot more, its like a 2d scroller and ive just opened up a gate in my way .

---------------------------------------
Thanks for any help!

 

[Epsilon101]

Hi LittleSmudge, just wanted to ask you, with the servers my dbase is on at the moment being DT100 as you called it, i never used an .mdw file to begin with because you have to reference the path name in the shortcut, each users access.exe is kept in their microsoft programs folder in their documents and settings which has their username on it, so each path is different...
Is it possible to use a .mdw in this situation, i havent done so this time, but i would do so again. Only reason i didnt now is that none of the users know Access and its a temporary dbase, in the future i wont be as lucky.

---------------------------------------

Neil

 

[LittleSmudge]

As long as the .mdw file can be located somewhere on the Network then everyone can use the same copy of the .mdw file.

Make sure that you refer to the .mdw file in the
\\Server\Path\FileName.mdw format

( rather than G:\Path\FileName.mdw )

and then all of the users should be able to see it wherever they log in.

Alternativly:-
You might need to keep a central copy of the file and then distribute it to each server every time it is updated - but thats a nasty ( error prone ) last resort.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Epsilon101]

What can be done about referencing the Access.exe, thats not the same as just making a new .mdw and moving the .mdw somewhere where everyone can get access to it.

---------------------------------------

Neil

 

[LittleSmudge]

Back in the olde days of Windoz 3.1 ( and even the the DOS dayzzzz ) you could specify a set of folders in the Win.ini file as default folders .
If you then called on those .exe files without speifying the folder, then the system would search all of those defailt folders in the order specified in the Win.ini .

I believe that there is a similar thing in current systems
Something like root%20% something

You need to make sure the Programme Files folder is defined as a default folder - that should work

- But why in all that is sensible has someone chosen to put APPLICATIONS inside someone's DOCUMENT settings folder ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.