Splitting a database...How to do Front end and Back end.

[madetoheal13]

Hello! I am the adminstrator for my company's database, that I would like to go live with next week. I almost have it completely done but am new to Access...(pretty much learning it on my own). I would like to split the database I guess (is what these forums are telling me). I have 3 departments that use the database for different functions. I take it I put the back end on our company server and the front ends on desktop/labtop computers. This allows to do work "offline" so to speak and then sychronizes it with the server later on right? There are 4 representatives that travel and do labtop work...when they return to the office will the info they've updated to their files...update to the main database...I guess I am in the dark. Please advise.

Thanks.
Tired of access here.

 

[lupins46]

When you split a database for multi-user operation you are placing ALL the data into one backend file and placing everything else (forms, reports, queries and code in the front-end). The backend goes into a shared folder and each user has a copy of the front-end which contains links to the tables in the backend.

No-one is ever working 'off-line'; all users work while connected to the single backend file through their own copy of the frontend.

If you want users to be able to use the database away from the network you need to look at the topic of database replication.

 

[madetoheal13]

Database replication? Is this easy to do? I really have no clue on how to do this or how long it takes, etc....please advise.

Thank you.
Madetoheal13

 

[LittleSmudge]

<Truth and honesty mode>
Replication is NOT "Easy" but then again neither is mastering the English language !
</Truth and honesty mode>

For anything more complex than a single user database on a single machine you need to split the db BackEnd ( containing all the tables ) and FrontEnd ( vontaining everything else )

The FE and BE CAN both go on the company server and multiple users can open the FE simultaniously and work independently.
Alternativly, you CAN move a copy of the FE to each local machine.
Which of these you choose is a matter of assessing MANY parameters and has been discussed on this forum ( at length ) in other threads.
If a search doesn't find what you need I'd suggest starting a new thread on that one.

As for this thread - your main issue is the "field agents" using laptops.

IF they just need to grab a copy of the db and take it with them ( Read Only ) then you just copy the FE and BE files to their laptop and re-link so the FE knows where the BE now is ( relative to the local C:\root ) then wave them goodbye.

If you want the laptop users to be able to update info on the database and then transfer the data back to the master db when they return then you MUST use Replication.
( Or give them web based wi-fi 24/7 access to the master database )


Replication is just a matter of defining the central master as the "Design Master" ( see help files on how ) and then making copies of this master. Each copy will automatically become a replication copy.

Schema changes can only be made in the Design Master

Data changes made in any replication copy can then be synchronised with the master. Syncronisation is a two way process so the copy then also get to know about changes made in the master and in other replicas ( that have previously be synchronised ).


Personally I never let users work directly in the Replication master. ALL local users work in a local replication copy and that too is synchronised with the master. This keeps the master one stage further away from harm because recovering from a damaged or lost Master is not impossible but it is a real pain.



'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[madetoheal13]

Thanks LittleSmudge! Does anyone know if there is a step by step procedural sight or link on splitting a database...I have a book from Microsoft but it isn't very good. Thank you.

Madetoheal13

 

[lupins46]

Tools>DatabaseUtilities>DatabaseSplitter

 

[LittleSmudge]

Personally I avoid the Database Splitter wizard for two reasons :-

1) It's easy to do manually

2) I'm a control freek

- Seriously splitting FE BE is the sort of thing you do to other people's designs or you do once and then realise what a fag it is and promise faithfully to design from scratch with split dbs from that day forward.


Steps:
Compile and then Compact and Repair your master
Take a backup
Call your master DbNameFE.mdb
Take a copy and call it DbNameBE.mdb
Open the DbNameFE and delete all of the non-system tables therein
Compact & Repair and then close the FE
Open the DbNameBE and delete all Queries, Forms, Reports, Modules ( and heaven forbid you still have macros! Delete them too )
Compact and repair the BE and then close it
Open the FE again and link all tables from the BE into the FE
Close FE

Job Done


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.