Multi-User Access

[calvinle]

Hi,

I will need to move my old excel to an access because the excel keeps bugging in Shared Mode. The user will have access directly to the table in the Access file accdb.

My question is which way is faster:
Let all my user access to that accdb in the LAN?
Create a frontend that link the backend table so they all access to front end?
Update the table data via fromt end that link the backend table thru form?

Thanks

 

[CJ_London]

not quite clear what you are saying the options are, but there is only one you should consider.

1. All data in tables in a backend located on the network.

2. All users have their own front end located on their local drive and linked to the backend.

3. User can only access data via forms and reports

Be aware if you have a slow network, the application will be slow if poorly designed.

Don't have forms/reports with a table recordsource and filter the forms
Do only bring through the data required (think how web pages work) - keep short and narrow

 

[calvinle]

Yes thats the issue. The LAN is quite slow sometimes, so I dont want to take chances.
I have made a test with another user at work going to the accdb in the LAN at same time and both us modifying data at once. Its quite slow sometimes.

1/ If I program to audit track all activity on the table, then why cant I let user go thru backend table directly on the LAN?

2/ Why do we have to edit data thru Form front end only? If i trust my user+with that audit trail track, I should be able to let them use the backend directly no?

3/ Lets say I have a backend, and front end as you suggest, should I add password to the backend? Does it takes more time for the front end to access the table in backend with a password?

4/ What do you mean by "Dont have form with a table recordsource and filter the form?"-> What can it cause?

5/ How do you "Do only bring data thru the data required..."? If I want to edit a certain record, I need to load all record first in a subform, then let user edit the record then save that record back to the table isnt it?

Thanks

 

[gemma-the-husky]

"slow at times" in a multi-user database is probably due to a (lack of) a persistent connection, rather than a slow LAN.

there should be no noticeable deterioration in speed in a multi-user environment.


1 and 2. you shouldn't even trust yourself with a backend. The idea of interacting via forms, is that forms can be designed to prevent users inadvertently doing "wrong" things, and help maintain data integrity. It's much harder/impossible to do that directly in a table. So other than looking at tables for problem solving, the right way is not to interact directly with a backend.

3. you can do - it won't affect the speed. however the password is actually exposed within the front end. If you are happy to let users access the backend directly, then the password is moot anyway.

4. you are better with a query than a table for a form record source, as it is easier to pre-sort a query, and a query can be pre-filtered. The default order for a table is unlikely to be what you want. There is no practical difference though. A query and table are both read-write.

5. A query can pre-select certain rows and columns.

Hope this helps

 

[calvinle]

About the point 4:
With my experience in Access, I always use recordsource because my data needs to be edit. But you mention even in query we can edit the data?
I have found on microsoft the following statement:

If you use a query as the record source, you might not be able to edit the data. Before you use a query as your record source, you should consider whether you need to edit data.

5. My concern is on a slow LAN, if a usrler make update a on field, then another one make another changes on the same record but different field, the data will be save under the latest record saved.
Example:
User A edit field 1 on record 1.
User B edit field 2 on record 2 at the same time as user A.
Then:
User A saved the field and off record.
User B saved and be prompt to overrite the data, if user B click Yes, then the field 1 that was modified by user will be override back to its original states. So we are losing data of the user A.

 

[Minty]

5. If you set record level locking user 2 wouldn't be able to make any changes until user 1 has released the record.

4. If the query is only filtering and sorting the tabe data you will still be able to edit the data.

 

[calvinle]

For the point 5: I have tested with the user beside me. And they are still able to edit the record, only after the user A leave the record.
The User A goes in a record and edit it.
The User B goes in a record but dont do anything.
The User A changes the record in field 1.
He saved. But since the User B is already in that record with the old data in field 1, then if he changed the field 2, the field 1 will restore back to the User 2 data.

 

[Minty]

Yes but they'll get a warning that the data has changed, so should drop their changes, refresh the record and then make the changes.

The only other way of dealing with this is to prevent locked records being loaded, but this is not straight forward.

Maybe you should take a step back and look at the process if the same people are always trying to update the same information? Surely you had the same issue with the spreadsheet - except it was for the whole spreadsheet not just the one record?

 

[The_Doc_Man]

To approach this problem, I add this wrinkle. I create a DAO Database object and at the beginning of the code, do a Set DAODB = CurrentDB

Then I use DATDB.Execute for an SQL statement (which should be built as a string first, then just feed the string to the Execute). Use the dbFailOnError option, which will cause an automatic rollback, because this method treats the SQL as being inside a protected transaction. So your changes that result in two-user interference won't leave the database in a bad state AND you'll be able to give your users a chance to retry the operation because you will know that something failed. (Or at least, if you have a trap handler in the module where this query is run, you'll know something went belly-up).

I have also found that if you use a direct table-link and you didn't do the linkage quite right, you get questions about how to find table X - but if you use a query, it somehow remembers where the table is located and won't ask. Admittedly, this might have been due to an error in configuration on my part, but my solution was to use queries and I got that solution from this forum years ago. Since then, I've had no trouble on a database that has been in production since 2011.

BTW, this is SUPPLEMENTAL. Minty, Dave, and CJ are giving you good advice that you should also consider.

 

[gemma-the-husky]

really, I would not worry about the possibility of simultaneous updates until it happens. It will happen only rarely in practice, if ever.

IMO it's much better to use the default optimistic locking, rather than a specific pessimistic locking strategy, until you decide you need the latter. The latter is really a high-end requirement, not a standard requirement

with regard to updating queries - certain query types are read-only. (summary queries, union queries). Most normal queries are editable.

 

[calvinle]

The reason I want to prevent its because I has issue before and multi user add simple data to a database. It end up causing error and all the data were replaced by #error. So I lost a lot of data.
I dont want to take anymore chances.