DB on network drive?

[imtheodore]

I have a db on a network drive and the performance is terrible, it runs fine on my local machine. How can I make it run better? The db is only 2 megs so I figured it would be ok, but since my forms have numerous queries within them, performance stinks.

 

[Rabbie]

Try looking at Optimisation in Access Help. You may find it helpful to add some indexes to your tables.

Also how busy is your network drive and how fast is the connection. If there is a lot of network traffic this will cause delays.

 

[GaryPanic]

stupid question but is the d/b split FE/BE ??

 

[anthonys]

The FE/BE question needs to be answered, it would also be useful to know if it accessed by multiple users.

 

[johndoomed]

What the guys men with FE/BE (Frondend/Backend) is that you need to split data and program. Have the DB containing the data on the network drive, and have the program db on the local clients.

 

[imtheodore]

The FE/BE question needs to be answered, it would also be useful to know if it accessed by multiple users.

It will only be accessed by 1 user at a time. But that user could be at a different PC every time they access it. If I could run it locally I'd be fine.

The issue is that my form has about 30 queries on it and it refreshes after every selection, there is about a 7 second lag on the network, but none locally.

 

[GaryPanic]

thats your problem

first of all split the D/B to F/E and B/E

reason
the front end does the work the back end stores data.


now depending on our company set up you can now instal a copy of the f/e on to the people who use it - or put a copy on the server (bad idea)..

if you have a small number of users then the copy each to a local Pc will resolve this issue

hint..
always try and keep the B/E as close to the Drive letter as possible

such as F:\datastore\xxx.mdb
and not F:\datastore\twinkletoes\keepdatasafe\xxxmdmb

 

[Zaeed]

I'll soon have to deploy a db over a network and had a few questions...

1: What is the result of having the database split, but the FE being on the server as well

2: Once split, can the FE be copied and pasted anywhere, or are there hard links holding it in a certain location (directory)

3: What access rights do the users require to the network drive the BE is stored on

My situation is that the database will be accessed by a large group of people, spread over a large site. It would be a nightmare to go and copy the FE to each individual computer, so i was hoping to maybe have the FE accessable on a network drive, OR in the initial roll out, have the FE on a network drive and give all users instructions to copy the FE to their computer.

Any help with this would be great thanks

 

[boblarson]

Perhaps this article would be good to read for this problem:

http://office.microsoft.com/en-us/access/HP051874531033.aspx

 

[Zaeed]

thanks bob, there's some good info there, but it doesn't really answer my question

 

[boblarson]

1: What is the result of having the database split, but the FE being on the server as well

A copy of the frontend should be on EACH user's computer. If you don't, then you will suffer the ill effects of
a. slower response times as Access has to transfer ALL objects across the network, including for rendering the forms and reports.
b. much, much greater chance of corruption of the frontend file.
(the link I provided should have specified all of that)

2: Once split, can the FE be copied and pasted anywhere, or are there hard links holding it in a certain location (directory)

The frontend can be placed anywhere, but the users do need to have Read/Write/Delete rights to the folder so the locking file will work.

3: What access rights do the users require to the network drive the BE is stored on

As mentioned the users need Read/Write/Delete rights for the ldb file to be able to be deleted by the database when the last user shuts their frontend and the backend terminates itself.

My situation is that the database will be accessed by a large group of people, spread over a large site. It would be a nightmare to go and copy the FE to each individual computer, so i was hoping to maybe have the FE accessable on a network drive, OR in the initial roll out, have the FE on a network drive and give all users instructions to copy the FE to their computer.

And that is exactly the reason I wrote my Frontend Auto Update Enabling tool, so that the frontends could be automatically updated when I changed versions of the master.

How it works is like this:

1. I make changes to my master which is located on the network in a location able to be accessed by users (Read/Copy rights only necessary on that folder).

2. I change the version numbers in the two tables (one in the frontend and one in the backend).

3. When users open their frontend again, they are greeted with a message saying that their frontend is out of date.

4. The database (FE) will then close

5. Then it will delete itself

6. Then it will copy the new file from the master location

7. And finally it will automatically reopen for the user.

You can find the tool, and documentation, here:

http://www.btabdevelopment.com/main/AccessTools/tabid/78/Default.aspx
Any help with this would be great thanks[/QUOTE]

 

[Zaeed]

Cheers Bob...

one more question. If the FE is in location A, can it be copied and pasted into Location B without changing anything? i.e. Its located on F: drive and all users copy the file from that drive to their local machine...

 

[boblarson]

As long as the backend location is the same, it doesn't matter whether the frontend is in location A or Location B. If they get a copy from the F drive then they open the frontend, it will still go to the right backend.

But the user has to have rights to that location.

 

[Zaeed]

great, that significantly reduces the prosteria pain for me