Performance: About splitting Front-end (application) , back-end (d

[Guest]

Hello to you experts!

At work, all our database in the department are on MsAccess. We are now
experimenting some performance and, sometimes, reliability problems. So, we
are looking for solutions.

In the previous discussions, I have learned that our databases size and
number of users are still ok for MsAccess. Then, there is this notion of
Front-end/Back-end that I have read here about and that I would like to
understand correctly. Here are my questions:

On each drive of the PCs here, MsAccess is installed. Our databases are on a
shared drive. When some changes have to be done to a database that allows
entries from the users, we ask people not to access the DB while modules
blocks are added to the original copy which is on the shared drive. If I
understand the concept well, because the MsAccess created program is not
installed on each PC but just the MsAccess application itself, it means that
we are not creating our database using the FE/BE concept. Am I right?

Then, if the answer is yes, can we create a DB with FE/BE even if we do not
have an SQL database?

Finally, still if the last answer is yes, could you tell me how to proceed
to create a splitted DB so I could give the info to oour programmers here so
they could realize it?

Thank you very much for your help,

 

[Rick Brandt]

Hello to you experts!

At work, all our database in the department are on MsAccess. We are
now experimenting some performance and, sometimes, reliability
problems. So, we are looking for solutions.

In the previous discussions, I have learned that our databases size
and number of users are still ok for MsAccess. Then, there is this
notion of Front-end/Back-end that I have read here about and that I
would like to understand correctly. Here are my questions:

On each drive of the PCs here, MsAccess is installed. Our databases
are on a shared drive. When some changes have to be done to a
database that allows entries from the users, we ask people not to
access the DB while modules blocks are added to the original copy
which is on the shared drive. If I understand the concept well,
because the MsAccess created program is not installed on each PC but
just the MsAccess application itself, it means that we are not
creating our database using the FE/BE concept. Am I right?

Then, if the answer is yes, can we create a DB with FE/BE even if we
do not have an SQL database?

Finally, still if the last answer is yes, could you tell me how to
proceed to create a splitted DB so I could give the info to oour
programmers here so they could realize it?

Thank you very much for your help,

Make a copy of your database with whatever name you want to use for the back
end. In that new file delete all objects except the tables. Place this
fiel in the desired share locaton on your network. In the original file
(after making a backup) delete the tables and leave everything else as is.

Now (still in the front end file) go to "File - External Data - Link
Tables". In the resulting browse-for-file dialog navigate to the location
of the back end file you just created, but do so by going through "Network
Neighborhood - Entire Network" rather than using a mapped drive letter.
This will produce a link path that will work on all PCs regardless of how
they have network drives mapped. When the list of tables in the back end is
displayed press [ Select All] and then [ OK].

That's it. You now give all users a copy of the front end file to put on
their local PCs and they all have links to the common back end file on your
network. This is much better than having everyone open the same monolithic
file and one of the biggest advantgaes is you can make design changes to a
copy of the front end without having to kick users out. You just give them
a new modified front end when you are finished. Changes in the back end
file will still require that you get everyone out of the database before
making the changes.

 

[Kevin3NF]

Take a sample database and put it in the same location as the others. Open
it using the Access application and look under tools>>database
utilities>>database splitter

The concept of a FE/BE in Access means:

Back-end: An Access MDB file with nothing but tables, located on the server
Front-end: An Access MDB file with everything but the tables, located on the
users hard drive. This file has links to the back-end

That's the basics...play with your sample database and post back with any
additional questions. In a multi-user environment, splitting these
databases is a best practice to avoid corruption.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

www.experts-exchange.com - experts compete for points to answer your
questions

 

[Albert D.Kallal]

On each drive of the PCs here, MsAccess is installed.

The above is a good thing to point out. All you software you purchases such
as word, excel, ms-access etc. etc. is INSTALLED on EACH PC.

good.

Our databases are on a shared drive

You mean the data is on the shared drive. Of course, you just mentioned that
you always installed the application part on EACH pc. So, with ms-access,
the idea behind a front end, and back end is the SAME concept. What is means
is that you take out the application part. By application part, we simply
mean that part of the application that you developed. Forms, code, reports
etc. is then simply installed on EACH computer.

So, sure, you might share some word documents on folder that is shared on
the server. However, no one is suggesting that you install word on the
server.

Remember, the difference here is that ms-access is a developers tool.

Just like c++, or VB, or ms-access, when you use these tools to create
software, that software needs to be placed on each computer. Can you imagine
if one user in the building had a problem with ms-word, and then EVERYONE
comes crashing down? So, you can see the kind of risk, and in-stability if
you allow multiple users into your mdb, or even into your front end that you
make. (often, people split the database, but then turn around and share the
front end part on the server. Doing this simply means the person missed the
point). Further, if you got 5, or 6 users, then the application part is ON
each workstation. Why waste all that bandwidth, and network resources
downloading the application part each time? Further, each user has their OWN
copy that can fail, but not touch everyone's else. This also allows you to
work on the next great version of the software. Can you imagine if Microsoft
was making a change to ms-word, everyone had to stop using word, and go
home?

So, keep in mind there is a BIG difference between a application with code,
forms, a user interface etc. etc. For years and years you ALWAYS installed
these applications on EACH computer, and if make a application in c++, VB,
or ms-access, then you STILL must install this application part on EACH pc.

So, when you open that word document that is shared on the server, in fact
what happens is word LOADS ON YOUR LOCAL pc first, and then the application
consumes the data part that you put on the server.

So, splitting your code and application out of mdb file is what we call
building a front end.

In fact, there is a built in wizard to do this for you.

tools->database utilities->database splitter.

So, the "feature" or ability to split your database is built into ms-access.
As a general rule, no code needs to be changed (the exceptions are the use
of the seek command, and if you open recordsets forced as a table).

Further, those front ends should be a mde.


Last, but not least, you will find performance can be real slow if you do
NOT keep a persistent connection open. This is simply a performance trick to
restore performance to un-split levels. This trick simply means that your
startup code in the front end mush open a table to the back end...and keep
that table open...

The beauty of the above system is now you are free to development the next
great version of the application. Even better is that you can use the linked
table manager and link to a "test" copy of the back end. You can then run
delete code, and all kinds of stuff. Right now, you got a situation where
working on a the application is like working on a air plane (no one can use
the application). I mean, really, if you are testing some code that deletes
data...how can you test on live data? So, after you get your "next" great
version working with some new forms, code etc, you then re-link to the
production back end. At his point you them make a mde out of your front end.
And, the last step is to distribute that front end to your users. Most of
us developers do spend a few hours writing a utility to check the version of
the application,a nd then download a update. However, Tony has released a
very high quality solution.

You can find Tony's auto front end updater here:
http://www.granite.ab.ca/access/autofe.htm

You can also read more about splitting at Tony's site here:
http://www.granite.ab.ca/access/splitapp.htm

 

[Guest]

Thanks to all of you guys, you have really helped me!

Have a nice day!