Getting Errors when Access DB gets large 
Author Message
 Getting Errors when Access DB gets large

I have an VB6 App using Access97.  After a while, as with any access db, the
gets large after a few days of continuous adding and deleting.  Problem is
erroneous errors
begin to occur as the DB begins to grow.  Compacting the DB clears this
problem but
my customer finds this annoying.

Has anyone else had this error?

thank you in advance.


Mon, 04 Mar 2002 03:00:00 GMT  
 Getting Errors when Access DB gets large
You didn't mention which errors you are getting.  If you are running
on Windows 95 or NT platforms (workstation and/or server), the
following may be part or all of the problem.  I took these from


Subject:  Corrupt Access databse
Date:  Friday, September 10, 1999 12:06 PM
Newsgroup: microsoft.public.vb.database

  I've created an application in VB 6.0 using DAO to access an MS
 database using JET. The application shares an Access database with 5
 (All using the same interface). The database contains 15 tables and
 largest table contains 600 records. Initially, the database resided
on one
 of windows 98 stations while the other stations shared it. When the
 application is run from the computer where the database resided, it
 fine. when any other station uses the database it starts slow but
 functions OK. We moved the database to an NT server, this time all
stations work
 fine but the database keep getting corrupted. I keep repairing the
 every day, and this happens when a recordset is being view (No
editing or adding
 is taking place). I cannot figure out what is causing this. Any
 suggestions?. Thank you in advance.


Subject: Re: Corrupt Access databse
Date: Friday, September 10, 1999 2:39 PM

It sounds like the problem with Opportunistic Locking. You can set
flags in the registry, and like magic your database will run smooth.
the Info that follow:

good luck

----- Original Message -----

 We have an application that was converted from Access 2.0 to Access
 We leaved the back end in the Access 2.0 format. Now we are getting
 with customers that have Windows NT environments. There the database
 corrupted now and then (1 to 2 times per day, but without any
 systematic). The users must then all leave the application, repair
the back end db,
 and work on. One company has made a detailed protocol with daily
errors per
 user. We have studied the protocols but could not find any point of


Sent: Freitag, 12. M?rz 1999 06:15
Subject: Re: Access db gets corrupt in network environment with Access
front end

 The problem you are having is very common.  I have given this same
 to many people on the newsgroup and it seems to work every time ----
 me know if it works for you.

 Derek Best.
 Best & Associates, Inc.

 Recently I replaced Novell with NT in an 18-station installation in
 and it immediately started corrupting everything.  It was acting as
 there were no multi-user locking protocol at all.  Indexes and
records were
 being overwritten and corrupted by competing workstations.

 Then I read an article from Data Access Corp.  The article is really
 intended for the Dataflex community but Microsoft Access is mentioned
 name in it.

 It explains that NT must have its opportunistic Locking turned off in
 order to function correctly as a multi-user server.
 It also explains that all workstations must have a certain version of
 VREDIR.VXD, and they must have DiscardCacheOnOpen properly set in the

 I applied the changes mentioned in the article and my NT installation
 immediately went from a nightmare to a dream.

 Perhaps you might want to try....?   The article follows.   Regards,

  Corruption with Windows NT Networks

  And Buffering with Windows 95/98 Clients

  As you know, we have been dealing with a situation where Windows 95
  workstations that open files shared from Windows NT machines have
  problems with buffering. Over the past few months, we have been
examining ways to
  defeat this buffering from within DataFlex because Microsoft had not
  provided a setting within the Virtual Network Redirector
  defeat the buffering at the operating-system level. The unfortunate
  aspect of the "fix-it-within-DataFlex" approach was a severe
reduction in
  Fortunately, Microsoft has now provided us with a new VREDIR.VXD for
  Windows 95 that does disable the buffering of files shared on
  The redirector executable is a self-installing program provided by
  Microsoft. Run it under Windows 95 and it installs itself and makes
  needed changes to your Windows registry. You then need to restart
  Windows 95 for the changes to take effect.
  This new redirector removes the local caching that the old
  included. The local caching would cause data errors when retrieving
  from an NT server. The new redirector has been extensively tested by
  Data Access Corporation and does indeed solve the problems with data
  reliability between a Windows 95 workstation and an NT server. This
  redirector is not needed if accessing data on a Netware server.
  installing the new redirector will have no detrimental effects on
  not using NT servers. Thus, our general recommendation would be to
  the new redirector at all customer sites.
  You may find the VREDIR executable on your disk after the
  process of Visual DataFlex 4.0.
  The new OSR2 version of Windows 95 (generally shipped with new
  in the last quarter of 1996 and into 1997), comes pre-installed with
  new redirector. This is the same version of Windows 95 with the new
  file system.
  Note: When you search for VREDIR.VXD on a machine that has not had
  either the patch or the OSR2 revision installed, the first entry
found is
  simply the component name and its associated data (displayed in
hex). On any
  machine which was shipped with the OSR2 release, or had the patch
  installed, the first entry that is found displays a comment about
  UNIX" and the revision number 4.00.955 beneath the comment (the
  with the hex data will be the second one found).
  Warning: Even if your machine came with the new VREDIR.VXD (Version
  4.00.955) installed, you will still experience corruption problems
  the registry key "DiscardCacheOnOpen" is not set to true (1 hex).
This key
  is not defined as part of standard Windows 95 installations, but
  automatically be defined and set if you use the patch provided to us
  Microsoft. (You may download this patch from
  < You may verify (and
  correct, if necessary) this setting with Regedit by querying this
key in
  "DiscardCacheOnOpen" = (1 hex).

  If you have OSR2, you must enter it yourself. Start the program:
  Choose: HKey_Local_Machine -
  System -
  Current Control Set -
  Services -
  VxD -
  Right-Click or pull down "Edit" -
  New -
  Binary Value -
  Rename Value -
  Value name: DiscardCacheOnOpen
  Modify -
  Parameter value: 00 01

  Sharing DataFlex Files on Servers  (note: also applies to Access -

  You will experience data- and index-file corruption (or at least an
  apparent lack of synchronization between the data at various
  when using DataFlex in configurations where the shared files are
  on a Windows NT Server. These problems were verified in-house with
  DataFlex 3.1b for DOS and Visual DataFlex 4. It was also reported
  Microsoft Access had similar problems and required that
  locking (an NT feature) needed to be disabled at the server for the
  Access application to function correctly. After examining the
  Knowledge Base article Q129202 (Explanation of Opportunistic Locking
on Windows
  NT), we re-tested DataFlex 3.1b and Visual DataFlex after turning
off the NT
  Server parameters for opportunistic locking. These settings, and how
  change them, are shown below.
  If you examine the Knowledge Base article, you will notice
references to
  NT Workstation settings as well as the server settings. Our testing
  limited to turning off OpLocks at the server. We have not tested the
  results of turning off OpLocks at all NT Workstations running
  applications and keeping it enabled at the server. It is possible
  NT Workstation enforcement will also work, but our recommended and
  supported solution is to disable this feature at the server.
  It is important to note that the updated VREDIR.VXD workstation fix
  also required for stable operations when using Windows 95
  There are actually two different operating-system-level functions at
  work when using combinations of Windows 95 and Windows NT
workstations and
  Windows NT server: local buffering of data at the Windows 95
  workstations (controlled by VREDIR.VXD and its registry settings at
the workstation)
  and opportunistic-locking buffering at the Windows NT workstations
  (controlled by combinations of settings at the workstation and
  but only supported by disabling it at the server).
  Verify (and correct, if necessary) these NT Server parameters:
  EnableOpLockForceClose REG_DWORD set to 1 (default=0)
  EnableOplocks REG_DWORD set to 0 (default=1)
  This is done by starting the program:
  Choose: HKey_Local_Machine -
  System -
  Current Control Set -
  Services -
  LanmanServer -
  Parameters -
  If the Value EnableOpLockForceClose exists -
  Double-click on it OR
  Select it, then pull down "Edit" and choose DWORD -
  In DWORD Editor, Hex selected, enter the value
  If it does not exist, pull down "Edit" and choose Add value -
  Value name:


read more »

Mon, 04 Mar 2002 03:00:00 GMT  
 Getting Errors when Access DB gets large
Your question was partly answered in the "Having to compact the database to
avoid errors" post dated Sept 16 of the public.vb.Database group.

Here is more to give you a hand:

If your user finds this annoying why not just have the application do it
automatically, you set a flag in the database or in a flag file or in the
registry that holds the date/time that the last compact was performed, on
starting the app you check the value and if it is beyond a certain range you
just pop up a "pease Wait" modeless form wtih the main screen as the parent,
do the compact and then connect to the database.

An related alternative, since you seem worried over the size of the file,
hold the size of the file along with the date/time of the last compact.
When you start the app it compares the current filesize to the last value,
you can use a percentage ratio to determine whether the file needs
compacting or not.

Since Compacts require Exclusive locks (because you are going to be killing
the source database file when you are done) you will have to add code to
ensure an exclusive before starting the compact routines.  A professional
app would also have a way to alert any connected users that they have to
close down for maintenance ... and that has to also be able to remotely shut
down any connections if remote users (in the nect office or in another
country) have your app running while they are not at their computers.  This
is the fun stuff!

I suggest you take a look at this source code:  it demonstrates exactly how this is
all done in a multiuser environment with fully documented project examples
and explains why some "more simple" flag file techniques tend to fail in the
real world.

-Robert Smith
Kirkland WA


>I have an VB6 App using Access97.  After a while, as with any access db,
>gets large after a few days of continuous adding and deleting.  Problem is
>erroneous errors
>begin to occur as the DB begins to grow.  Compacting the DB clears this
>problem but
>my customer finds this annoying.

>Has anyone else had this error?

>thank you in advance.


Mon, 04 Mar 2002 03:00:00 GMT  
 [ 3 post ] 

 Relevant Pages 

1. Access DB's keep getting errors

2. Flexgrid populated from ADO control - getting error on large tables

3. Getting to code in VB module for Access DB

4. Getting Image from Access DB to PictureBox

5. Getting list of Access DB tables with VB

6. Trouble getting a VB3/Access Jet 2.0 DB App to run in NT4.0

7. Getting quoted text from an Access db

8. Getting list of reports and printing from Access DB using VB

9. Getting a list of tables from a Access DB

10. Getting Image from Access DB to PictureBox

11. Trouble getting Access db info to form...

12. Getting a picture from an access db on an ASP page


Powered by phpBB® Forum Software