Access 2003: Help

[tan1sha]

I am creating a database that someone else will be adding data to. I'm doing
this at home and then taking it to the office to copy to a shared pc. I
don't have any admin rights on the pc at the office but we all have different
user names. I want other people only to be able to enter data not to make
any changes to design, is this possible? I've made a form for data entry
which loads at startup.

 

[Scott McDaniel]

I am creating a database that someone else will be adding data to. I'm doing
this at home and then taking it to the office to copy to a shared pc. I
don't have any admin rights on the pc at the office but we all have different
user names. I want other people only to be able to enter data not to make
any changes to design, is this possible? I've made a form for data entry
which loads at startup.

The simplest way (but the least secure) is to simply set the Startup properties to your database:

1) Click Tools - Startup
2) Set the "Display Form/Page' to the the form you want shown when the db opens
3) Uncheck Display Database Window
4) Check or Uncheck other options, depending on your needs.

Of course, any user can hold down the Shift key to bypass your startup options and display the database window; to avoid
that, you'll need to Disable the Shift Key bypass; the link below includes a code module which will do this

http://www.mvps.org/access/general/gen0040.htm

It's best, also, if you split your database into Frontend (forms, reports, etc) and Backend (data only). This way, when
you make changes, you can change only the Frontend, leaving the current data as is. Otherwise, you'll have to import all
changes to your "in use" db, which can be somewhat troublesome.

If you want more security and control over your program, and what parts of your program specific users can interact
with, you'll have to delve into the world of ULS, which can be quick tricky and complex.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[tan1sha]

Thanks for that. How do I split the database into frontend and backend
(sorry not an Access expert)!

 

[Scott McDaniel]

Thanks for that. How do I split the database into frontend and backend
(sorry not an Access expert)!

Access includes a Splitter Wizard ... click Tools - Database Utilities - Splitter Wizard.

You can do this manually as well. I'd advise you to first make a backup BEFORE starting this, and take that backup and
rename it so there's no question that you could inadvertantly delete it, or otherwise mess it up. Stick in on a CD or a
USB drive or something, then do this:

1) Make a copy of your current db. You'll now have 3 - the backup you created above (which you've stored off-machine
somewhere), the original db, and the new copy you just made.
2) In your original db, delete everything except the tables. This is now your "backend"
3) In the copy you made in Step 1, delete all the tables. YOu may be prompted to delete relationships as well; that's
fine. This is now your "frontend"
4) Now click File - Get External Data - Link Tables. Locate the your original db and select all tables (note: don't
select any tables which begin with MSys, if you see any)
5) After the linking finishes, verify that your database works as planned.

From here, it's pretty simple to make changes, at least to your frontend. After you've finished making changes to the
new frontend, just replace the one your users are currenlty using with the new one, relink it to the backend, and users
can continue to work with the exact same data as before.

If you must make changes to the backend, however (like add a Table, or add a Column to a Table), you'll need to do that
in the "live" backend - you'll have to get all users out of the db, open the DB exclusively, and make your changes.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[tan1sha]

Thanks very much!

Access includes a Splitter Wizard ... click Tools - Database Utilities - Splitter Wizard.

You can do this manually as well. I'd advise you to first make a backup BEFORE starting this, and take that backup and
rename it so there's no question that you could inadvertantly delete it, or otherwise mess it up. Stick in on a CD or a
USB drive or something, then do this:

1) Make a copy of your current db. You'll now have 3 - the backup you created above (which you've stored off-machine
somewhere), the original db, and the new copy you just made.
2) In your original db, delete everything except the tables. This is now your "backend"
3) In the copy you made in Step 1, delete all the tables. YOu may be prompted to delete relationships as well; that's
fine. This is now your "frontend"
4) Now click File - Get External Data - Link Tables. Locate the your original db and select all tables (note: don't
select any tables which begin with MSys, if you see any)
5) After the linking finishes, verify that your database works as planned.

From here, it's pretty simple to make changes, at least to your frontend. After you've finished making changes to the
new frontend, just replace the one your users are currenlty using with the new one, relink it to the backend, and users
can continue to work with the exact same data as before.

If you must make changes to the backend, however (like add a Table, or add a Column to a Table), you'll need to do that
in the "live" backend - you'll have to get all users out of the db, open the DB exclusively, and make your changes.


Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com