File in use error

[Rover]

I have a front end database on multiple machines sharing a back end
database. In my form on the front end, I would do a DLookUp to verify a
variable was in a table, then display various forms. The recordsource
for these forms are simple queries into the back end data.

I changed the DLookUp (see below) to a VB read of the record set. The
code works fine for a single user. The problem is it now errors on the
"OpenRecordSet" command with an error 3045 - "Could not use "path and
file name"; file already in use" occurs when another user on another PC
tries to run the form.

What am I doing wrong?

TIA

Jim


DLookUP (before code change)
=============================
Private Sub Command6_Click()

Dim answer As Variant
Dim myWhere As String

If txt_ssn <= " " Or IsNull(txt_ssn) Then
myWhere = "blah blah blah " & txt_workSheet
DoCmd.OpenForm "Frm_Ci04_ClaimDisplay", acNormal, , myWhere
Else
answer = DLookup(blah blah blah ")
If Not IsNull(answer) Then
DoCmd.OpenForm "frm_Ci02_ClaimantSelection", acNormal
Else
answer = MsgBox("No such SSN", vbCritical)
End If
End If
End Sub

OpenRecordSet (After code change)
==============================
Private Sub Command6_Click()

Dim answer As Variant
Dim myWhere As String
Dim dbs As Database
Dim rst As Recordset
Dim TempNo As Integer
Dim sqlString As String
Dim sqlClaimantSelection As String
Dim GroupNo As String

GroupNo = "Like " & "'*'"

sqlClaimantSelection = "SELECT blah blah blah
" INNER JOIN blah blah blah " & _
" WHERE blah blah blah ;"

sqlString = "SELECT blah blah blah " & _
"FROM blah blah blah " & _
"WHERE blah blah blah "


If txt_ssn <= " " Or IsNull(txt_ssn) Then
myWhere = "blah blah blah " & txt_workSheet
DoCmd.OpenForm "Frm_Ci04_ClaimDisplay", acNormal, , myWhere
Else
Set dbs = CurrentDb()

Set rst = dbs.openrecordset(sqlString, dbOpenSnapshot)

On Error GoTo ContinueOpen
rst.MoveLast
TempNo = rst.RecordCount

ContinueOpen:

Set rst = Nothing
Set dbs = Nothing

Select Case TempNo
Case 0
answer = MsgBox("No such SSN", vbCritical)
Case 1
DoCmd.OpenForm "frm_blah blah blah ", acViewDesign
Forms!frm_blah blah blah .RecordSource =
sqlClaimantSelection
DoCmd.Close acForm, "frmblah blah blah ", acSaveYes
DoCmd.OpenForm "frm_blah blah blah ", acNormal
Case Is > 1
DoCmd.OpenForm "frm_blah blah blah ", acNormal
End Select
End If

exitSub:

End Sub

 

[Guest]

What kind of Locking Options are you using?

 

[Rover]

No locks.
Open databases using record-level locking is NOT checked

 

[Rover]

No locks.
Open databases using record-level locking is NOT checked

 

[Rover]

I have found that it has nothing to do with my code. If I run a query
in from the front end database on one PC and go to another and try to
open the table in the back end I get the same error.

 

[Immanuel Sibero]

Hi Rover,

Can't help much more than with the usual suspects:

- Full Access to the share folder for each user.
- Make sure that no .LDB file is left behind when you're sure that noone is
in the database.
- Make sure that noone opens the database exclusively.
- The server may think the file itself is open.

You can also do some tests. Copy the backend to a local drive and run
multiple sessions of the Front End, see if you can reproduce this error.

Did you get a chance of going through the Tony's website regarding
corruptions?

Immanuel Sibero