Network d/b Replication for Multiple Users - Best Way? 2

[TR6]

I have been assigned the project of taking an existing database, placing it on the company's network, and make it accessible to multiple users (up to 30). The users will be able to search and view records, enter new information, and edit existing data through various existing forms. I also need a method to arcive changes.

I've never done this before - I'm a mechanical engineer not a programmer, so I'm looking for suggestions and guidance on how to best accomplish this. My first thoughts are to create individual replicas for all users and place a shortcut key on the users individual desktops. Then perform a syncronization during a "Non Production" period, like 12:01 am to update all changes to the data base and replicas. I have no idea how long the syncronization process would take to update 30+ replicas, each with a main data table of 8000+ records.

So, my question is - is this a reasonable approach, or is there a better method to accomplish this?

Your thoughts and suggestions are greatly appreciated.

Bill

 

[Lightning]

Hi Bill

No, there is a better way. Since you say you're not a programmer, I'll start at the basics. Sorry if I'm telling you stuff you already know.

Access is designed to work as a "Split Database" application and has a built-in tool to do this.

A Split Database application consists of:

1) a "Back-end" database which contains all the tables the database requires. Nothing else.

2) a "Front-end" database which is the user interface. This contains all the forms, queries, reports, macros and VBA code that the application needs to fulfill its functions.

The usual setup is for the Back-end db to be stored on a network server that all users will have access to, and a copy of the Front-end installed onto each users desktop PC. The Front-end is usually set up to be accessed from a desktop shortcut, especially if Security has been implemented on the application.

By using this type of setup, all users have access to all the data, and this data is completely up-to-date at all times (therefore no synchronisation process).

The process of splitting the database automatically creates linkages between the Front-end and the Back-end Dbs. As long as the Back-end is not moved from its location, the links will remain current. If the Back-end is moved, the links need to be refreshed for each individual user. There are ways to automate this (search this forum for "Refresh Links&quot.

You split the database by using the Database Splitter wizard. Select Tools|Database Utilities|Database Splitter from the menu and follow the instructions in the dialog boxes. (Tip: move the unsplit database to the network location where the Back-end will be stored before running the Database splitter.)

To refresh the links between the Front-end and Back-end databases, select Tools|Database Utilities|Linked Table Manager from the menu.

You will also need to implement some level of record locking, to stop two users attempting to change the same record at the same time. Select Tools|Options|Advanced|Default record locking to set this.

These topics in the Access Help File will give you some more information on thes processes:
Split an Access Database
View, refresh or Change the file name and path for linked tables
Set options for a shared Access database

As a personal preference I recommend staying away from replication and synchronisation if at all possible. Using the split database approach is usually better in the long term.

HTH
Lightning

 

[JeremyNYC]

Lightning's right on target. I would add a cautionn taht this will not be an easy thing to do. There are a lot of things to keep in mind, and many things to balance.

You will definitely do better using the front end/back end scheme Lightning's described than if you try to use replication. But you have some more decisions to make.

I've been developing Access databases and have never built one that gets heavily used by 50 people (and I'll bet dollars to donuts that if your planned user base is 30, it will be 50 within a year). It might even make sense to put the back end in SQL server, MySql, or some other more robust back end than Jet (the back end Access uses).

You're definitely going to want to make sure your table scheme is as solid as possibe, that your indexing is as optimized as possible, and that you pull as little data across the wire as possible. There's a good article on this subject on my website, written by Paul Litwin, a guru of Access world (Fundamentals of Relational Database Design).

You're also going to want to plan carefully how you will roll out new versions of the front end to your users. I have a scheme that I've written about often on TT, and I've got a couple of pages about it on my web site. I rolled out three new versions today!

I would highly recommend that you buy the Access [version number] Developer's Handbook. You're about to jump into a big project--it should be a lot of fun, but it will require a bunch of learning.

Welcome to the world of Access Development! It's quite a bit of fun, actually.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[TR6]

Lightning & Jeremy,

Many thanks for your input, especially the basics. I didn't know anything about spitting dbs. You both saved me a lot of future aggrevation and headaches. Now I have something new to think about.

So, by splitting the d/b, revisions to the data tables are available to all users immediately(??).

Another question, I will be entering the data into the data table myself. Does it matter when I split the db, before or after the data is entered?

 

[Lightning]

So, by splitting the d/b, revisions to the data tables are available to all users immediately(??).


Yes, once the data is entered into the tables it is available to all users. Note however, that if you are using a data entry form to enter data into the tables (as is the best practice) the data is not entered into the table until the user saves the record. This is done in a variety of ways, but is usually either by Closing the form OR by moving to the next record or a new record. Leaving a form open with a record displayed will lock that record, meaning that other users cannot access it.

Another question, I will be entering the data into the data table myself. Does it matter when I split the db, before or after the data is entered?

No. The splitting process does not affect the tables themselves, so any data already entered is quite safe.

I echo Jeremy's recommendation of buying the Access Developer's Handbook, but with one caveat: It is not a book that teaches the basics of Access - it assumes a level of expertise with the Access system. For a Beginner/Intermediate level book the Access version for Dummies books are good, as is Building Applications with Microsoft Access.

Good Luck

HTH
Lightning

 

[Jedi420]

Hello all,

I have just been given an assignment very similar to TR6's. It's a project DB that MOST of the executives of my company are going to use to keep track of projects that take longer than 20 days (which is alot!). Every executive will have to update records pertaining to their project at some point in time for their monthyly meetings, so that means that there will be about 20-25 people updating this thing possibly on the same day .... some even share projects together so they may have to update the same record from time to time (hopefully not simultaneously!). The DB has already been sort of started by my old boss who no longer works in our department, so I've been given a whole bunch of things the execs want changed and what not. The DB so far is not split. I guess my question is, do you think I should split it ... I guarentee there will be many users on this thing the day before their meetings and I do not want any of them to be pissed at me because they got locked out of trying to update their projects. Any suggestions would be most appreciated or is the advice that was given to TR6 sufficient for me as well. Thank you.

Sticking feathers up your a$* does NOT make you a chicken!
-Fight Club

 

[JeremyNYC]

Jedi,

Yes, this database _must_ be split. Also, keep in mind that your user base will be at least twice as big as you think it will--most of those execs will have their assistants do at least some of the data entry, some of them will split the work amongst two or more assistants, and other execs will be brought into the system, if it's at all successful.

With a user base this large, you're starting to push the limits for Jet, the database engine behind access. You may want to use msde, the crippled version of SQL server that comes with Access XP. You may want to use SQL Server. But if you're really going to be getting that many people in there, using Access (Jet) for the back end is going to give fairly poor performance.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Jedi420]

Thanks for the advice Jeremy. I just found out that my user base is going to be around 15 (thats execs and their analysts, rouded up). For now at least, it must be an access DB due to time constraints and the time delay in ordering software.

What I plan on doing is having the Back end on the server so everyone can get to it ... I also plan on making every dept. an identical front end, but for feasability purposes, those must also be kept on the server. I imagine this is not the best manner to handle this, but at least its better than just having one front end, right? This means about 1 front end for every 2-3 people and those 2-3 people will not be updating the same info ever it seems.

This isnt a life-critical app or anything, but its for the BIG guys and they all know that it's only me working on it, so I'd like to have it work very well. I have about 1 month to get it running in time for their next meeting. With all the help and advice I've read in the forums though, I think I'll be OK. Any more advice though will be taken with open arms. Thanks a bunch.

- Jedi420

 

[JeremyNYC]

Jedi,

Well, a smaller user base will certainly make things easier for you. But I'll put up dollars to your donuts that within a year you have 30 people using your database.

On the question of front ends--no, I would never, ever use that solution. Check out my Deploying Databases article on my website. There are MANY arguments against sharing a front end, and almost none for it. You _will_ run into problems if you go about it this way.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Jedi420]

OK ... I'll take your word for it ... 1 front end for every user. Thank You. (^_^)

- Jedi420

Sticking feathers up your a$* does NOT make you a chicken!
-Fight Club

 

[Jedi420]

OK ... I'll take your word for it ... 1 front end for every user. I'll let you guys know how things turn out. Thank You. (^_^)

- Jedi420

Sticking feathers up your a$* does NOT make you a chicken!
-Fight Club

 

[JeremyNYC]

Jedi,

OK, I just want to put something here to clarify, though it's up on my website already....

The way this should be done is that there is one front end that sits on the server. When a user starts up the database, there should be some method of checking whether or not the user has the latest version of the front end on his or her PC. If not, copy it over from the server. If the latest version is already there, just start it up directly on the PC.

So that's sort of one front end for every user, sort of one sitting on the server. But no one should ever USE the copy sitting on the server, just make copies of it.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Jedi420]

Ok ... Ill read your article. Thnx!

- Jedi420

 

[perrymans]

Just to cover a couple other things:

1) TR6, replication and back-up is easiest on the network level. Using a Raid config will copy all data to two different sevrers/ drives at all times. Performing nightly back-ups will give an archive OF THAT DAY if that is what you want. Copying ALL tables to a second database, and creating append queries to retrieve old records from the "Live" database will keep it running faster. You can even add a command button to relink the Live databse to the Archive database, meaning you use all the same forms, queries reports, etc... to view the archived database (because the table structure and names are the same).

2) RecordLocks is a concern with any amoutn of multiple users. Ensure you set a forms RecordLocks appropriately (click in the field and press F1).

3) Access is billed as 50 users or less, but even Microsoft suggests no more than 25 users! I can find the link again if you want to read it.

4) Finally, after you have your Front End, change it to an .mde file (Tools->Database Utilities->Make Mde File). This LOCKS all forms and code from the users changing it. If they change something, it could start corrupting your records.

Use the suggested security (I can email you the .doc all about proper security).

Sean.