General Questions help!

[Allen]

I have a few Questions.

1. When using an Access Db, sharing with more than one
person, is it better to split the database, or juct creat
a shor cut, though a map drive?

2. How do you lock down the database so people can not
edit it?

3. How do you hide the object window?

4. What is some good software that can make an Access Db a
runtime version with out having access installed?

5. Is there a better way to manage users, than what is
built in?

6. When making an Acess Db a run-time version, can the
above aply as well?

Thanks for you time, Allen

 

[GVaught]

1. Split the database: Back-end contain the master tables and resides on a
server type CPU such as NT, Win 2K Server, Win 2033 Server. If you use XP
Pro or 2000 Pro you are limited to 10 users. The front-end resides on each
users machine and contains the linked tables from the back-end, queries,
forms, reports etc.

2. In order to control who can edit and who can not invoke User level
Security. However, before you implement on your database read the articles
you can find out at the Microsoft Knowledge base and experiment first on a
copy of your database. I've heard many a people locking themselves out of
their own database to which there is no recovery except using a backup copy.

3. You can hide the db window by clicking a box under Tools | Startup.
However, anyone who knows the bypass keys can still open the database
window. In order to protect this you need to write code that prevents the
bypass key from operating at the same time you create a backdoor for
yourself. You can also lock yourself out from the db window if you are not
careful. Therefore, test any code on a copy of your database.

4. You have to purchase the Developers Edition in order to get a royalty
free runtime version. The version you buy can only support the version of
mdb file you are using. Therefore, if you are using Access 2000 then your
Developer's Edition has to be for 2000. If you buy 2002, then your mdb
should be 2002 database. Although, it may be possible that a 2002 Runtime
will support 2000 mdb as they use the same Jet Engine. I can not confirm
this last statement as fact.

5. Using User-level security is always the best method.

6. You can use user-level security using a runtime version. However, the
setup can be tricky as you must have an admin level created at the time of
distribution which allows your designated user the ability to add and remove
permissions at the Admin level. Again read up on this before implementing
and always use a copy to experiment.

 

[John Vinson]

I have a few Questions.

1. When using an Access Db, sharing with more than one
person, is it better to split the database, or juct creat
a shor cut, though a map drive?

If it's shared, it's almost always best to split. A shared full
database is going to have adversely affected performance and more risk
of corruption.

2. How do you lock down the database so people can not
edit it?

Implement Access security... *carefully*. Get the Security FAQ from
Microsoft or from the CompuServe forum in my .sig.

3. How do you hide the object window?

Specify a startup form in Tools... Startup, and uncheck the Show
Database Window checkbox on the Startup screen.

4. What is some good software that can make an Access Db a
runtime version with out having access installed?

Depends on your version of Access; Microsoft keeps changing the name -
Office Developer's Edition, or Visual Studio Developer Tools.

5. Is there a better way to manage users, than what is
built in?

Depends on your definition of "better"...

6. When making an Acess Db a run-time version, can the
above aply as well?

Yes.

 

[Wayne Morgan]

Replies in line below.

--
Wayne Morgan
Microsoft Access MVP

I have a few Questions.

1. When using an Access Db, sharing with more than one
person, is it better to split the database, or juct creat
a shor cut, though a map drive?

Split the database. Put the tables in the back-end to be shared and give
each computer a copy of the front-end with the code, queries, forms,
reports, and any temporary tables you may be using. Just the data should be
in the back-end.

2. How do you lock down the database so people can not
edit it?

Look at Security in the help file. Also check the User Level Security Wizard
under Tools|Security. Do this on a COPY of your database. ALWAYS keep a
backup copy. Files do get corrupted and everyone makes mistakes.

3. How do you hide the object window?

Tools|Startup, it is one of the startup options. Be careful with these
options. Disabling the Special Access Keys, Toolbars, and Menus all at the
same time can leave you with no way back into the database yourself.

4. What is some good software that can make an Access Db a
runtime version with out having access installed?

Office Developer Edition or, in 2003, the Developer Extensions (I think I
have the name correct). You also have the option of an MDE file instead of
an MDB file. This will help prevent users from editing the front-end, this
will also work with the regular Access installed.

5. Is there a better way to manage users, than what is
built in?

Using a SQL Server back-end may be better, depending on what you're trying
to do.

 

[Tom Wickerath]

Hi Allen,

My answers are shown in line below.

Tom

___________________________________________


I have a few Questions.

1. When using an Access Db, sharing with more than one
person, is it better to split the database, or juct creat
a shor cut, though a map drive?

The database should be split. Although you can share the entire database, this involves a
lot more network traffic to transfer all the bytes of information that define each object
(query, form, report, etc.). In a seminar that I attended at Microsoft earlier this year,
the issue of sharing an entire database was identified by an Access Program Manager as the
# 1 cause of database corruption. Unfortunately, they have not published this advice, as
far as I know, in either the KB or MSDN.

Also, you are much better to use UNC paths to the back-end database, rather than relying
on a mapped drive. A mapped drive requires that all users have the same drive letter
mapped appropriately. This is not a requirement when using UNC paths (\\server\share).

____________________________

2. How do you lock down the database so people can not
edit it?

Download and read the Access Security FAQ. However, be aware that implementing Access
security only raises the bar. It will not keep someone out who is determined to get in.
Although this document covers versions up to 2000, the information applies equally well to
Access 2002. I suspect that it also applies to Access 2003 as well.

Microsoft Access Security FAQ Available in Download Center
http://support.microsoft.com/?id=207793

Make sure to practice on a *copy* of your database first.
____________________________

3. How do you hide the object window?

Tools > Startup. Uncheck the option to Display Database Window.

____________________________

4. What is some good software that can make an Access Db a
runtime version with out having access installed?

You need to have the Developer's edition of Office to do this. This has a new name for
Office 2003, but I can't remember exactly what it is called. This gives you a royalty
free right to redistribute the Access run-time. Without going this route, you would be in
violation of Microsoft's EULA (End User License Agreement) for Access.
____________________________

5. Is there a better way to manage users, than what is
built in?

You can "roll your own" so-to-speak, but that's going to involve a lot of work on your
part.
____________________________

6. When making an Acess Db a run-time version, can the
above aply as well?

Probably, as long as you don't attempt to make changes in design view to any objects
during run-time. For example, some methods of changing report properties rely on first
opening the report hidden in design view, making the change, and then opening it in
preview mode. This just doesn't work with a run-time version, since you are not allowed
to open any objects in design mode.


There are many changes that are usually required to implement a shared Access database
successfully. Start by reviewing the information available on Tony Toews web site:

http://www.granite.ab.ca/accsmstr.htm

In particular, pay attention to all of the details in the links "Microsoft Access
performance FAQ" and "Corrupt Microsoft Access MDBs FAQ" (to learn methods to avoid
encountering corruption).

 

[John Vinson]

If it's shared, it's almost always best to split. A shared full
database is going to have adversely affected performance and more risk
of corruption.

Larry Linson pointed out to me privately another big advantage of
splitting: if you send a client a split database, you can send them an
updated frontend without needing to be concerned about their data.
Such upgrades are possible with an unsplit database but much more
difficult.

 

[Larry Linson]

If it's shared, it's almost always
best to split. A shared full
database is going to have adversely
affected performance and more risk
of corruption.

I certainly agree that a multiuser database should be split, but I think it
is also desirable for _any_ database that you distribute to someone else --
otherwise, you will have a non-trivial situation when you need to send them
an update to the queries, forms, reports, macros, or modules.

Split, your update doesn't even get near their data; unsplit, you have to
worry about their data.

Larry Linson
Microsoft Access MVP