Deploying Access over a Network (How To)

[martinr]

I need to deploy an Access 2007 database to a network server to
allow Multi User access to the data.
What are the minimum access permissions that should be assigned to the users’ directory that will allow them to open forms/reports and edit records but not directly access the tables?
Should I ‘split’ the database and if so should the tables be stored in a separate directory with the same permissions or should they be different to the permissions for the Access file?
The options I have for granting permissions/rights include (settings in brackets):
Supervisor (no)
Read (Yes)
Write (???)
Erase (no)
Create (???)
Modify (???)
File Scan (Yes)
Access Control (no)
Also, what’s the best way to stop uses from opening forms/reports/queries in design mode?

 

[ButtonMoon]

I need to deploy an Access 2007 database to a network server to
allow Multi User access to the data.
What are the minimum access permissions that should be assigned to the users’ directory that will allow them to open forms/reports and edit records but not directly access the tables?

There is no such set of minimum permissions. A Jet/ACE database is and must be an open book as far as users are concerned because every user must be granted either read-only or read/write access to the database file itself.

Is there a reason why you didn't or can't implement the database in SQL Server or some other SQL DBMS? Still keeping the forms and reports in Access of course. If role-based security is any part of your requirement then you will need to do that and if it's a multi-user system on a network then it makes sense to use a client-server DBMS anyway.

 

[CJ_London]

This is a big subject and does not have a simple answer and really should have been built in before you started development.

Should I ‘split’ the database and if so should the tables be stored in a separate directory with the same permissions or should they be different to the permissions for the Access file?

Absolutely, have the BE on the server and a copy of the FE on each users machine. If you don't it will become almost impossible to maintain and subject to possible corruption. You can also password protect the back end so the casual user can't get in.

will allow them to open forms/reports and edit records but not directly access the tables?

I presume you mean not able to edit the table design - if they can edit records they are accessing the tables - hide the linked tables in the FE (set their hidden property to true) - but won't stop an access knowledgeable user.

Also, what’s the best way to stop uses from opening forms/reports/queries in design mode?

Making the FE an .accde will stop forms and reports being editable. queries are more of an issue - you can hide them - or hide the whole db window or the navigation pane, it depends on what you require

With regards individual rights you need to build this into your forms - depending on what level of detail you want to have you can disable/enable/hide/show navigation buttons, sections of a form or individual controls depending on the user profile.

You'll need to set up the user profile yourself - if you look around this forum you will find plenty of threads where this has been asked before

With regards server rights, each user will need full access permissions for the folder where the BE resides.

 

[martinr]

Thanks for your reply.
I'm open to suggestion as to an alternative DBMS but would need to do some training as Access is the only one I've used to date and my understanding was that SQL Server licence is expensive(?) install and requires a lot of training(?) to use. What are the main advantages of this over Access?

 

[CJ_London]

main advantage of sql server over access?
1. higher degree of security
2. access is limited to a 2gb file - but can be overcome to a certain extent by having multiple back ends but this still means a limit of a 2Gb table. SQL server is not so limited
3. SQL Server allows for more complex queries (e.g. 'if exists then run this query') - altough these can all be handled in Access by using recordsets in VBA
4. SQL Server Express is free, but does have some limits on size and number of concurrent users
5. SQL Server is a 'serious' system whereas IT tend to consider Access as a toy

However:
6. SQL Server doesn't have a front end - typically use Access or a web development tool

 

[gemma-the-husky]

but yes, access is multi-user out of the box, in a way that excel just isn't

having said that, although in theory multiple users can share the same database, in practice this is not so good, and is likely to lead to corruption. Hence the suggestions above to

a) separate data from code and
b) do not share code

Personally, I would concentrate on designing a database that works first, and then think about more advanced issues. Making an mde/accde is a simple option. Do not lose your master mdb/accdb version though. And save regular code "versions" in case your active version gets corrupted.

using SQL server backends definitely requires more expert knowledge than using access.

 

[ButtonMoon]

Personally, I would concentrate on designing a database that works first, and then think about more advanced issues. Making an mde/accde is a simple option. Do not lose your master mdb/accdb version though. And save regular code "versions" in case your active version gets corrupted.

Dave, do you mean Build it first in Jet/ACE. Upgrade to SQL Server later ? I know people have suggested that before but I think it's bad advice for lots of reasons. Even at best it involves more work overall: creating two versions of the database instead of one. Very likely it may require a lot of complex rework that would otherwise be unnecessary. Jet/ACE supports features that make no sense in SQL and also lacks some of the things you may ultimately need in SQL, so a database designed for Jet/ACE is potentially quite different to the one you ought to implement in SQL Server (consider that even basic features like unique and foreign key constraints work differently in Jet/ACE compared to SQL Server). The end result will probably be more satisfactory if the application is architected for SQL Server as early as possible.

My advice? If you think you are likely to move your database to SQL Server in the future then you should begin by developing in SQL Server. It's much simpler in the end to do that than to develop in Jet/ACE and then upgrade. If on the other hand you truly think you will never need to use a SQL DBMS for a multi-user database then think again. You probably will. A SQL DBMS has so many advantages for so little effort it's difficult to think of good reasons not to take that option.

Learning a new tool takes very little time relatively speaking. Redesigning a database and re-engineering the applications that run on it is likely to prove much more expensive.

 

[spikepl]

One of the main advantages of Access over MS SQL is that it seldom involves the IT-department, especially if Access is already installed. IT-departments often do not consider Access as a "real DB" so it is beneath their dignity to get involved. MS SQL, on the other hand, would often require IT people, hence you'd have to go through the ususal channels to obtain a resounding "NO" (standard fare from any self-respecting IT-dept)

If you need what you need now, and Access can do the job now, go for Access. Planning om some future requirements which may or may not materialize is insane, unless you already have some reasonable inkling concerning the future, or a cooperative IT-lot.