Split Database

F

frank

There is an Access database on a network file server that multiple
users may access simultaneously.
The way it is now, the database is split - but the front and back ends
are both in the same network location.
Multiple users access the same copy of the front end component
simultaneously over the network.
I may be mistaken, but I thought that when splitting the database,
each client should have a local copy of the front end that is not
shared with other users.

Shouldn't each client have a local copy of the front end component on
their computer?

The back end component will always remain in the same network location
that is accessible to all users.
To add new users, do I just place a copy of the front end component on
their local computer and instruct them to use that to access the
database?
As usual thank you all for your help
 
D

Douglas J. Steele

Yes, each client should have his/her own copy of the front-end, ideally on
his/her hard drive. Doing it any other way means you get the _worst_ of both
worlds: the problems associated with sharing a front-end PLUS the problems
associated with transferring data across the LAN.
 
P

Paul Shapiro

frank said:
There is an Access database on a network file server that multiple
users may access simultaneously.
The way it is now, the database is split - but the front and back ends
are both in the same network location.
Multiple users access the same copy of the front end component
simultaneously over the network.
I may be mistaken, but I thought that when splitting the database,
each client should have a local copy of the front end that is not
shared with other users.

Shouldn't each client have a local copy of the front end component on
their computer?

The back end component will always remain in the same network location
that is accessible to all users.
To add new users, do I just place a copy of the front end component on
their local computer and instruct them to use that to access the
database?
As usual thank you all for your help

You are correct. Each user should have run own local copy of the front end.
If you want to make deploying updates easier, you can use a process where
the user copies the master front end from the server share and then executes
that local copy. You can either use a command file to do the copy and
execute, or look at Tony Toew's automatic front end updater:
http://autofeupdater.com/.
 
D

Daniel Pineault

Yes, you are correct.

The entire point of splitting a database is to locate the back-end on a
central server and then distribute individual copies of the front-end to each
user (which they setup locally on their own pc).

If your network is setup properly, then distributing your front-end is very
straight forward. Worse case scenario, you might have to relink the tables
if drive mapping isn't standardized (although UNC is preferrable).
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
A

Armen Stein

The entire point of splitting a database is to locate the back-end on a
central server and then distribute individual copies of the front-end to each
user (which they setup locally on their own pc).

Well, not the *entire* point. One of the main reasons for splitting
is to be able to make changes to the FE application offline, while
linked to a test or dev BE database, without disturbing or freezing
production data. This *one* benefit is still realized even if the FE
isn't distributed to local PCs. But it's still best practice to have
everyone run their own local copy of the FE for the other reasons.
If your network is setup properly, then distributing your front-end is very
straight forward. Worse case scenario, you might have to relink the tables
if drive mapping isn't standardized (although UNC is preferrable).

The best approach is to link the master copy of the FE *first* using
UNC, then distribute it to the users. They'll open it and it will
already be linked. If you do it right, users won't need to relink
unless they need to switch to a different BE database, like if you use
a Training environment.

If you want a more automated approach to relinking tables, you're
welcome to use our free J Street Access Relinker on our J Street
Downloads page: http://ow.ly/M56Q

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
F

frank

Of course things are seldom as they first seem.
I've learned the FE in this case is actually not a pure front end.
It has local tables which are not linked to the current BE.
It was split this way because of size constraints.
So now I have a "FE" that all users share.
Many of the tables are linked to the "BE", but not all.
My first thought is to Split the current Front End to give the Users a
local copy FE instead of them all sharing it.
I do not currently have the luxury of redesigning at this point, but I
may in the future. At that point the schema could be designed to
actually fit the data and usage, which it currently is not.
So I may end up with three components:

FE1 User Level Front End local to the Users computer
BE1/FE2 Intermediary Front End with several local tables and several
linked tables (~18Mb)
BE2 Current Back End with the largest tables (~300Mb)

This same concept would also be used on larger databases where the
largest back end is approaching the 2G limit
Has anyone tried to linking to tables that are already linked to a
third database?
I will be testing this offline to see so I will find out, but any
advice is appreciated.
 
T

Tony Toews [MVP]

frank said:
Has anyone tried to linking to tables that are already linked to a
third database?

I've never tried that but I rather doubt it will work. What's the
problem with putting your intermediary FE on the server as a second
back end?

Now are those data tables or temp tables? If temp tables See the
TempTables.MDB page at my website which illustrates how to use a
temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
F

frank

I've never tried that but I rather doubt it will work.     What's the
problem with putting your intermediary FE on the server as a second
back end?

Now are those data tables or temp tables?   If temp tables See the
TempTables.MDB page at my website which illustrates how to use a
temporary MDB in your app.http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/
Granite Fleet Managerhttp://www.granitefleet.com/

The Intermediary Front End is currently on the Network File server
where multiple users access it simultaneously.
I want to end up with a solution that uses a pure front end to
eliminate the problems with concurrent users.
Perhaps I will convert the intermediary front end to a second back
end.
My FE would then be linked to two different back end
Thanks
 
J

John W. Vinson

I want to end up with a solution that uses a pure front end to
eliminate the problems with concurrent users.

The only - ***ONLY***!!!! - way to do that is an unshared, individual,
personal frontend for each user.

Sorry, but that's the way it is.

You can have individual frontends in separate folders on a server (they don't
have to be on the user's actual desktop machine), but each user must have
their own frontend. The only thing you can share is tables, not forms, not
code, not queries.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top