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>

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...