Re: Access database stability issues

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Todd wrote:
When I upgraded from SBS 2003 to SBS 2008, I began having Microsoft Access Database stability problems. We have a front-end Access database file and the back-end Access database file with all the content. The back-end file is located on the server while the front-end is installed on 3 different computers in our office.

Once we upgraded to SBS 2008, each client computer began experiencing occassional crashes due to the front-end losing its connection to the back-end file (time-out while accessing the data). It all seems quite random as we couldn't identify a pattern. Also, one person's front-end would lose the connection while the other two were just fine. I would have expected them to all crash at the same time.

I've tried removing all AV protection, but this didn't help. I finally got frustrated enough that I moved all of the back-end files to a different computer. This is not a server, but just a client machine. Since moving the files off of the server, we have had zero crashes. It is as stable now as it has ever been.

Unfortunately, keeping the files on this other computer isn't a great long-term solution for me because it poses difficulties with backup and I'm guessing it won't run as efficiently.

So, given this information, does anyone have a suggestion for me? Any idea what would cause the crashes???

Move the data to an SQL server. This will avoid data corruption (which you've clearly managed not to suffer so far) when write access to a shared file is interrupted. If you don't have SBS Premium, I believe the free SQL Server Express should run on SBS2008. If you have an always-on workstation, MySQL would be an option, but I wouldn't run it on a Microsoft server. MySQL has simple built-in backup options, which can be run from a timed script.

This is almost certainly not a problem with Access, but Access is unusually sensitive to lost network connections, and tends to throw its toys out of the pram at the slightest excuse. Other shared-file 'databases', such as some accounting software, will also be vulnerable to damage from the same cause, which I would assume to be some problem with networking. Access has a few funnies when used with an SQL server backend, but they are minor and do not involve database corruption. It does run very much faster, as it is no longer necessary to copy complete tables across the network, only query results.

Of course, the underlying cause needs to be investigated. Best of luck, intermittent problems are a real pain. My feeling would be to check whether more recent drivers were available for the network card, and if not, try another (Microsoft W2008 supported) network card, at least to confirm if the problem is there. And it never does any harm to have a spare network card, which you've already tested.

It's probably worth checking the DNS setup of the server and workstations before doing anything else, as that costs nothing but a little time, but I would not expect that to be the problem here. Near-random disconnections of client-initiated sessions say 'Ethernet NIC' to me, assuming of course you're not on wireless. If you are, look no further and start laying cables.

--
Joe
.

Relevant Pages

  • RE: Add table to split database
    ... Ensure no one is working in the db (no ldb back-end file) and then you take ... if offline. ... Do not keep a copy on your server or else people may do work ... distribute a new version of your front-end to all your users. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How to do this?
    ... move the backend tables to the SQL Server and link the sql server tables in ... > typically one things of an Access database as either a relational database ... when you speak of splitting a frontend to an IIS ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: How to do this?
    ... would return to questioning if a basic IIS server facing the public really ... to backaway from the technology a little bit to look at the business plan ... conditions on trying to front a website on an Access database that you have ... You would have IIS load of the SBS, ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: To Web or to synchronize? That is the question.
    ... putting together an Access database and ASP, ... Jet replication is a really great tool if you have laptop users ... Server, and just host the app on a Windows Terminal Server in the ... the ASP solution and Lyle's SQL ...
    (comp.databases.ms-access)
  • Re: Slow response from Windows 2003 web server
    ... I am not sure which of these entries in the HTTPERR Logfile concerns my ... This is my simple test ASP script, ... What excatly do you mean by "Access Database - change in Windows Server ...
    (microsoft.public.inetserver.iis)