I have a VB application which uses an Access database file as a back end. It is a transactional database (a point of sale system) and the mdb is shared over a network with clients running the VB accessing and transacting data in the mdb.

I have a requirement for a user interface which is not delivered by the VB application so i've constructed this using Access forms in a separate mdb with linked tables to the main mbd on the network. The main mdb has about 30 tables and the 4 main transactional tables currently have about 100,000 records or so each.

This has lead to a few problems:
1. data access to the shared mdb is extremely slow
2. I frequently receive error message saying too many databases open

The data view on the MS Access forms needs to be real time, i.e. records are added every few seconds, so there is a need for an updated feed. I have tried the following methods but with limited success:
1. linking from the main shared mdb only those tables that are regularly updated. Problem: very slow and error message of too many databases open


2. create identical tables in another database that is not shared and use an append query running on a timer loop to update these tables from the main shared networked mdb. Problem: considering the size of the database, it takes a few seconds (up to 10 seconds) for the query to run and seeing that records are added to the database every 2 seconds or so I would need to run this timer every second. This freezes both the client computer and the server.

Is there any solution to this? My last resort is to hard code the forms directly into the shared mdb and have the users access from there, i.e. not using a split mdb with a locally hosted front end and the shared networked mdb backend. I have tested the interface with the forms and tables in one mdb and it works just fine with none of the errors mentioned above.

Please help me...