No announcement yet.

Sorry - not a SQL question but it's for Access

  • Filter
  • Time
  • Show
Clear All
new posts

  • Sorry - not a SQL question but it's for Access

    Please don't flame me as being in the wrong forum, as I don't see anything related to Access. I also don't do Access or SQL programming, but I've run into a problem that I can't seem to find an appropriate answer. I have a new client (a municipality) who has some billing software that resides on multiple Access databases on a Windows 7 workstation they call their Server. One of the databases frequently becomes corrupted (multiple times per week and sometimes multiple times per day), to where the user will do a backup after every couple transactions. The other databases don't seem to have any problems.

    I talked to the vendor about this (the municipality has already talked to them multiple times prior to contacting me), and I was told that any glitch in the network, even a single packet that gets corrupted or lost, will corrupt the database. This seems bogus to me, as I would expect TCP/IP to ask for a retransmission before the database would know any differently. I was also told by the vendor that Access creates a local copy of the database from the server, the user modifies the local database, and then the changes are replicated to the server. This seems very inefficient, and it even seems to me as if this could cause syncing issues if multiple users had multiple copies floating around the network. At the time, I also noticed the antivirus was scanning the databases, so I excluded the entire directory on both the workstation and Server, but the problem is still happening.

    Here's a little history. The network used to be a domain composed of a Win2K3 server and XP workstations, and this setup never had a problem. About 6 months ago, they upgraded to all Windows 7 machines in a workgroup, after which they had to upgrade to Access 2010. I don't see port errors on their switch for the problematic systems. To boot, we had really bad weather last night and this morning, so no one came in the office this morning. The user came in later today, and immediately off the bat when trying to open the database, it was corrupted. She hadn't even made a change.

    I've also checked for disk errors both with a chkdsk and looked at SMART reporting, but everything appears to be clean. I also don't see any Access problems in the event viewer. The server is idling and has 4 GB of memory installed.

    Does anyone have any thoughts what the problem might be? Thanks very much.
    Last edited by baskervi; 9th February 2011, 23:38.

  • #2
    Re: Sorry - not a SQL question but it's for Access

    Access is not a multi-user database, so this kind of problems are unfortunately by design. I've seen quite a few cases like this.

    The most reasonable solution is to replace Access with more suitable a database. For example, Sql Server Express Edition is free as-in-beer, even in commercial use. This requires some effort from the application vendor, so the solution is not instant.

    You could create a batch file that copies the MDB onto local hard drive for editing and launches Access. After Access quits, the file is pushed back to the server. More robust an alternative is to store the access DB into a version control system and checking the MDB file out as local copy and working with it.