Re: [CF-Database] Access database on a network

[<paul@fennell.co.uk>]

First let me say access while is ok really should not be used in a heavy
load environment
I'm sure many others on this list can explain it better than I :)

Rex if you think you really are stuck with using access as a network
database and are using multiple forms
from it internally I think you should look at dropping the forms and using
an intranet app instead for data input
rather than using access forms -

That way you can work around the issue of database locking and having a
nightmare of merging databases'
You situation is not idea but you clearly know that already.

what you should do first though is split your database. Access has a wizard
that will do that. This removes all the data tables and places them in
separate MDB file which you have part done already however ! This is your
back end and that file should be placed on the network share.

The front end, which contains the forms, reports, etc, should then placed
LOCALLY !! on each computer.
Don't place it on the front end ! it causes to much heart ache in the long
run trust me.  you can update the users forms and reports stuff by version
management separately and its a lot safer that way to boot !

You then maintain a development copy of the front end, either in a separate
folder or locally.
This copy is where you do all your changes. When you are ready to place the
changes into use, then you copy the front end to overwrite the production
front end. not ideal but close to what you are doing all ready

also Goto Tools > Options on the file menu. Then to the advanced TAB
Ensure default open mode is set to shared and default locking is set to
edited record
(or no locks depending on the database) and check that the Open databases
using record locking is also ticked.
this might also address you particular problem

Hope my ramblings help



----- Original Message ----- 
From: "Rex A." <rex@pgrworld.com>
To: <database@list.cfdeveloper.co.uk>
Sent: Wednesday, December 22, 2004 10:13 PM
Subject: [CF-Database] Access database on a network


> >----------------------------<
> >- see footer for list info -<
> >----------------------------<
> We have an Access database on a file server.  When one or more people
> have it open, the Admins/programmers cannot save changes to anything:
> forms, queries, tables.
>
> Right now, what I do is we have our own individual copies of the Access
> MDB, and we can make changes/additions to our local copy.  At the end of
> the day, when everyone's gone and is not using the MDB, we have a
> meeting, and one by one update the server copy of the mdb with all our
> local copies.  Tedious, but gets the job done.
>
> How do you suggest I go about making changes to the database?  One
> successful implementation we did was to use ColdFusion for the forms,
> and now the MDB just holds the tables, but this was for ANOTHER access
> mdb.
>
> We have over one hundred forms and reports on the MDB so we cannot just
> abandon our Access solution.
>
> Another problem is that the Access mdb runs SOOOOO SLOOOOOW, and one of
> our admins said that it is because Access is not really a Client/Server
> product.  What it does is it creates a "local instance" of the database,
> so since the database is about 135MB, the whole 135MB traverses the
> network from the server to the client.
>
> So what we did was have an MDB, MDB1, that houses all the tables, and
> another MDB (MDB2) that houses all the forms, and we link the tables in
> MDB2 to MDB1's tables.  But we did some time runs, and it produced
> minimal effect.
>
> I'm sure that a lot of people have Access databases that are being used
> by many people.  What are the best practices for this?
>
> Thanks!
>
> Rex A.
>
>
>
> --
> For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
>
> --
> CFDeveloper Sponsors:-
> >- Hosting provided by www.cfmxhosting.co.uk -<
> >- Forum provided by www.fusetalk.com -<
> >- DHTML Menus provided by www.APYCOM.com -<
> >- Lists hosted by www.Gradwell.com -<
> >- CFdeveloper is run by Russ Michaels, feel free to volunteer your
help -<


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.822 / Virus Database: 560 - Release Date: 22/12/2004