Home / server / Questions / 317342
Accepted
DKNUCKLES
Asked: 2011-10-01 10:30:44 +0800 CST

Slow access to Access Database after Server Migration

  • 772

I'll preface this question with the fact I know about This question but unfortunately it was no help to me as "don't run the database over the network" is just not an option.

Last week we migrated our file server from Server 2000 to a Server 2008 R2 Standard x64 Virtual Machine. We have a database that provides contact information for stakeholders and business partners. This is a proprietary database written in house by our database analyst. Since we migrated the server, this database has been running much slower than normal.

That said, I've been looking for the cause for the past week as this is an important database for us. We are moving toward using another system, but I'd like this issue resolved with this sooner rather than later.

Relevant Information

  • Linked tables have been re-linked to reflect their new UNC address and the database compacted

  • There is currently no anti-virus running on the server

  • All client anti-viruses are set to not scan network drives

  • We have made an antivirus exception for msaccess.exe for testing purposes with no luck

  • I tried dropping the firewall on the file server with no luck

  • I haven't noticed any issues with file access (in fact, most of my employees have said they've noticed an increase)

I would love to hear any suggestions as to why people would think that Server 2008 R2 is slowing down the database.

windows-server-2008 microsoft-access

2 Answers

  1. Plain and simple, MS Access is a horrible product, something that MS should have never developed or sold. That said, for whatever reason, you're stuck using it (for now). You don't want to hear this, but the real answer is to:

    1. Stop using the database over the network.
    2. Stop using MS Access and use a real database server.

    Migration paths between Access and MSSQL are available, and are not all that difficult if your dba knows what he's doing. You can even still use MS Access as the front end, connecting to ODBC sources on the SQL server.

    • 3
  2. Best Answer

    At the risk of being tabu and answering my own question (and being reprimanded for continuing to use MS Access over the network) , we have been successful in troubleshooting the issue.

    The issue was resolved by doing the following :

    1) All tables were re-linked to be the UNC path and NOT a path relative to the mapped drive (ie \\server\share\database.mdb and NOT T:\database.mdb)

    2) Code of the database was recompiled

    After completing the above we noticed a dramatic speed increase in the database lookups and functionality.

    • 2