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
database
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.

tim



Mon, 04 Mar 2002 03:00:00 GMT  
 Getting Errors when Access DB gets large
Tim,
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
another
posting.

----------------------------------------------------------------------
-------------------------



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

 Hello:
  I've created an application in VB 6.0 using DAO to access an MS
Access
 database using JET. The application shares an Access database with 5
users
 (All using the same interface). The database contains 15 tables and
the
 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
ran
 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
database
 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
some
flags in the registry, and like magic your database will run smooth.
Read
the Info that follow:

good luck

Quote:
----- Original Message -----

 We have an application that was converted from Access 2.0 to Access
97.
 We leaved the back end in the Access 2.0 format. Now we are getting
problems
 with customers that have Windows NT environments. There the database
gets
 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
 failure.

======================================================================
===



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

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

 Regards,
 Derek Best.
 Best & Associates, Inc.
 ============================================

 Recently I replaced Novell with NT in an 18-station installation in
Boston
 and it immediately started corrupting everything.  It was acting as
if
 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
by
 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
 registry.

 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,
Derek
 Best.
 ===========================================================

  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
(VREDIR.VXD) to
  defeat the buffering at the operating-system level. The unfortunate
  aspect of the "fix-it-within-DataFlex" approach was a severe
reduction in
  performance.
  Fortunately, Microsoft has now provided us with a new VREDIR.VXD for
  Windows 95 that does disable the buffering of files shared on
Windows
  NT.
  The redirector executable is a self-installing program provided by
  Microsoft. Run it under Windows 95 and it installs itself and makes
the
  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
redirector
  included. The local caching would cause data errors when retrieving
data
  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.
However,
  installing the new redirector will have no detrimental effects on
sites
  not using NT servers. Thus, our general recommendation would be to
use
  the new redirector at all customer sites.
  You may find the VREDIR executable on your disk after the
installation
  process of Visual DataFlex 4.0.
  The new OSR2 version of Windows 95 (generally shipped with new
machines
  in the last quarter of 1996 and into 1997), comes pre-installed with
the
  new redirector. This is the same version of Windows 95 with the new
FAT32
  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
"Samba
  UNIX" and the revision number 4.00.955 beneath the comment (the
screen
  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
if
  the registry key "DiscardCacheOnOpen" is not set to true (1 hex).
This key
  is not defined as part of standard Windows 95 installations, but
will
  automatically be defined and set if you use the patch provided to us
by
  Microsoft. (You may download this patch from
  <ftp://ftp.dataaccess.com/anonymous/pub/redir) You may verify (and
  correct, if necessary) this setting with Regedit by querying this
key in
  HKEY_LOCAL_MACHINE/System/CurrentControlSet/Services/VxD/VREDIR
  "DiscardCacheOnOpen" = (1 hex).

  If you have OSR2, you must enter it yourself. Start the program:
  Regedit.exe
  Choose: HKey_Local_Machine -
  System -
  Current Control Set -
  Services -
  VxD -
  VREDIR -
  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 -
  Derek)

  You will experience data- and index-file corruption (or at least an
  apparent lack of synchronization between the data at various
  workstations)
  when using DataFlex in configurations where the shared files are
stored
  on a Windows NT Server. These problems were verified in-house with
both
  DataFlex 3.1b for DOS and Visual DataFlex 4. It was also reported
that
  Microsoft Access had similar problems and required that
opportunistic
  locking (an NT feature) needed to be disabled at the server for the
  Access application to function correctly. After examining the
Microsoft
  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
to
  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
is
  limited to turning off OpLocks at the server. We have not tested the
  results of turning off OpLocks at all NT Workstations running
DataFlex
  applications and keeping it enabled at the server. It is possible
that
  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
is
  also required for stable operations when using Windows 95
workstations.
  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
server,
  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:
  ...\WINNT\Regedit.exe
  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
  1
  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:
http://www.smithvoice.com/vbdadate.htm  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

Quote:

>I have an VB6 App using Access97.  After a while, as with any access db,
the
>database
>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.

>tim



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