Replication vs. Frontend/Backend

[IngoNiclas]

I have read some stuff on the issue of making a database (in my case an Access 2000 db) synchronizable - multiple external users should be able to access it at the same time. However, I do not quite see the difference between replication & splitting the db in FE/BE. When replicating, Access 2000 differentiates between tables & the other objects (queries, macros etc.) by default anyway - only data (content of tables) are exchanged between server & remote machine as with a FE/BE solution. So where is the great advantage of having a FE/BE solution???? Many thanks for your reply!!!!!

 

[fredk]

With the FE/BE you always have the most recent data in the database - you don't have to worry about wondering if it was replicated.

Fred

 

[BobJacksonNCI]

To add to Fred's comment, and noting that I've done nothing with replication, think of FE/BE as a means to increase the capacity and flexibility of Access. Note that ALL implementations of Access allow for multiple Users, just some work better than others...

As I understand it, an example of replication might be a copy of the database on a salesmans laptop that must be synchronized with the office database when he/she gets back from a (hopefully) fruitfull trip. Replication is complex and involves updating BOTH copies with the data that has changed.



HTH,
Bob

 

[willir]

IngoNiclas

Replication is not really suited for high transaction tables, especially if the data has to be intergrated. It is more suited for "sharing" data over distances where the transactions from either party is not needed by the other. As previously indicated, a replication solution is a "solution" for working with Access across the WAN or via modem access.

Examples where replication would work: A contact database, product / pricing database, customer complaint database, a sales order database where remote sales rep is responsible for their own clients.

Example where it would not work: Inventory database, especially where there is low overhead, a concert ticketing system where specific seats are sold.

Replication requires more management, and there is the risk of failure / corruption. If you do end up creating like transactions (i.e., more than one sales rep selling to the same client), the chances of write conflicts increases.

A FE/BE database has one central database. Each user may have their own front end which could be the same or different from the other. (Example: Admin front end and End user front end) Where replication adds complexity to maintenance, FE/BE usually helps reduce complexity to maintenance. It is a very common "solution" for an office LAN.

The one thing not discussed here is Access Data Pages.

Good luck in your decision.

 

[LittleSmudge]

I've used both sorts ( and them both in combination )

In general terms splitting the datbase FE/BE is GOOD for anything other than the most simple single user db.

For a start it allows Front end User interface development on the fly whilst users are activly using the production version.
( you take a copy of the FE and edit it whilst the users are working in the 'real' FE and then replace the old FE.mdb file with the new one when the users are all offline. )


Replication comes into its own when a user needs read AND WRITE access to the database when they cannot see the BE file directly.
( Eg they are working on a lap top away from the office
or - as in a case I worked on
working on a large ( 150Mbyte back end over a dial up line ) )
You have one copy of the BE that is set up using the replication Wizard as the "Design Master". This is then then only place where table design changes can take place.

you then take copies of this Design Master and they are automatically defines as "Replicas".

Remote users can then bind their local copy of the FE to their local Replical and use the database in both read and write mode.

Then, when the remote user is 'back in the office' you 'Resyncronise' their database with the design master ( and ny other replicas ) so that everyone gets thelastest updated information.

Various protocols exist ( including the Mk1 eyeball test ) to resolve issues where users in two replicas have edited the same record etc.



'ope-that'elps.




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

 

[IngoNiclas]

I was thinking of combining the advantages of replication & BE/FE solution as suggested by LittleSmudge. For the internal useres in the network, BE/FE separation makes sense. I tried it two days ago, but for some reason, there was a problem with the code behind my forms. In one case, the code was entirely gone when I set up a new, blank db where I imported all objects (to make it the FE). In another approach, where I just copied the original db and kicked out the tables (and linked them again with the BE of course), the code was still there, but the performance of one particular form was totally unacceptable. Any ideas why this could be?

For the external laptop users, who dial-in over ISDN, I am thinking of indirect replication. Only BE replica on the server would be synchronized. What do think of this solution? Would it be feasible like that?

 

[LittleSmudge]

Not sure why you're having problems splitting the db.

My approach is always to:
1) Start off with the combined FE/BE in one do
2) Fully compile it and fix erors etc.
3) Take a backup copy & archive it
4) Take a copy and call it XxxxFE.mdb
5) Take a copy and call it XxxxBE.mdb
6) Exit XxxxBE.mdb and delete out all Queries,Forms, Reports, Modules and then save it
7) Edit XxxxFE.mdb and delete out all tables
8) Link all tables from XxxxBE.mdb
9) Save Xxxx.FE.mdb and test.



On the issue of slow response ..
Is the table that the problem form is bound to a Huge table ?
Have you now put the XxxxBE.mdb on a Network drive when it used to be on your local drive ?

These things can lead to slow response as a form opening on that table requires Access to send the entire table from the BE to the FE before the FE will display the first record.



'ope-that-'elps.




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

 

[temporaneo1234]

[FW] access replica and client/server applications

Yesterday night I was not so happy 'cause, after working for a month to convert my application from client.mde/server.mdb standard to client.mde/server.mdb + replica, I realized an application too slow.

On LAN, the client (FrontEnd) is very slow to link to a server (BackEnd) based on a file of access (*.mdb) replica (master).

Before replica convertion, the application opens in 1/2 seconds, and now it takes up 3 minutes, and everty time make an operation, the avarege time is so long...

Only if FrontEnd is located in the same pc of BackEnd, application works with normal access time.

I have found in newsgroup (posted from Michael michka Kaplan) that this situation can be based on shared resources/drive to deactivate.
I have tried in client-PC's (with WinXP) to remove sharing, but nothing changes.

The application is based on Access.2003 (same FE that BE)

There are some ideas before I left replica to past?

Bye
Gianluca

 

[LittleSmudge]

Your problem stems from the fact that you are NOT using a Client/Server system. Access cannot support Client/Server - it is a File/Server structure.

when a form opens that is bound to a table the entire table is sent from the back end to the front end. The front end then finds the correct record and displays it.

If you want an Access front end to work over a slow link you'll need to go to something like MySQL backend so that you can work Client/Server.

The front end asks for a record the back end selects it and sends just that one record over the network to the front end to display.


'ope-that-'elps.







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

 

[BNPMike]

when a form opens that is bound to a table the entire table is sent from the back end to the front end. The front end then finds the correct record and displays it.

Which is why you never ever bind to a table. Always bind to a query. Apart from the fact it isolates you from the logical data model, it allows you to select just the records you need. Access will use sophisticated optimisation to minimise the number of pages it fetches from the back end.

 

[LittleSmudge]

Access will use sophisticated optimisation to minimise the number of pages it fetches from the back end.

That's NOT how I understand it works Mike.

The query 'fetch' is only from the table IMAGE.

So you have a local front end linked to a remote back end, the query is in the front end and you can have a form bound to it, fine.
But before the access query can do any work on its "sophisticated optimisation" the ENTIRE table has to be transported over the network to the FE.


So you still have the five minute wait.



G.

 

[BNPMike]

That's NOT how I understand it works Mike.

What's given you that impression?

Why would Access have 5 or more strategies for minimising i/o and then not use them. By the time the data gets to your pc it's academic. A pc can access data in a trillisecond even if it's stored as a pile of lego bricks encrypted in Sioux Indian.

 

[BSman]

I've had applications using Acces (97) FE and BE where a form was linked to a table with about 1.5 million records (and each record was a decent size, not, for example, just 2 or 3 integer columns). They worked very well without the long waits you mention.

I wonder if every table (at least those of any size) has a primary key, particularly a numerical one (text primary keys, especially those involving multiple text columns, should be avoided if at all possible).

Another question is if the network connection is working correctly. I have encountered situations where the network connections were lousy, even though they should have been fast enough (and were sometimes).

If you can set up one drive letter as mapped to the network server where the BE is stored, linking through a drive letter can be quicker than by using UNC (of course, you then lose the flexibility of UNC linking).

I would strongly suggest that you avoid replication if at all possible. Obviously, for the situration of a dial up (as Little Smudge mentions), these really is no option. But ISDN should be quick, I believe, so that would not be appropriate for replication. Replication can be a nightmare to administer. And if any tables require manual intervention when updating through replication you have created a monster.