wthornton59
New Member
- Joined
- Aug 17, 2009
- Messages
- 23
I have a 2 tier, Excel front end / Access back end, database set up that I inherited when I accepted my current position and I’m having a problem with it that I haven’t seen before. The set up is:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
<o> </o>
1. The end user does their work in Excel which is then updated to the Access database.<o></o>
2. The Access database resides on a server that is mapped to the user’s desktop computer.<o></o>
3. The Excel spreadsheet resides in their All Users desktop.<o></o>
4. Excel works with Access through VBA \ SQL and uses drive mapping. <o></o>
5. There are 12 iterations of this combination.<o></o>
6. At each site there are between 3 and 20 users.<o></o>
<o> </o>
The issue is that there are times, especially where there are more users accessing the database, when 2 users will click the button to refresh their data at the same time and the database doesn’t know how to handle it. Access gives an error and requires a Compact and Repair to get it to function again. While this doesn’t take a large amount of time to do, we are experiencing a growth in the number of clients in our offices and it is happening more often. <o></o>
<o> </o>
I know that Access can handle more than 20 users at a time but I have also noticed that with more than 20 users Access starts to act really funky and I am not able to move from using Access to say SQL Server, MySQL or another database.<o></o>
<o> </o>
My questions are:<o></o>
<o> </o>
1. Is there anything I can do to get Excel and Access to work together better where this won't happen?<o></o>
2. Would using UNC path or other addressing method versus drive mapping to allow Excel and Access to communicate help?<o></o>
3. Are there any settings or options that needed to be set or that I could modify that might take care of it?<o></o>
4. Anything I haven't thought of to check?<o></o>
<o> </o>
<o> </o>
Thanks in advance for your assistance.<o></o>
fficeffice" /><o></o><o
> </o>1. The end user does their work in Excel which is then updated to the Access database.<o
></o>2. The Access database resides on a server that is mapped to the user’s desktop computer.<o
></o>3. The Excel spreadsheet resides in their All Users desktop.<o
></o>4. Excel works with Access through VBA \ SQL and uses drive mapping. <o
></o>5. There are 12 iterations of this combination.<o
></o>6. At each site there are between 3 and 20 users.<o
></o><o
> </o>The issue is that there are times, especially where there are more users accessing the database, when 2 users will click the button to refresh their data at the same time and the database doesn’t know how to handle it. Access gives an error and requires a Compact and Repair to get it to function again. While this doesn’t take a large amount of time to do, we are experiencing a growth in the number of clients in our offices and it is happening more often. <o
></o><o
> </o>I know that Access can handle more than 20 users at a time but I have also noticed that with more than 20 users Access starts to act really funky and I am not able to move from using Access to say SQL Server, MySQL or another database.<o
></o><o
> </o>My questions are:<o
></o><o
> </o>1. Is there anything I can do to get Excel and Access to work together better where this won't happen?<o
></o>2. Would using UNC path or other addressing method versus drive mapping to allow Excel and Access to communicate help?<o
></o>3. Are there any settings or options that needed to be set or that I could modify that might take care of it?<o
></o>4. Anything I haven't thought of to check?<o
></o><o
> </o><o
> </o>Thanks in advance for your assistance.<o
></o>
