Solved

Access/Oracle : series of records stay locked.

Posted on 2000-05-09
22
554 Views
Last Modified: 2008-02-20

We use oracle 7.3.4 and access 97 and odbc driver oracle73 ver 2.5 2.05.03.01 sq032_73.dll 11/3/98

My problem is that a lot of records in access stay locked even when all the users have closed ms-access and i did a shutdown & restart of the oracle database.

Now i make a copy of the table in access, then delete all the records with SQL Plus in oracle and then append the copy to the origional table.

But more and more i have to do this, who knows a good solution.

Jan
0
Comment
Question by:jan_l_keersmaekers
  • 11
  • 9
  • 2
22 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2792331
Hello jan_l_keersmaekers,

Is the data modified only via forms and queries or do you have code that manipulates data?

If there are code that updates data, check that all objects and connections are closed in the end of code - and finish transactions with CommitTrans.

I also had some problems with some other things when I used Oracle 7.3.x with Access, but when I upgraded to version 8.0.5 everything has worked smoothly.

Regards,
Paasky
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2792474
I never use transactions. I used rs.edit and rs.update
0
 
LVL 10

Expert Comment

by:paasky
ID: 2792511
and you always close and free recordset objects with

rs.close
set rs = nothing
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2792555
I only use rs.close
0
 
LVL 10

Expert Comment

by:paasky
ID: 2792629
It's very important to "set xx=Nothing" of all Data Access Objects (DAO) - and other objects too. Even you use .close method, objects remain active until you unassing it with Nothing.

Set DB = CurrentDB
Set rs = DB.Openrecordset("table1")
Set fld = rs.Fields("field1")
Set qry = Db.OpenQueryDef("query1")

....
do some processing here
....

Set DB = nothing
Set rs = nothing
Set Fld = nothing
Set qry = nothing

I recommend you add these into your functions and procedures. I'm sure it will help (also saves computer resources when unneccesary objects are not taking memory).

paasky
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2792698
I only have this in one application all the others are designed the same way and don't have the problem.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2792937
Can you name any differencies between this "problem" application and other applications?
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2795670
The other applics work fine.
In this applic, a lot of records stay locked in access. In oracle they are not locked. The locking stays even if everyone has rebooted his pc and oracle is stopped and restarted.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796120
Here's some information about locks and unlocking records. I copied this article from Oracle Technet:

DETECTING AND RESOLVING LOCKING CONFLICTS

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

Document ID:        106450.503
Title:              Detecting and Resolving Locking Conflicts
Creation Date:      24 September 1994
Last Revision Date: 24 September 1994
Revision Number:    0
Product:            RDBMS
Product Version:    7.1.3
Platform:           GENERIC
Information Type:   Solution
Impact:             High
Abstract:           This article describes how to detect and resolve locking
                    and hanging problems.  It gives numerous suggestions when
                    dealing with difficult locking issues which involve
                    complex applications.   This bulletin should be used as
                    a stepping stone in resolving locking issues.
Keywords:           LOCK;SESSION;TX;TM;SQL_TEXT;HANGING
 
------------------------------------------------------------------------------
 
                 DETECTING AND RESOLVING LOCKING CONFLICTS
 
With the growing complexity of applications, it has become a challenge
for the dba and Oracle Support to resolve and properly diagnose locking
(hanging) issues.  The focus of this paper is to give some concrete steps and
examples in resolving the most common locking issues.  We will focus on
a combination of three utilities: SQL, MONITORING FACILITY, and LOCKING
SCRIPTS provided by oracle. We will also give a methodology of things to
check when faced with a difficult "hanging" scenerio.
 
 
USE OF MONITOR SCREENS:
=======================
 
MONITOR SESSION:
---------------
 
Session Serial Process                           Lock
  ID    Number   ID    Status   Username         Waited   Current Statement
===============================================================================
      6     35      28 ACTIVE   LTO2             C2D2B3B4 UPDATE
      8     70      19 INACTIVE SYSTEM                    SELECT
     12     15      25 INACTIVE LTO                       INSERT
     14     17      27 ACTIVE   LTO3             C2D2B438 DELETE
     15     30      26 ACTIVE   SYS                       UNKNOWN
 
Name            Description
Session Id      SID - Session Identifier
Serial Nr       Session serial number.  Used to uniquely identify a
                session's objects.  Guarantees that session-level commands are
                applied to the correct session objects in the event that the
                session ends and another session begins with the same session
ID
Process Id      PID in v$process.  Oracle Process Identifier.
Status          Status of the session.
Username        User name.
Lock waited     Address of lock waiting for ; null if none.
 
The most important column would be LOCK WAITED.  If it is NOT NULL, then
this user is waiting for a resource.  In this example, LTO2 and LTO3 are
waiting on locks.
 
 
MONITOR LOCK:
------------
 
                 Session Serial  Lock  Resource Resource  Mode   Mode
Username            ID   Number  Type    ID 1     ID 2    Held Requested
 
===============================================================================
 
LTO2                   6     35   TM       2294        0    RX   NONE
LTO2                   6     35   TM       2295        0    RX   NONE
LTO2                   6     35   TX     262167       87  NONE      X
LTO2                   6     35   TX     327682       90     X   NONE
LTO                   12     15   TM       2294        0    RX   NONE
LTO                   12     15   TM       2295        0    RX   NONE
LTO                   12     15   TX     262167       87     X   NONE
LTO3                  14     17   TM       2294        0    RX   NONE
LTO3                  14     17   TM       2295        0    RX   NONE
LTO3                  14     17   TX     262167       87  NONE      X
LTO3                  14     17   TX     196636       87     X   NONE
 
Username        User name.
Session Id      SID - Session Identifier.
Serial Nr       Same as above.
 
Type of Lock    LOCK ID1                    LOCK ID2
TX(Transaction) Decimal representation of   Decimal representation of
                rollback segment number     "wrap" number (number
                and slot number             times the rollback
                                            slot has been reused)
TM(Table Locks) Object id of table being    Always 0.
                modified.
RW(Row Wait)    Decimal representation of   Decimal representation of
                file no. and block no.      row within block
                (Version 6)
UL(User Defined Complete list is found in chpt 10 of the Oracle 7 Concepts
   Locks)       Manual or in Appendix B-81 in the Oracle 7 Admin Guide.
                Please note that the RW enqueue was taken out after
                version 6.0.34 and may be added back in a future release.
Mode Held       Lock mode held.
Mode Requested  Lock mode requested.
 
The following users are waiting:
 
LTO2                   6     35   TX     262167       87  NONE      X
LTO3                  14     17   TX     262167       87  NONE      X
 
They are waiting on:
 
LTO                   12     15   TM       2294        0    RX   NONE
LTO                   12     15   TM       2295        0    RX   NONE
LTO                   12     15   TX     262167       87     X   NONE
 
For the TX lock, lock id1 and lock id2 are the same when they are contending
for the same resouces.  Within the block, we have a record of the each
transactions pertaining to the data in that particular block.  If the
transaction is not committed or rolled back, then other subsequent
transactions may have to wait for that resource.
 
Often, the user(s) may be modifying many tables within the same transaction.
At times, this will make it difficult to find out which resource the WAITER
is contending from the HOLDER.  This is easily resolved by looking at a
combination of two monitors.   MONITOR SESSION will tell you which user is
waiting on a lock and MONITOR TABLE will tell you the table that the user is
currently trying to modify.
 
 
MONITOR SESSION:
---------------
 
Session Serial Process                           Lock
 
  ID    Number   ID    Status   Username         Waited   Current Statement
 
===============================================================================
 
      5     31      19 INACTIVE LTO                       INSERT
      6     43      25 ACTIVE   LTO2             C3D320F4 UPDATE
      9      1      26 ACTIVE   LTO3             C3D320C8 DELETE
 
 
 
MONITOR TABLE:
-------------
 
Session
   ID   Schema Name                    Table Name
 
===============================================================================
 
      5 LTO                            DEPT
 
      6 LTO                            EMP
 
      9 LTO                            EMP
 
 
Session 6 and 9 are waiting for locks.  They are both attempting to modify
lto.emp TABLE.
 
 
RESOLUTION:
==========
 
Most locking issues are application specifics.  To resolve locking contention,
one needs to free the resource by:
 
1.  Asking the HOLDER to commit or rollback,
2.  Killing the session which holds the lock,
    ALTER SESSION KILL SESSION sid, serial#;  or
    use KILL USER SESSION menu found in the sqldba form.
3.  Killing the unix shadow process which is not recommended.
    When killing the unix shadow process, please be careful of shared
    servers in a multi-threaded environment.
4.  ROLLBACK FORCE or COMMIT FORCE if 2pc pending transaction.
 
 
SQL STATEMENTS TO DECIPHER LOCKING ISSUES:
=========================================
 
TRANSACTIONS INVOLVED:
---------------------
 
This query defines the transactions involved in a locking situation:
 
column username format a10
column sid format 999
column lock_type format a15
column MODE_HELD format a11
column MODE_REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8
 
select a.sid,
   decode(a.type,
   'MR', 'Media Recovery',
   'RT', 'Redo Thread',
   'UN', 'User Name',
   'TX', 'Transaction',
   'TM', 'DML',
   'UL', 'PL/SQL User Lock',
   'DX', 'Distributed Xaction',
   'CF', 'Control File',
   'IS', 'Instance State',
   'FS', 'File Set',
   'IR', 'Instance Recovery',
   'ST', 'Disk Space Transaction',
   'IR', 'Instance Recovery',
   'ST', 'Disk Space Transaction',
   'TS', 'Temp Segment',
   'IV', 'Library Cache Invalidation',
   'LS', 'Log Start or Switch',
   'RW', 'Row Wait',
   'SQ', 'Sequence Number',
   'TE', 'Extend Table',
   'TT', 'Temp Table',
   a.type) lock_type,
   decode(a.lmode,
   0, 'None',           /* Mon Lock equivalent */
   1, 'Null',           /* N */
   2, 'Row-S (SS)',     /* L */
   3, 'Row-X (SX)',     /* R */
   4, 'Share',          /* S */
   5, 'S/Row-X (SSX)',  /* C */
   6, 'Exclusive',      /* X */
   to_char(a.lmode)) mode_held,
   decode(a.request,
   0, 'None',           /* Mon Lock equivalent */
   1, 'Null',           /* N */
   2, 'Row-S (SS)',     /* L */
   3, 'Row-X (SX)',     /* R */
   4, 'Share',          /* S */
   5, 'S/Row-X (SSX)',  /* C */
   6, 'Exclusive',      /* X */
   to_char(a.request)) mode_requested,
   to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
from v$lock a
   where (id1,id2) in
     (select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
     b.id2=a.id2 and b.request>0)
/
 
Sample output:
 
 SID LOCK_TYPE       MODE_HELD   MODE_REQUE LOCK_ID1 LOCK_ID2
---- --------------- ----------- ---------- -------- --------
   5 Transaction     Exclusive   None       262172   90
   6 Transaction     None        Exclusive  262172   90
   9 Transaction     None        Exclusive  262172   90
 
This is very similar to the monitor lock output.  Again, SID 6 and 9 are
waiting on SID 5.
 
OBJECTS INVOLVED:
----------------
 
This query will defined the objects that are involved in the locking conflict:
 
column username format a10
column lockwait format a10
column sql_text format a80
column object_owner format a14
column object format a15
 
select b.username username, c.sid sid, c.owner object_owner,
   c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address and
   a.hash_value=b.sql_hash_value and
   b.sid = c.sid and c.owner != 'SYS'
/
 
 
Sample output:
 
USERNAME          SID OBJECT_OWNER   OBJECT          LOCKWAIT
---------- ---------- -------------- --------------- ----------
SQL
----------------------------------------------------------------
LTO2                6 LTO            EMP             C3D320F4
update lto.emp set empno =25 where empno=7788
 
LTO3                9 LTO            EMP             C3D320C8
delete from lto.emp where empno=7788
 
LTO                 5 LTO            DEPT
insert into lto.dept values (60,'PROGRAMMER','LOS ANGELOS')
 
 
One can see the DMLs resulting from LTO2 and LTO3 who are the WAITERS.
They are waiting for LTO to commit or rollback; unfortunately, the DML
statement listed under LTO is only the most recent DML.  This may not indicate
the statement that is holding the resource.  In this example, it is NOT the
DML.  The only DML was an update statement.  OBJECT_OWNER.OBJECT is the object
USERNAME is attempting to modify.
 
PROCESSES INVOLVED:
------------------
 
To ascertain process information, one can execute this query:
 
column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8
 
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
   s.sid "SESSION ID", s.serial#, osuser "OS USER", p.spid "PROC SPID",
   s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
   p.addr=s.paddr and
   s.username != 'SYS'
/
 
Sample output:
 
ORACLE USER PROCESS ID SESSION ID  SERIAL# OS USER  PROC SPID SESS SPID LOCKWT
----------- ---------- ---------- -------- -------- --------- -----------------
LTO                 19          5       31 usupport 17312     17309
LTO2                25          6       43 usupport 17313     17310    C3D320F4
LTO3                26          9        1 usupport 17314     17311
C3D320C8
 
What can you do??
 
1) Can ask LTO to commit or rollback  or
2) alter system kill session '5,31';  or
3) kill -9 17309   (shadow process on unix)
   stop/id=<SESS SPID> (PROC SPID=SESS SPID on vms running single task)
 
Notice that the user SYS has been excluded.  If your application was created
under SYS, then you may want to include it in the query.
 
USING CATBLOCK.SQL and UTLLOCKT.SQL:
-----------------------------------
 
These are very helpful scripts that oracle provides with the other RDBMS
installation scripts (CATALOG.SQL, CATPROC.SQL, etc.)  For example,
?/rdbms/admin directory on unix and ora_rdbms directory on vms.
 
First run catblock.sql as sys and then run utllock.sql as sys.
 
Print out the result in a tree structured fashion:
 
column waiting_session format a8
 
select lpad(' ',3*(level-1)) || waiting_session waiting_session,
   lock_type,
   mode_requested,
   mode_held,
   lock_id1,
   lock_id2
from lock_holders
connect by  prior waiting_session = holding_session
start with holding_session is null;
 
Sample Output:
 
WAITING_ LOCK_TYPE         MODE_REQUE MODE_HELD  LOCK_ID1   LOCK_ID2
-------- ----------------- ---------- ---------- ---------- ----------
   5        None
   6     Transaction       Exclusive  Exclusive  262172     90
   9     Transaction       Exclusive  Exclusive  262172     90
 
Sessions 6 and 9 are waiting on session 5.
 
 
DRASTIC METHODS IN DECIPHERING A LOCKING OR "HANGING" SITUATION:
===============================================================
 
At times, it may be difficult to find out the root cause of the
"hanging" problem within your application.  One may need to resort to
drastic measures which include both tracing on the database and operating
system layer.  Here are some recommendations:
 
1) Turn on sql_trace.  This will reveal the sql statements involved.
2) Set timing on and monitor processes on the operating system side
   as well.  This will reveal if the process is getting any cpu and the
   status of the process.
3) Utilize the operating system debugging facilities or utilize "oradbx" if
   available.  (oradbx is only for support and development use and may not
   be available on your platform.)
4) Utilize monitor screens and the locking (blocking) scripts.
5) One can build your own self monitoring script.
 
For example:
 
Create a file called monitor.sql:
 
select b.username username, c.sid sid, c.owner object_owner,
   c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address and
   a.hash_value=b.sql_hash_value and
   b.sid = c.sid and
   c.owner != 'SYS'
/
 
@?/rdbms/admin/utllockt
 
!ps -ef
 
!sleep <time_interval>
 
Create a file name runtest with executable permissions:
 
sqlplus name/password << test
spool output.txt
set time on
set echo on
@monitor
 
 
ASCERTAINING PROCESS STACKS:
===========================
 
In some rare cases, one may encounter a "hanging" problem that can
not be fathom.  One may find that a process stack of the hanging process or
the process that holds the resource may be helpful in resolving the
problem.
 
1) Operating system debugging tools can be used to find out the last call
   before it hangs.  ie.  truss -p <shadow pid>
2) ORACLE'S debugging utility, oradbx, is for SUPPORT and DEVELOPMENT's
   use ONLY.
 
select substr(s.username,1,11) "ORACLE USER" ,
   p.pid "PROCESS ID", s.sid "SESSION ID", s.serial#,
   osuser "OS USER", p.spid "PROC SPID"
from v$session s, v$access a
where a.sid=s.sid and
   p.addr=s.paddr and
   s.username != 'SYS'
/
 
Sample Output:
 
ORACLE USER PROC ID SESSION ID  SERIAL# OS USER  PROC SPID SESS SPID LOCK WAIT
--------------------------------------- --------------------------------------
LTO            19          5       31   usupport  17312     17309
LTO2           25          6       43   usupport  17313     17310    C3D320F4
LTO3           26          9        1   usupport  17314     17311    C3D320C8
 
cd $ORACLE_HOME/rdbms/lib
make -f oracle.mk oradbx
To find out what LTO process is actually doing, one can dump the process stack.
 
ps -ef | grep 17312
usupport 17312 17309  0  Sep 15  ?     0:00 oracleV713  (DESCRIPTION=(LOCAL=YE
 
 
type <oradbx>
debug 17312 (which is the oracle shadow process for this user)
dump stack
dump procstat
 
These trace files may be instrumental in resolving a hanging problem.  Please
remember that Oracle's debugging utility, oradbx, is for Support and
Development's use ONLY.  The trace files should go to your USER_DUMP_DEST and
should be sent to Worldwide Support for debugging.
 
UNUSUAL LOCKING PROBLEMS:
========================
 
1) When your application has referential integrity and attempts to modify
   the child table, Oracle will get a TABLE LEVEL SHARE LOCK on the parent
   table when there is NO index on the foreign key.
 
2) When a table's PCTFREE is set too low , the block is full with data, and
   there are many concurrent DML's occurring on rows within the block, one may
   see a Share Lock being requested when doing a DML.  To my knowledge, this
   is only time we grab the SHARED lock.  Instead of waiting for a lock, this
   process is waiting for some extra space or a release of an INITRANS within
   the transaction layer of the block.
 
Here's a simple example:
 
create table test (a number) initrans 1 maxtrans 1;
 
SYSTEM:  insert into test values (5);   /* completed */
SCOTT:   insert into SYSTEM.test values (10);    /* Scott waits */
 
In normal situations, SCOTT should not have to wait but SCOTT has to wait until
SYSTEM releases the one and only INITRANS in the block.  If the MAXTRANS
was not set to 1, then oracle will try to allocate another INITRANS in the
block if space permits.  I believe each INITRANS is about 24 bytes.
 
 SID OWNER   LOCK_TYPE       MODE_HELD   MODE_REQUE LOCK_ID1 LOCK_ID2
---- -----   --------------- ----------- ---------- -------- --------
   7 System  Transaction     Exclusive   None       196639   54
  10 Scott   Transaction     None        Share      196639   54
 
 
LOCKING CHART:
=============
 
The following table describes which table accesses are gotten for which
table operations and what equivalent DML modes are actually gotten in
various conditions.
 
                                 DML Table Lock Mode
                                 Yes     Yes     No      No      Row-Locking
 Operation       Table Access    No      Yes     No      Yes     Serializable
--------------- --------------- ------- ------- ------- -------
 Select          Read            NULL    S       NULL    S
 Select
   For Update    Row-Read        SS      S       SS      S
 Insert          Row-Write       SX      SX      SSX     SSX
 Update          Read-Row-Write  SX      SSX     SSX     SSX
 Delete          Read-Row-Write  SX      SSX     SSX     SSX
 Lock For Update Row-Read        SS      S       SS      S
 
 Lock Share                      S       S       S       S
 Lock Exclusive                  X       X       X       X
 
 Lock Row Share                  SS      SS      SS      SS
 Lock Row Exclusive              SX      SX      SX      SX
 Lock Share Row Exclusive        SSX     SSX     SSX     SSX
 
 Alter           Write           X       X       X       X
 Drop            Write           X       X       X       X
 Create Index                    S       S       S       S
 Drop Index      Write           X       X       X       X
 ---------------------------------------------------------------
 
Lock Mode Compatibility:
-----------------------
 
NULL    SS      SX      S       SSX     X
-----------------------------------------------------
 NULL  YES     YES     YES     YES     YES     YES
   SS  YES     YES     YES     YES     YES     no
   SX  YES     YES     YES     no      no      no
    S  YES     YES     no      YES     no      no
  SSX  YES     YES     no      no      no      no
    X  YES     no      no      no      no      no
 
------------------------------------------------------------------------------
                                       
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796158
Above article contains instructions how to resolve record locks - you don't need to shutdown database or ask all users to quit, just log in with dba privileges, determine problem process with

column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8
 
select substr(s.username,1,11) "ORACLE USER",
       p.pid "PROCESS ID",
       s.sid "SESSION ID",
       s.serial#,
       osuser "OS USER",
       p.spid "PROC SPID",
       s.process "SESS SPID",    
       s.lockwait "LOCK WAIT"
from v$process p,
     v$session s,
     v$access a
where a.sid=s.sid and
   p.addr=s.paddr and
   s.username != 'SYS'
/

and kill locked process with this command:

ALTER SYSTEM KILL SESSION '<PROCESS ID>,<SESSION ID>';

Hope this helps,
paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796240
copy-paste error, ALTER SESSION clause should be like this:

ALTER SYSTEM KILL SESSION '<SESSION ID>,<SERIAL#>';

Here's an example how to kill jammed process:

SQL> SELECT ....

ORACLE USER PROCESS ID SESSION ID  SERIAL# OS USER  PROC SPID SESS SPID LOCK WAI
----------- ---------- ---------- -------- -------- --------- --------- --------
DBSNMP               8          9        5 SYSTEM   00165     117:311
TOP20                9          7     2636 arto     0020E     476:430   D3A34051
SYSTEM              10          8    13299 arto     001B4     332:582
....
....

SQL> ALTER SYSTEM KILL SESSION '7,2636';

System altered.

Voila, lock is gone!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jan_l_keersmaekers
ID: 2796275
But when i let every one exit access and look in oracle there are no session because all sessions are ended.
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2796278
And when in oracle sql i can update the records that access thinks are locked.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796389
In which situations this locking usually happens, does the user run any particular action (for eg. code) when this happens. After you everyone has logged out of the database, does .ldb file still remain on the server/workstation HD? If the .ldb was still there, have you tried to delete it before re-opening application?
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2796427
I checked again and in oracle are absolutely no lockings.

Only access thinks that a lot of records are locked from the attached tables to oracle.

I created a new mdb and linked to oralce table and again access thinks the records are locked. In oracle they are not locked. And they shoudn't because there are no users except me in access or oracle for the moment.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796494
Install latest SR-2 and Jet35 Update if they would help:

SR-2:
http://officeupdate.microsoft.com/

Jet35 Update:
http://support.microsoft.com/download/support/mslfiles/jet35sp2.exe
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2796573
I can't do that because i'm in a worldwide company, it would take month's to ask for an update.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796640
Would it be possible to add before insert & before update trigger to the problem table that would keep the log of record changes (store events to log table)? When the error happens, you could check the data of problem record and possibly find out what's the problem.
0
 
LVL 57
ID: 2797943
From everything that's been said, it sounds like a coding problem with this one MDB.

I would suggest downloading JETUTILS.EXE from microsofts web site.  In that file, you will find JETLOCK.DOC, which explains JET locking in detail.

  More importatnly though are the two utilities included: LDBView.EXE and DBLCK.EXE.

  LDBView let's you see who is flagged as being connected to the MDB file.  This may point out a problem with network security, users who are termination in abnormal ways, etc.

  DBLCK.EXE will let you view the actual locks placed with the NOS.  This will help you determine what area of your app may be causing a problem.

HTH,
Jim.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2797978
That's good information JDettman! Can you give the URL where this tool can be downloaded? I even might have downloaded it some time ago, but forgot the whole stuff...

>HTH
I'm sure it will help! ;o)

Regards,
Paasky
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 2798055
0
 

Author Comment

by:jan_l_keersmaekers
ID: 2803445
I still don't have a clue why in this mdb i have this problem, while all my other mdb's are programmed the same way.

I'll unload, del & reload these tables.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now