General discussion

Locked

Linked tables on network in Access 2000

By swild ·
I'm a newbie in the world of VBA. I love working with it, but have run into a problem. I have a .mdb data file out on the network and a .mdb file (user interface with forms and report, linked to data file)on the client machine. The connection is extremely SLOW. I was wondering if anyone else has run into this and what I can do (with or without code) to pick up the performance.

This conversation is currently closed to new comments.

12 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Linked tables on network in Access 2000

by Bob Sellman In reply to Linked tables on network ...

I have many Access applications running over networks with front end (application) databases with tables linked to a back end "data" database.

Although you don't mention the speed of the network connection, running through a network will always be slower than if you had both databases on the workstation. Access transfers all data (for a data request) to the workstation, where the data is then selected and sorted, rather than the selection (SQL processes) being performed at the server and sent to the workstation.

You might try a few things, however, to help performance.

1. Review what columns are indexed and add indexes appropriate to the sorting and multi-table queries used for your reports. Indexes to help queries run faster can be a real help. Queries should be in your application database.

2. If you are doing any temporary processing into "report calculation" tables to prepare reports, locate those in your local application database, rather than your data database. This also will help reduce the need to compact the data database as frequently, although it will then be necessary to compact the application database periodically.

3. Be sure to compact the data database at appropriate intervals (depends upon the use of the database). Nothing slows things down more than lots of wasted space for deleted records.

4. Get the network connection optimized for speed. I still have applications running slower after some newer PCs are installed, apparently due to some workstation software problem.

Good luck.

Collapse -

Linked tables on network in Access 2000

by swild In reply to Linked tables on network ...

Although I do appreciate the response, this didn't help my situation. I have a multi-user database on a network. I'm linking "user-interface" .mdb files on the client machine to one central "data" .mdb file on a server. I can take almost a minutefor the data to transfer from the data to UI! Even in a test database with very little data in it. I'm not sure this is a network issue. Other data transfers very fast on our network. Thanks again for the response.

Collapse -

Linked tables on network in Access 2000

by swild In reply to Linked tables on network ...

I have a multi-user database on a network. I'm linking "user-interface" .mdb files on the client machine to one central "data" .mdb file on a server. I can take almost a minute for the data to transfer from the data to UI! Even in a test database with very little data in it. I'm not sure this is a network issue. Other data transfers very fast on our network.

Collapse -

Linked tables on network in Access 2000

by Alias_Ales In reply to Linked tables on network ...

I've had a similar problem and we have a fast server and LAN. I've found that if I want to edit forms and especially VB code, I need to copy the mdb to my machine first. Without boring you to death with all of my problems my fix was to move the database to the Intranet server. Without too much work (or time) you can connect Access to IIS. You'll never have to update your users front end again and you'll be pleased with the performance.


We have a mixture of new and old machines; older machines some having been upgraded from Office 97. My machine has all of the latest service pack and libraries great for developing new VB apps but I got burned when I found I needed to update all Access users with MDAC 2.5. Too many headaches with form problems and slow speed led me to try moving to a web server. I'm glad I did.

Collapse -

Linked tables on network in Access 2000

by swild In reply to Linked tables on network ...

Poster rated this answer

Collapse -

Linked tables on network in Access 2000

by irisolutions In reply to Linked tables on network ...

There are several steps you can take. The first is obviously to upgrade the speed of the network, but that is often not possible due to costs or availability from 3rd party providors. The next step you can take is optimization of all filters and queries behind the forms. There are some good articles on this type of optimization on the microsoft developers site, msdn. Another option is to open forms in memory without visibility and apply filters on startup. This makes initial start up slower (but people can startup in the morning and then get coffee - and at least the work they are doing is faster). The last thing you can look at is database replicationm (Access supports a replication utility) where the data file is replicated on local servers and refreshed periodically. Depending on the number, location and requirements of information availablity, you should be able to find the optimum # of times for refreshing the datafiles.

Collapse -

Linked tables on network in Access 2000

by swild In reply to Linked tables on network ...

Thanks for the reply. Perhaps I wasn't clear enough in my question. When I said that even my test database (with very few records) still takes a minute to open, I meant that the test database had FIVE records in it. I don't think it's a network issue at that point. Even the slowest of networks shouldn't take a minute to transfer 5 records. I do have the queries pretty tight. It's the slowness on startup that is my concern. I don't want them to have to wait forever when opening the database. I have used replication in the past, but that isn't the most efficient when you have a multi-user situation where people need to see other entries on a more timely manner. Thanks for the response anyway. It is appreciated.

Collapse -

Linked tables on network in Access 2000

by delamarel In reply to Linked tables on network ...

Why not convince your company to get Citrix Metaframe? Then you can just keep your front end on the server along with your database (all the processing takes place on the server and NO raw data will go across the network to the workstations for processing) ......... all that will go across your network to the Users are screen paints ~ We are using it and it really is as fast (or faster) than if you had the mdb's on your local hard drive) ~

Collapse -

Linked tables on network in Access 2000

by swild In reply to Linked tables on network ...

Poster rated this answer

Collapse -

Linked tables on network in Access 2000

by knowlimits In reply to Linked tables on network ...

We have had the same problems that you have described. Using Access on the network slowed down to a crawl when more than two people accessed the program. Unfortunately, Access is not a network friendly application. It works well on the client side but it needs a database server to work over the network. Regardless of where the mdb file is the Access program is located on the client machine, and is not run by the server. Every query, every keystroke, every time a record is changed, the mdb file gets recreated and replicated in a tmp file on you machine and then uploaded to the shared drive. Access is NOT a database server Client/Server app.

We were out of luck with our particular database, since there is no other like it that we can use for our needs, and I am not a programer. We have a very fast switched 100/1000 network, and still had problems.

We solved the problem by installing Windows 2000 server as a terminal server and connected the users (16 and 32 bit clients) to the server with the built-in client software that 2000 provides. Now access only runs in one area, the server, not on the network.

The IIS option works as mentioned in a previous post.

The only other option is to convert the server side mdb file to an client/server SQL solution. SQL 7 is a good option for access clients.

Back to Web Development Forum
12 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums