To split or not to split...that is the question :) (1 Viewer)

Hi, I am just wondering if there is a rule of thumb about when to split the tables of a database and when to keep the tables intact?

I have a database I need to put on a server. Only one user will access the forms, but many users will need to query the data. So in this case I thought it best to split the database into a FE & BE.

Also, I find spiting the database means that if I need to update the FE with new code or forms, I can just overwrite the old one without having to worry about data in the tables.

But I have noticed that I get problems when I split databases too, especially when tables are over networks, as it slows things down a bit.

So was just wondering what is the normal practice?

Thanks

As a general rule I split when I am close to release.

Once you give access to users you really have no choice but to split. All users must have their own copy of the front end (Normally located on their own machine) with a link to the backend on the server.

But I have noticed that I get problems when I split databases too, especially when tables are over networks, as it slows things down a bit.

Click to expand...

Can you explain this a little better. What problems do you get.

I assume you are using Access Front and Back ends, and not SQL Server or some other database engine.

RainLover said:

As a general rule I split when I am close to release.

Once you give access to users you really have no choice but to split. All users must have their own copy of the front end (Normally located on their own machine) with a link to the backend on the server.



Can you explain this a little better. What problems do you get.

I assume you are using Access Front and Back ends, and not SQL Server or some other database engine.

Click to expand...

Ok, thanks for the advice. Sounds like a split it is.

As for the problems, I made a post here:
http://www.access-programmers.co.uk/forums/showthread.php?t=277450

Basically after I split the database it caused a problem with the main form not refreshing /showing new data after a user entry. The underlying tables are updated via the code using the recordset method. At the end of the code I requery the main form to get the new data to show in the form detail. (It is a continuous form) But it doesn’t show unless I put some extra code in the OnTimer event to requery a few times.

When I keep the tables with the database I don't have this problem at all, it refreshes right away.

Splitting/not splitting should not affect functionality. There must be something else going on.

Are your forms BOUND to the tables or no.

If you are not using Bound forms then you will need code to save. If they are bound then the tables are updated automatically. NO code required.

When an application requires to be deployed in a multi-user environment, it is very convenient to split the database into two groups. The first contains the client application: database with forms, queries, reports, macros, and modules. The second contains only one or more database tables. The client application is distributed among all the workstations and connects to one or more databases stored on the server.

It is important to clarify that the term server is not very suitable for "Jet Engine and Access Database Engine" databases (mdb, accdb, etc) and it only refers to a shared folder on the network because no data processing task is executed on the server, but on the client.

By implementing the client – server design:



* All users share the same information.
*Maintenance tasks may be carried out in the application objects (forms, reports, etc,) without placing the database offline.



Perhaps the easiest method to implement this design consists of attaching all tables of the database server in the client application. For security and performance reasons, this method is not recommended for real server databases like a SQL