?
Solved

Error -2147467254

Posted on 2003-03-18
9
Medium Priority
?
340 Views
Last Modified: 2012-06-27
I am trying to write a program for multiple users to take tests on the PC at the same time. I am getting an error in several different sections of my code that mention a record lock. I listed the error number above, but have had 3 different descriptions of the error including: "Could not lock file" and "The database has been placed in a state by user 'ADMIN' on machine 'ABC' that prevents it from being opened or locked"

I put in error handling code to find out what section this happens in. I am listing the code below. If anyone has any posible soltions I would sure love to hear them. Thanks


Public Sub Rtv_Mult_Answer()
Dim cn As ADODB.Connection
Dim rs_US As ADODB.Recordset

On Error GoTo errh:
Err_Cnt = 0

Set cn = New ADODB.Connection
With cn 'Open Connection
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Training Exams\Training.mdb;Mode=Read;Persist Security Info=False"
        .CursorLocation = adUseClient
        .Open
End With

Set rs_US = New ADODB.Recordset

    With rs_US 'Write user answer to User score file
        If frmUser.Del_W <> "C" Then ' Not Completed get prev answer
            .Filter = "USUSER = '" & frmUser.UserID_W & _
            "' and USTSTNUM = " & frmUser.Tstnum_W & _
            " and USTSTQST = " & frmUser.TstQst_W & _
            " and USSEQNUM = " & frmUser.SeqNum_W & _
            " and USDTES = " & Date
        Else 'User took test before get last tests answers
            .Filter = "USUSER = '" & frmUser.UserID_W & _
            "' and USTSTNUM = " & frmUser.Tstnum_W & _
            " and USTSTQST = " & frmUser.TstQst_W & _
            " and USSEQNUM = " & frmUser.SeqNum_W & _
            " and USDTES = " & frmUser.LastDate_W
        End If
         
        .Open "USRSCR", cn, adOpenForwardOnly, adLockReadOnly, adCmdTable
        frmUser.PrevAns_W = !USANSNUM
        .MoveNext
    End With
     
    rs_US.Close 'Close User Score File
    Set rs_US = Nothing

cn.Close 'Close the Connection
Set cn = Nothing

Exit Sub 'Don't do this unless error happens

errh:
' Retry until MAX_RETRIES are hit to increment your error count.
Err_Cnt = Err_Cnt + 1
If Err_Cnt < 20 Then
    MsgBox "Error in RTV_MULT_ANSWER", vbCritical
    Resume
Else
' Retries did not help. Show the error, and fall out.
    MsgBox Err.Number & " " & Err.Description & " Contact technical support with this information", vbCritical
    End
End If

End Sub
0
Comment
Question by:CMULHERON
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 16

Accepted Solution

by:
Richie_Simonetti earned 60 total points
ID: 8160295
it appears that you did not properly closed the DB.
Also, it could be opened in design mode by another machine (even the same pc!)
0
 

Author Comment

by:CMULHERON
ID: 8160922
I know that the database is not open in Design mode, because I am currently the only one with access to it.  I am relatively new to VB so do you see a problem with the closing of the database in this code?

I did go through the rest of my code and did find 2 spots that I did not close the connection "CN.close" before I left the subroutine.  I use CN as my connection in all my subroutines.  Could this be the cause?

Also, when I find and error I do an "Exit Sub".  I just close the connection in this case.  Do I need to set my recordsets = NOTHING before closing the connection or will closing the connection do this for me?
 
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8161057
you are using a restrictive connection an recordset there, try using a less restrictive one

.Open "USRSCR", cn, adOpensnapshoot, adLockoptimistic, adCmdTable
 (if i remember well)
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!

 

Author Comment

by:CMULHERON
ID: 8161211
The cursor options are opendynamic, openstatic, openkeyset and openforwardonly.  The lock types available are lockBatchoptimistic, lockOptimistic, lockPessimistic and lockReadolny.  I don't think that Opensnapshoot is an option.  By the way I am using VB6.0

I am using the forwardonly cursor because I only need to read the records in order and I am using the LockReadonly becaue I don't need to update anything, so I figured that VB would not lock the file then.

.Open "USRSCR", cn, adOpenForwardOnly, adLockReadOnly, adCmdTable

In my last post I mentioned I missed closing the connection a couple times.  Would this have caused the problem?
0
 
LVL 3

Expert Comment

by:Shaka913
ID: 8161346
I recommend changing your cursor to a static, potentially changing your cursor location type (on the connection open) to useServer.  

This should work for you.
0
 

Author Comment

by:CMULHERON
ID: 8161521
Here is Microsoft's defintion of the forward only cursor.

"Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset"

As for the cursor location on the connection, the book I have says that you cannot sort when usinf the USESERVER cursor and I need to sort some of my data.  It also says that the only time you should need to use the ADUSESEVER cursor is for batch transactinos, and mine is interactive.

So I don't think either of these will work for me, but thanks for your input, and if you have any other sugesstions it sure would help.  Thanks
0
 

Author Comment

by:CMULHERON
ID: 8167827
Thanks Richie - I went through my code and found that I did not close the database in a couple spots.  I posted a new question if you have any ideas.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8167889
Glad to help but, why a "B" grade?
0
 

Author Comment

by:CMULHERON
ID: 8167955
I don't know, This is my first time posting and I didn't really pay much attention to the grading system.  Sorry
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month8 days, 8 hours left to enroll

764 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