Issue using Excel 03 & Access 03 together


New Member
Aug 17, 2009
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:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
1. The end user does their work in Excel which is then updated to the Access database.<o:p></o:p>
2. The Access database resides on a server that is mapped to the user’s desktop computer.<o:p></o:p>
3. The Excel spreadsheet resides in their All Users desktop.<o:p></o:p>
4. Excel works with Access through VBA \ SQL and uses drive mapping. <o:p></o:p>
5. There are 12 iterations of this combination.<o:p></o:p>
6. At each site there are between 3 and 20 users.<o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
My questions are:<o:p></o:p>
<o:p> </o:p>
1. Is there anything I can do to get Excel and Access to work together better where this won't happen?<o:p></o:p>
2. Would using UNC path or other addressing method versus drive mapping to allow Excel and Access to communicate help?<o:p></o:p>
3. Are there any settings or options that needed to be set or that I could modify that might take care of it?<o:p></o:p>
4. Anything I haven't thought of to check?<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Thanks in advance for your assistance.<o:p></o:p>

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Latest member