?
Solved

Record locks in MS Access

Posted on 2011-10-18
14
Medium Priority
?
528 Views
Last Modified: 2012-05-12
Hi Guys,

I think I already know the answer to this, but thought I would look sof some advice from the experts.

Scenario.
We run a bit of production control software in the factory/offices. This was written by a guy years ago, we have since parted company and are currently in process replacing these systems. (quite a vulnerable position).

We are having an issue with one particular area of the system. When USER A attaches raw material to a job he crashes 50% of the time with Runtime Error 13, type mismatch.

On further investigation we have found that if USER B is in a stock search screen, USER A crashes out (seems strange that the error is type mismatch, but true). This is a nightmare as the whole factory uses this machine.

Is there any way from MS Access (or 3rd party tool) to view current record locks, or does anyone else have any gems of wisdom? Could I even get a clue through the ldb file?

DB = Access 2000
App = VB6

Many Thanks
Steven

0
Comment
Question by:noooodlez
  • 4
  • 3
  • 3
  • +3
14 Comments
 
LVL 59

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 80 total points
ID: 36985546
<<We are having an issue with one particular area of the system. When USER A attaches raw material to a job he crashes 50% of the time with Runtime Error 13, type mismatch.>>

 This is just a bug...has nothing to do with locking.

  Type mis-match means a numeric is being used when a string is expected or vice-versa.

<<Is there any way from MS Access (or 3rd party tool) to view current record locks, or does anyone else have any gems of wisdom? Could I even get a clue through the ldb file?>>

   There is a DBLock utility, but this is not going to help as it has nothing to do with locking.  Ditto for the LDB file, which actually doesn't contain any of the locks.  It is used to place locks, but doesn't actually hold them.

Jim.

0
 
LVL 3

Expert Comment

by:clamps
ID: 36985553
How do you access the AccessDB?
ADO?
if yes you can use the proper lock types...
0
 
LVL 3

Assisted Solution

by:clamps
clamps earned 80 total points
ID: 36985565
adLockReadOnly
adLockPessimistic
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Assisted Solution

by:kbirecki
kbirecki earned 160 total points
ID: 36985765
I would agree with jdettman that this is a variable typing problem.  Do you have access to the vb6 code?

If this hadn't occurred until recently, has anything else changed?  New parts added?  New anything that the db stores?  It might be that it allowed you to enter a string in a numeric field, but requires a number and is now choking.
0
 
LVL 10

Accepted Solution

by:
Luke Chung earned 100 total points
ID: 36985774
When you deploy Access applications to otherrs, you should have error handling in place to capture and document what went wrong. Ideally it would notify you of the problem via email, log file, writing to a table, etc. You should have line numbers to document exactly where the crash occurred.

For more info, read my paper: Error Handling and Debugging Tips and Techniques for Microsoft Access, VBA, and Visual Basic 6 (VB6)
Hope this hellps.

0
 
LVL 11

Assisted Solution

by:kbirecki
kbirecki earned 160 total points
ID: 36986100
@LukChung, I know this is a little off topic, but When I saw you were from FMS, I wanted to say you have great software.  We use several of your products.

@noodlez,i had another thought.  Ideally if you have source code you could step through until you get to the failure.  If not, how complex is your db?  Could you manually reproduce the data entry on a *test* copy of your database to narrow down what fields might be experiencing the problem and then you could back into your app to see what areas might have invalid data. I'd guess you may be able to identify where the type mismatch comes from with a little sleuthing.
0
 

Author Comment

by:noooodlez
ID: 36986464
Hi All.

Thanks for the feedback. My first port of call will be to prove 100% that accessing the stock report does produce the error. It does seem strange and I agree with JDettman and kbirecki. Type mismatch is not logical.

I do have access to the source, but I cannot fix the errors as there are 3rd party controls/components which I do not have access to. I can potentially get it loaded in vb6 studio, but will be time consuming and ultimately will never fix the problem.

I think the best I can hope for is to find a fast way to identify the root cause in order to work around in the short term.

I'll get back to you!!

0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 36986535
noooodlez:

Glad you've found our products helpful with making you more productive with Microsoft Access. I'm the president and founder of FMS, so I appreciate we've made a difference for you.

I certainly hope the problems you've encountered are not related to our 3rd party controls. Let me know if I can be of assistance.

Luke
0
 

Author Comment

by:noooodlez
ID: 36987042
No, Sage Line 50 Data Objects is an example. You need to pay an annual subscription to Sage to have access to them.

I have browsed your site and you have some tidy software on there. I may be tempted at some stage!
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 80 total points
ID: 36988713
I guess this is just really a side note, because as noted ... based on what you are saying what the Error is ... it most likely has nothing to do with record locks.

However ... IF ... you do want to know 'who' is in the DB ... you can use this excellent tool from FMS:

http://www.fmsinc.com/MicrosoftAccess/monitor.asp

It will tell you (among other things) the DeviceID ('Workstation') of all users in the db.

mx
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 36990349
mx

Thanks for the kind words about Total Access Admin!

Luke
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 36991791
@LukChung, I'm the one that mentioned I use your products.  I don't think it was noooodlez.
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 36991832
kbirecki: My bad!  Thanks for your support.
0
 

Author Closing Comment

by:noooodlez
ID: 37237828
Never really got to the bottom of this. Moral of the story is dont fall out with your support provider and dont by crappy software. have distributed points and not listed in db.
Thanks everyone for your feedback.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

621 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