2

I am attempting to find a problem with a poorly coded vb.NET application (running on vista with database on Win Server 2K3) that randomly seems to freeze and crash when in use. Most frequently this is when used by more than one user at at time (2 to 5 users max). This program has no back end server, the clients just connect to an MS Access 2k database and do their thing. This program has been running successfully for over 3 years (somehow) with a heavy workload, and the problem seems to be recent. I am reasonably certain the issue is with the access database, but I am running into problems finding details on how MS Access handles simultaneous read/writes (access is via vb.NET library). Does access have built in protocol to handle this? Does anyone have any ideas why the problem would emerge after 3 years, and with a lighter workload?

Note: I do not have access to source code

Standard windows updates have been installed, and no new access updates have been applied

JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
mark
  • 21
  • 2
  • You've been lucky. You asked how Acess handles simultaneous read/writes? It doesn't. Two people try to write to the same region at the same time and you will get corruption. – Joel Coehoorn Apr 01 '11 at 03:55
  • So clients are launching the exact same instance of the app simultaneously across the network and you only now have gotten corruption? Someone must have freed the leprechaun. – Thomas Apr 01 '11 at 04:24
  • I am not getting a corrupted database so far as I can tell. The only real problem I am having is with the clients crashing. This company is small enough that I doubt data is ever been simultaneously written in the same region. I do know that the data is being read simultaneously though. The very act of launching a client is querying the database. – mark Apr 01 '11 at 04:39
  • Have you tried compacting and repairing the MS Access backend? A heavily fragmented Access backend tends to lead to problems with locking ... – Mitch Wheat Apr 01 '11 at 04:54
  • Yes, I did compact and repair the DB to no effect. – mark Apr 01 '11 at 05:01
  • how does the lib connect to access? odbc /oledb or something else? if it connects via odbc you can enable sql-logging to a file. I donot know if this option exists for oledb – k3b Apr 01 '11 at 05:19
  • it uses an ADO, but i don't have access to the source, so IDK how I could enable logging anyway. – mark Apr 01 '11 at 05:32
  • 2
    How can you tell it is extremely poorly coded if you don't have access to the source code. – Craig T Apr 01 '11 at 06:47
  • How does it know how to connect to the DB? It it always in a fixed location? Parameter file? etc. – BIBD Apr 01 '11 at 15:13
  • First of all I know it is badly coded because of its architecture. e.g. simultaneous user db access but no client-server relationship (just client), and the fact that it uses 300MB ram to do almost nothing. It is most likely the db because the environment and program have not changed for 3 years, and the problem is just showing up now. Obviously the program is ultimately at fault, but the db is the current issue. To answer CodeSlave the db is simply on a windows network share and accessed by the client from each terminal. – mark Apr 01 '11 at 16:11
  • You might try creating a new database and importing the old data. The reason for that is that certain kinds of corruption can be hidden and are not cleaned up by a simple compact. My guess is that you've got some kind of exotic corruption that has been caused by some change in the software ecosystem that interferes with Jet's/ACE's ability to write to the file. – David-W-Fenton Apr 01 '11 at 20:27
  • I attempted a new db + import to no effect. – mark Apr 02 '11 at 04:44
  • 3
    Joel is wrong. The Access locking system quite niely handles multiple users updating records in the Access database. Indeed his usage of the term region, which doesn't exist in Access terminology, shows his ignorance. – Tony Toews Apr 02 '11 at 19:34
  • thanks tony, you are definitely right. I found everything i needed in the link provided by James Anderson. jet 4 still sucks tho. :) – mark Apr 04 '11 at 04:47

1 Answers1

4

Have a look at this post. Its the clearest explanation of Access concurrency issues I have seen. msaccess-mdb-concurrency

Community
  • 1
  • 1
James Anderson
  • 27,109
  • 7
  • 50
  • 78