Making periodic updates to a shared database

[nnaacc]

I've got one database with all the tables and a database password, and another with queries, forms and reports that is secure with users and groups. I'm about ready to give about 100 people access to the database... and a little nervous to see how it will work.

One problem I already forsee is that I need to make periodic updates to reports and to replace (delete and import new) data tables. I can only do so when I am the only user of the database, and I know that some people will always be in the database, sometimes without even realizing it.

I have looked into replication, but the database is secure, and I have had trouble with replication in the past. Is there an easier way?

Thanks in advance for your help.

 

[Rohdem]

We have a similar situation where I work. I built a utility that will copy a new front end from a special directory that only I have Access to. It also compacts the back end. When I make changes to the front end, I put it in my directory and my program copies it onto the server during the night. We have about 40-50 users in our database, but they are usually pretty good about closing the databases and shutting down their machines when they leave. The program I built also keeps an event log so I know in the morning if a database was left open and didn't get copied/compacted. As for deleting and importing tables, you'll have to either do it yourself during the night or get everyone out and make your changes. The NT server task manager is a big help for this process.

I wish you luck!!

Mike Rohde
rohdem@marshallengines.com

 

[nnaacc]

Thanks for the response Mike. Your solution still requires that all the users shut out of the database, and I am hoping for a solution that doesn't require that. The problem basically is that our users keep their computers always on, with most applications always open. All of my users will be using the same database - I wonder if I should give each user a different (but identical) copy. It might be faster, and it would partially solve my problem (since most users will log out occassionally).

I've discussed the problem with some of our network guys, but no solutions have popped up.

I'm going to look into replication of the front end, and keep the back end as is. Gonna have to see how replication can deal with the user defined security...

Still hoping for any other possible solutions...

 

[nnaacc]

I've replicated the database, with good success. I can make updates, and syncronize the databases with users still in the replica. Problem seems to be solved.

But...

In the replica, or the master, I can not delete records from any table. The backend database still works fine, and other (non-replicated) databases linking to the data are fine. When I try to delete a record in the replica or master, this message appears:
"You do not have the necessary permissions to use the 'table name' object. Have your system administrator or the person who created this object establish the appropriate permissions for you."

The message appears when I log in as the administrator (with all possible permissions), or a user who has all permissions to some of the tables. The message also appears when a user logs in and tries to delete in a form based on a query with "use owner's permissions" of just one table. In that case, it still gives the table name instead of the query name.

Thanks in advance for your help...