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
ffice
ffice" /><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
>




<o


1. The end user does their work in Excel which is then updated to the Access database.<o


2. The Access database resides on a server that is mapped to the user’s desktop computer.<o


3. The Excel spreadsheet resides in their All Users desktop.<o


4. Excel works with Access through VBA \ SQL and uses drive mapping. <o


5. There are 12 iterations of this combination.<o


6. At each site there are between 3 and 20 users.<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


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


My questions are:<o


<o


1. Is there anything I can do to get Excel and Access to work together better where this won't happen?<o


2. Would using UNC path or other addressing method versus drive mapping to allow Excel and Access to communicate help?<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


4. Anything I haven't thought of to check?<o


<o


<o


Thanks in advance for your assistance.<o

