Frontend / Backend Question

[NewShoes]

Hey all,

I have created a frontend / backend Acces database. The backend holds all the tables and sits on a network drive that all users have read/write access to. Each user has their own FE (not a shortcut to a FE).

My question is regarding the best setup to prevent record locks and corruption in a system like this?

Many thanks in advance,
NS

 

[Alisa]

I think thats about the best you can do. Just make sure you have a frequent backup routine.

 

[NewShoes]

Thanks for the reply Alisa.

Out of interest, what is the accepted max number of people for using a FE/BE system? I read that linked tables dont respond well to more than approx 8 users...is this true?

 

[Alisa]

Thanks for the reply Alisa.

Out of interest, what is the accepted max number of people for using a FE/BE system? I read that linked tables dont respond well to more than approx 8 users...is this true?

I think that the official limit is something like 200, but I have never heard of a system that was functional with that many connections. I don't think there is any set answer, it really depends on the system - if everyone is working on one table, that is really going to lower the number of users you can have in there at once. The only way to know for sure is to try it out and see what happens.

 

[georgedwilkinson]

I have a currently working system with an Access front end and SQL Server backend that runs in excess of 50 concurrent users.

At one point, I had a similar system that had hundreds (maybe even thousands) of concurrent users.

I've heard of people running an Access FE/BE with up to 80 users.

If your database is properly normalized, linked tables will work a little better and your system will support more users.

There are many variables involved.

 

[BobMcClellan]

If your database is properly normalized, linked tables will work a little better and your system will support more users.
There are many variables involved.[/quote]

George,
..Just curious...
do you think an .mdb /.accdb using linked tables with ODBC
has better performance than using an .adp with ADO ?

 

[georgedwilkinson]

Hmmm. I've never tested that. Sounds like an excellent experiment for someone. Or you could ask my friend Banana who is really in to this kind of thing.

I was speaking to the width of the tables and the number of records transferred to/from the database and how that will improve linked table performance.

 

[BobMcClellan]

I see. Thanks for the quick reply George.
..bob

 

[Banana]

Unfortunately, I've not had much experience with ADP so I'm afraid I'm no authority to check with.

However, what I can tell you is that in researching for the article about ODBC I wrote few months ago, is that SQL Server team has chose to provide a native implementation of ODBC, so it's pretty much efficient as would be using ADO or ADO.NET.

ODBC can, and often is, implemented on top of a proprietary API. In the early days of ODBC, many perceived this to be a weakness in first generation ODBC drivers. Research soon showed that layering ODBC on top of proprietary native APIs usually has minimal performance impact. In some cases ODBC even out-performed proprietary APIs when the driver adopted strategies to overcome weak default behavior in the underlying native API. For some databases, including Microsoft SQL Server, ODBC actually fulfills the role of proprietary native API.

Source

Next point: Given that .mdb/.accdb can use ADO just as equally well, there is no loss in functionality, other than the obvious that you have to do it via VBA and not UI while retaining the benefit of having local database engine to offload some of tasks.

Now, I can't tell whether .adp would perform faster because of more closer binding to the SQL Server compared to .mdb which has to do it via VBA to bind a form to ADO so that still would be a good question to get answered.

I hope that helps.

 

[BobMcClellan]

Our branch stores run across a WAN and talk to our servers here with apps built as .adp / SQL 2005. The performance is good. I never considered using ADO from within an .mdb / .accdb. I guess depending on what the app is needed for, having the benefit of local tables and local db engine is reason enough to consider it though.

Thanks for the detailed reply Banana.
I appreciate it.
..bob

 

[Banana]

Cool.

A question for you, Bob.

Can you edit SS objects via ADP? I've seen a post saying that it's not as seamless if we use SS 2005 or 2008. Would appreciate the confirmation.

 

[BobMcClellan]

I did also hear that the editing of objects after 2003 was a problem.
To test...I just went into an .adp and added a column to a table without issue.
I spend most of my time in SSMS though. I build tables using CREATE TABLE...and really do not use the .adp UI for anything other than building the forms and writing the code to access the stored procedures (also written entirely in the management studio). I have not used a QBE grid in a very long time

 

[Banana]

Thanks for reporting back, Bob.

I've only seen brief mentions here and there but no detailed of what is actually going with the issues (if there are any) with using SS 2005 or later. But even if one couldn't edit stuff via ADP UI, SSMS is perfectly good enough and much better anyway.

Thanks again.

 

[BobMcClellan]

I couldn't agree more

 

[gemma-the-husky]

you may well be aware that even if you have "no record locking", you do ACTUALLY have record locking in place. Access uses by default something called optimistic record locking

when you write a change, it rereads the record, and verifies that didnt change from the version you are editing, and then permits the change. if it has changed you get the "another user has changed ,,,, message"

that is why you can sometimes inadvertently get this error in a simple app, by opening the same record on 2 different forms.

because of this, you will never get inconsistent updates because of multiple writers accessing the same record - although I suppose it is prossible to get a deadly embrace sort of issue

ie
user a, changes record 1, then gets a problem with record 2
user b, changes record 2, then gets a problem with record 1

if this is really critical then you may need to look at some sort of transaction update procedure.

 

[Banana]

Gemma's comments is spot on for a pure Access solution. When a different backend is used, though, Access does nothing to enforce any kind of locking, leaving it entirely up to the server so the locking behavior may be slightly different.

However, my experience has been that regardless of the backend, people get the messages because the code is stepping on itself.

A common scenario is when someone starts to edits a record, and before the record is saved, fires a procedure that then executes an action query or stored procedure or just ad hoc SQL statement and if it happen that the same edited record is affected, it's perceived by the backend as two separate requests coming over different connections and thus act as if it was two users and return an error that there was a write conflict.

Hope that helps.