Replication?

[bluke]

I've created a rather extensive database with security that about 15 users in various global locations across our network will use on a daily basis. The problem i'm facing now is that as this database is ever changing (thanks to my boss who always wants different things added) when I go to make design changes to reports, form or queries I often encounter the error that I do not have exclusive permissions to the db to make changes. What can I do to alleviate this problem so I can make changes on the fly without disrupting other users? Will Replicating the db work? If so, will synchonizing update forms, button controls on the main form etc?

Help, suggestions would be appreciated!

Thanks.

 

[redneckgeek]

I've never worked with replication before, so I don't know if that will work for you or not.

If you are dealing with multiple users in multiple locations, you may want to split your database. The "back end" will contain your tables, and you'll store that on the network where all the users can access them. The "front end" will contain your forms, reports, code, etc. Each pc has their own copy of the front end. You can make changes whenever you need to on your local copy, and then copy it out to the network. Your offsite users then copy the front end whenever necessary. All of that can be automated (I use a batch file), so that it's transparent to the user.

Of course, depending on how large your app is, this could be a lot of work for you, and goes a little deeper than I'm able to explain in one post in a forum.

 

[boblarson]

Replication should ONLY be used on data NOT with forms, reports, etc. and your situation is not a candidate for replication. See here for more about replication:
http://dfenton.com/DFA/Replication/index.php?title=Main_Page

In your case you should split your database, as redneckgeek suggests, and then each user has a copy of the front end on their machine.

I have a tool on my website that will allow you to enable auto updating for any frontend. Yes, the first distribution you will need to get it to people somehow and that is in your court. But, if you have enabled auto updating, then you can just go into your master file on the network, make your changes, change the version number in two tables (one in the frontend and one in the frontend that is linked to the backend), then when your users open their frontend and the version numbers don't match it will tell them that their frontend is out-of-date and it will close, delete the old file, copy the new file, and then reopen for them automatically.

I had to create this for a big healthcare provider I worked for which originally had over 100 users using a single mdb frontend on the server and they wondered why it crashed each week, or several times each week. So, once we implemented this and moved to frontends on each user's machine, they never had a corruption issue again as long as I was there.

You can download that free tool here:
http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx

 

[dfenton]

In addition to Bob's useful comments, I would argue that an app that's distributed in 15 locations would be much better hosted on a central Terminal Server. That way, users could have the most up-to-date versions of the front end each time they launch the app, and would have real-time access to the data. Likewise, it means that administration is very simple, since it's all in one location, on one server.

 

[bluke]

Thanks for the great help! I've split the database, so I now have 2 db's, the *_be db and the original on our network, do the other users just make copies of the original (frontend) and place them on their local (c) drives? Do I install the utilities mentioned in this post before I ask other users to make copies? Lastly, how does changing the location or copying the front end to the local drive affect the security and mdw file?

 

[redneckgeek]

That's one of the reasons I use a batch file - I copy both the .MDB and the .MDW files from the network at the same time.

 

[bluke]

So the users need to have the front end db and the mdw on their local drive for this to work properly?

 

[redneckgeek]

That's how I do it.

I keep a local copy of both files on my hard drive for development. When changes are made, I copy them to the network. I created a shortcut on each desktop that points to the batch file . When I've made a change,
I send out an email to my users, and they just click on the shortcut and it copies from the network to their local drives.

Others have pointed at different (and probably easier) ways to do this, I just chose this method years ago and never bothered changing it.