Record locks in MS Access

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

noooodlezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
clampsCommented:
How do you access the AccessDB?
ADO?
if yes you can use the proper lock types...
0
clampsCommented:
adLockReadOnly
adLockPessimistic
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kbireckiCommented:
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
Luke ChungPresidentCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbireckiCommented:
@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
noooodlezAuthor Commented:
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
Luke ChungPresidentCommented:
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
noooodlezAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Luke ChungPresidentCommented:
mx

Thanks for the kind words about Total Access Admin!

Luke
0
kbireckiCommented:
@LukChung, I'm the one that mentioned I use your products.  I don't think it was noooodlez.
0
Luke ChungPresidentCommented:
kbirecki: My bad!  Thanks for your support.
0
noooodlezAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.