mlcktmguy
asked on
Access Performance and SQL server
I developed an app in Access 2003 for a client several years ago.
Over the years the company has grown and the number of concurrent users increased to above 20 and sometimes more.
The backend database is an MDB. The size of the MDB is only about 250MB so nowhere near the Access limit. I have many clients with backend MDB's larger that this.
System response is becoming an issue, especially with certain forms. The client has very state of the art hardware and networking.
The client is asking me to make it better from the application side. My first thought is to move to a SQL Server. I have heard and read many times that the MDB is not designed to support concurrent usage by this many users. I have also read that SQL Server is a much better solution as the number of users with concurrent access grow.
I'm sure the client is open to this solution but before proposing it I want to make sure my recommendation is valid.
The last thing I want, is to propose the solution, have them go thru the time and expense to implement it and end up with no improvement.
So I'm just looking for experiences of any EEer's that have gone thru a similar transition and hearing what kind of performance improvements were realized, or not.
We also plan to transition the application to Access 2010 at some point. However, I don't see that providing any performance benefit. Am I correct about that.
Over the years the company has grown and the number of concurrent users increased to above 20 and sometimes more.
The backend database is an MDB. The size of the MDB is only about 250MB so nowhere near the Access limit. I have many clients with backend MDB's larger that this.
System response is becoming an issue, especially with certain forms. The client has very state of the art hardware and networking.
The client is asking me to make it better from the application side. My first thought is to move to a SQL Server. I have heard and read many times that the MDB is not designed to support concurrent usage by this many users. I have also read that SQL Server is a much better solution as the number of users with concurrent access grow.
I'm sure the client is open to this solution but before proposing it I want to make sure my recommendation is valid.
The last thing I want, is to propose the solution, have them go thru the time and expense to implement it and end up with no improvement.
So I'm just looking for experiences of any EEer's that have gone thru a similar transition and hearing what kind of performance improvements were realized, or not.
We also plan to transition the application to Access 2010 at some point. However, I don't see that providing any performance benefit. Am I correct about that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks you. Based on some of your responses I am very interested in any fundamental changes I can make in the DB to improve performance. I realize this is a separate issue/questions so I created another question:
https://www.experts-exchange.com/questions/28348583/Acsess-Backend-DB-efficiency.html
Please look at it and provide any suggestions, SOP's, corrections or techniques for making an Access application more efficient.
One thing I asked in the new question was how to ensure a constant DB connection, as was suggested above.
https://www.experts-exchange.com/questions/28348583/Acsess-Backend-DB-efficiency.html
Please look at it and provide any suggestions, SOP's, corrections or techniques for making an Access application more efficient.
One thing I asked in the new question was how to ensure a constant DB connection, as was suggested above.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Armen,
Didn't realize you hang out here on EE.
Enjoyed the piece you posted on Linked In. Hope you are making good use of that keyboard!
Dale
Didn't realize you hang out here on EE.
Enjoyed the piece you posted on Linked In. Hope you are making good use of that keyboard!
Dale
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the clarification ACPerkins.
ASKER
Thanks for all the great advice and information. I split the points among responders
"But if the query contains VBA functions"
I should have said:
"But if the query contains VBA or user defined functions "