Hi there
Some expert advice is required here guys (please) becuase I need to decide whether MS Access can do the job or do I need to move to SQL Server/MySQL!
My DB runs several action queries (delete/appends) on the client's main SQL DB and then there are about one hundred reports they can subsequently run after merging all the data from the action queries. That's it apart from a few parameters that they can enter on running the reports.
When they create the data it can take up to 5 minutes. This is simply deleting and appending data to the MS Access tables based on their username.
I've now moved to an ASP.NET front end so that the backend is multiuser (10 users) but I've run in to two big problems:
1) locking error which seems to be caused by two users hitting the same table at the same time with their action query. I've tried everything, connection string changes, record level locking and conversion to Access 2003
2) If one user is creating data (takes up to 5 minutes) and another user is simply opening or outputting a report it takes ages for his report to open because of other users "creating data".
Help becuase it's gonna be a hige job to move all the tables/queries to another DB Provider.
Thanks in advance
Dave
Some expert advice is required here guys (please) becuase I need to decide whether MS Access can do the job or do I need to move to SQL Server/MySQL!
My DB runs several action queries (delete/appends) on the client's main SQL DB and then there are about one hundred reports they can subsequently run after merging all the data from the action queries. That's it apart from a few parameters that they can enter on running the reports.
When they create the data it can take up to 5 minutes. This is simply deleting and appending data to the MS Access tables based on their username.
I've now moved to an ASP.NET front end so that the backend is multiuser (10 users) but I've run in to two big problems:
1) locking error which seems to be caused by two users hitting the same table at the same time with their action query. I've tried everything, connection string changes, record level locking and conversion to Access 2003
2) If one user is creating data (takes up to 5 minutes) and another user is simply opening or outputting a report it takes ages for his report to open because of other users "creating data".
Help becuase it's gonna be a hige job to move all the tables/queries to another DB Provider.
Thanks in advance
Dave
