Microsoft Access 2007 & Power users

Question

We're using MS Access 2007 as our DB and should support users with limited access (power users):

• The DB should be common to all users (hence cannot be located on user's private folder)
• We are not allowed to grant permissions for all users for R/W for a certain directiry
• Yet we need all users be able to use this DB.

Can anyone can think on a solution to this issue? (I can only think on writing a service to this job, but I'm sure there must be something better)

Andyane

Are you runing the UI inside Access itself or a totally different layer and only using Access as your database backend? – Vetiver

Answer 1

Have a look at this to help you make your decision. Personally I won't recommend using Access if your user is more than say 4 simultaneous users as a shared database. I've used solution however where we use Access as a front end to a SQL Server backend where we cache SQL data locally for faster access and push necessary data changes back to SQL Server. And have that Access file reside on the client computer.

Putting Access anywhere will require a R/W permission on that directory since Access will create lock file in that directory (or it used to be anyhow).

If you insist on using Access, I'd go w/ Access front end connecting to SQL Server backend. The network share option is doable if you don't have a lot of users (4 - 8 the most).

Vetiver

I have clients happily running 20-25 users all day long and it works and works well. A few tables have 800K records. – Topic

I used to work at a company where we did this. When we migrated all of our users to SQL Server Express, the performance of our application doubled, and the stability improvement was essentially off the scale. Some of them were 20+ users, but most of them were five. – Aquilar

Answer 2

You want to split the MDB into a Front End MDB containing the queries, forms, reports, macros and modules with just the tables and relationships in the Back End MDB. The FE is copied to each network users computer. The FE MDB is linked to the tables in the back end MDB which resides on a server. You make updates to the FE MDB and distribute them to the users, likely as an MDE.

See the "Splitting your app into a front end and back end Tips" page for more info. See the Auto FE Updater downloads page to make this relatively painless.. The utility also supports Terminal Server/Citrix quite nicely.

Now I don't quite understand why you have the restriction mentioned in your second bullet There has to be a common folder on a server somewhere where all usess have R/W privileges. Please explain this in more detail.

Topic

Answer 3

SQL Server Express can be used as the backend database, and it doesn't require that you give write permissions to a folder for everyone.

The SQL Server Migration Assistant 2008 for Access V4.0 can be used to migrate the Access database tables to SQL Server Express.

Using SQL Server Express as the database server will greatly improve the stability and reliability of Access in a multiuser environment. I have worked with Access for several years, in installations with 20+ concurrent users. If you are doing something similar, I strongly recommend that you use Sql Server or Sql Server Express as your backend database.

Aquilar

But it does require the IT dept to get involved. Frequently this is a lot more trouble than it's worth. And if you are having troubles with the stability and reliability of Access in a multiuser environment then you either have a flaky network or are using Access over a WAN or wireless network. – Topic

If you are using an Access database for a backend in a multi-user scenario, it's not a question of if it will crash, it is a question of when. – Aquilar

No, Robert Harvey -- it's a question of your competence. If you can't design an Access app with a Jet/ACE back end that never corrupts then you are not competent enough to be attempting the job in the first place. – Putrescible

Once every two or three years? And with no data losses? With 25 users? Now granted that app should've been upsized to SQL Server. However that was cancelled on me by a Dilbert's PHB (Pointy Haired Boss) – Topic

Perhaps a bit of perspective is in order. The application I worked on has about 120 forms, 150 tables, 200 reports, and somewhere in the neighborhood of 400 queries. There is nothing wrong with the application; it is a good design, it's just a big application that does a lot. Microsoft has even looked at it. But they also said it was the largest Access application they had ever seen. The Jet database doesn't perform well under this kind of stress. I am telling you the truth when I say that moving the Access backend to SQL Server greatly improved its stability. – Aquilar

Who at MS stated it was the largest app they had ever seen? One of mine is considerably larger. I was told the relationships window, which was printed on a plotter and was 4' wide and 3' tall, was hanging on the dev teams hallway at Microsoft for almost a year. And this was at least 3 years ago I was told this. – Topic