Shared Access .mdb

[SuA]

A stupid question, but...

I inherited and old Access application that often got corrupted & whatnot. I
made a new .mdb and filled it with the still needed data & forms (weeded out
some old stuff).

In our current setup, we have a NAS and we use a shared network folder where
the old application resides. Our users open it directly from there. The old
application is set up as a replication master, but everyone simply opens it
from that single location.

If I drop my new application in the same location and use the same M.O., do
I need to set up a new replication? Everyone simply opens it from said
location, so it wouldn't replicate anything, unless I somehow fail to grasp
the concept here (I'm somewhat at a loss why the old is a replication in the
first place).

 

[SuA]

PS: It's a 2002-2003 database, if that matters. I'm not familiar with the
capabilities of Access 2007.

 

[Dale Fye]

SuA,

I'm not familiar with replication, it is designed to allow individuals to
disconnect from the primary data source (say your network), run the
application on their laptops while disconnected, then reconnect and
synchronize the data on both the laptop and the network database.

In your situation, since you are working on a network (I assume it is a
LAN), I would recommend that you use the database splitter to split your
application (commonly referred to as the "front-end" or "fe") and the data
(commonly referred to the "back-end", or "be"). Part of your historical
corruption problem is most likely because everyone was using the same mdb
file as both the frontend and backend. When that happens, and one of the
users computer locks up, or gets shut down without first closing the
application, it can cause database corruption. This occurs much less
frequently when you split the database and place a copy of the frontend on
each users machine. Allen Browne (one of the Access MVPs has a good
description of this on his web site http://www.allenbrowne.com/ser-01.html).

I honestly don't know how splitting the database will affect the
replication, if at all, so before you try this, I strongly recommend that
you copy your database and work with the copy.

HTH
Dale

 

[SuA]

the history of corruption was greatly due to inept handling, i bet...
somehow, the .mdb grew to a size of 1.12Gb while the actual data stored only
took 209,6Mb.

after i rebuilt the app myself (by inputing tables, queries, forms & reports
as well as their contents into a brand new .mdb) the program itself was
21.4Mb, when i added the actual data it was 230Mb.

i'm guessing this'll help with the corruption problems.

splitting into a real front end & back end would be adviced, but i dont
really know how easily this goes. i'll check it out.

originally, the idea was to investigate an MS SQL back-end, but the program
itself was not written by a programmer and as such it's not documented nor in
any way prepared for such a step. too much vba code in queries and whatnot.
porting it would likely take as much time as rewriting the whole app in .NET
+ MS SQL.

 

[Dale Fye]

You can split the front and backends into Access with almost no effort, and
it will not require changing a single line of code. The challenge will be
getting people to use their own copy of the front end, rather than going to
the network.

I usually create a very small application that checks whether the copy of
the frontend that is on the users computer (I actually put it in the C:\Temp
folder) is the same as the version on the network. If not, it deletes the
older version from their C:\Temp folder, and then copies the newer version
to their C:\Temp folder. Immediately after copying the file, I execute a
followhyperlink command that runs the file on their C: drive and then quits
the current Access application. This works great to keep ensure the users
have the most recent version of the program, and all I have to do is have
them copy the shortcut that I create to this file to their desktop and use
that to start the program.

HTH
Dale

 

[Albert D. Kallal]

splitting into a real front end & back end would be adviced, but i dont
really know how easily this goes. i'll check it out.

You have to lean this, or simply tell the people not to use your
application. You REALLY need to split your application if more then ONE USER
is going to use this at the same time.....

I explain splitting here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Tony Toews [MVP]]

You can split the front and backends into Access with almost no effort, and
it will not require changing a single line of code. The challenge will be
getting people to use their own copy of the front end, rather than going to
the network.

That can be handled by using the Auto FE Updater to give the users a
new copy of the FE MDE. On the backend data MDB you display a form
telling them that they can't change things and their only option is to
click on a button and get out.

On the FE MDE on the server you check to see if it's running from the
network. If so you then display a message accordingly telling them to
not run the FE from the server and then exit it for them.

However you really want to put the FE on each machine or place in a
user specific directory on the server. This will help avoid some
weird error messages when users are changing the same forms record
source, filters and such as well as corruptions. It is also much
easier to implement a new version of the database with changed
queries, forms, reports and VBA code.

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

the history of corruption was greatly due to inept handling, i bet...

No, the most likely cause of corruptions is users sharing the app MDB
forms and reports. Splitting the app and giving each user thier own
copy of the FE MDE is the first thing to try when trying to figure out
corruptions.

after i rebuilt the app myself (by inputing tables, queries, forms & reports
as well as their contents into a brand new .mdb)

splitting into a real front end & back end would be adviced, but i dont
really know how easily this goes. i'll check it out.

As others have said real easy.

originally, the idea was to investigate an MS SQL back-end, but the program
itself was not written by a programmer and as such it's not documented nor in
any way prepared for such a step. too much vba code in queries and whatnot.
porting it would likely take as much time as rewriting the whole app in .NET
+ MS SQL.

Wrong. Access is an excellent RAD tool. .NET and SQL Server are
not. It will likely take three or five times as long to create the
same app.

In addition the IT department will then look at all this, decide the
app should be rewritten, put the project on a three or five year
backlog, then do a requirements study which will take six months or a
year and then hire consultants at x hundreds of $$$s per hour to build
the fancy system. Which no one will like using because those
consultants never talked to the users to see what they needed. Just
management.

Now when you need a change done you will need to fill out a change
request form which will be approved by management up a few layers, an
estimate from the developers will have to be requested, (Note that by
now the consultants will have a whole new set of people to work on the
app who are completely unfamiliar with your app.) And then the
estimate will have to be approved and scheduled. By this time the
opportunity has gone and your competition has the business.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

In our current setup, we have a NAS and we use a shared network folder where
the old application resides. Our users open it directly from there. The old
application is set up as a replication master, but everyone simply opens it
from that single location.

Replication is only designed to replicate the data, that is tables.
It is not designed to replicate queries, forms, reports, macros and
modules.

(I'm somewhat at a loss why the old is a replication in the
first place).

Probably because someone didn't realize what replication was meant
for.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Dale Fye]

Tony,

I've seen notes about your auto FE uploader, but the first one I saw was
only days after I wrote my first one, so I never got around to checking
yours out. One of these days, I'll take a look. Is it an mde or is the
source code available for viewing in an mdb?

Dale

 

[Tony Toews [MVP]]

I've seen notes about your auto FE uploader, but the first one I saw was
only days after I wrote my first one, so I never got around to checking
yours out. One of these days, I'll take a look. Is it an mde or is the
source code available for viewing in an mdb?

No to both. It's a VB6 exe with about 4000 lines of code and a lot of
API calls.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[SuA]

Yeah, well the entire problem is that it's not "my" application to begin
with.

I'm just the poor sod stuck with putting out fires set ablaze by other
people.

You have to lean this, or simply tell the people not to use your
application. You REALLY need to split your application if more then ONE USER
is going to use this at the same time.....

By inept handling I meant the original designer & the person currently
taking care of the application, not the actual individual users.

As for the IT department, I'm the poor bugger stuck with making the
assessment in the first place. Started here 2 weeks ago...
We wouldn't need consultants though, we have a battery of in-house .NET, C#
and Java programmers (which makes it even harder to comprehend why people are
using that application in the first place).

 

[David W. Fenton]

we have a battery of in-house .NET, C#
and Java programmers (which makes it even harder to comprehend why
people are using that application in the first place).

Makes perfect sense to me. You have a lot of back-end and server
programmers, but nobody who can code a front end app for real users.