|
Home > Archive > MS Access Multiuser > April 2005 > File in use error
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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_ClaimDispla
y", acNormal, , myWhere
Else
answer = DLookup(blah blah blah ")
If Not IsNull(answer) Then
DoCmd.OpenForm " frm_Ci02_ClaimantSel
ection", 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_ClaimDispla
y", acNormal, , myWhere
Else
Set dbs = CurrentDb()
Set rst = dbs. openrecordset(sqlStr
ing, 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
| |
| Klatuu 2005-04-20, 8:25 pm |
| What kind of Locking Options are you using?
"Rover" wrote:
> 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_ClaimDispla
y", acNormal, , myWhere
> Else
> answer = DLookup(blah blah blah ")
> If Not IsNull(answer) Then
> DoCmd.OpenForm " frm_Ci02_ClaimantSel
ection", 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_ClaimDispla
y", acNormal, , myWhere
> Else
> Set dbs = CurrentDb()
>
> Set rst = dbs. openrecordset(sqlStr
ing, 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
>
>
| |
|
| No locks.
Open databases using record-level locking is NOT checked
Klatuu wrote:[color=darkred
]
> What kind of Locking Options are you using?
>
> "Rover" wrote:
>
>
| |
|
| No locks.
Open databases using record-level locking is NOT checked
Klatuu wrote:[color=darkred
]
> What kind of Locking Options are you using?
>
> "Rover" wrote:
>
>
| |
| Immanuel Sibero 2005-04-20, 8:25 pm |
| Rover,
Just guessing..., any of the table involved is a linked table to an external
file? Text file? Excel file?
Or are all your tables native Access tables?
Immanuel Sibero
"Rover" < rover44614@RemoveThi
sStuffNetscape.net> wrote in message
news:4266CC85. 2030902@RemoveThisSt
uffNetscape.net...
> No locks.
> Open databases using record-level locking is NOT checked
>
> Klatuu wrote:
>
| |
|
| The back end database(s) are imported from another application and keyed
and indexed in Access so they should look like native Access; like
importing a text file to a Access table.
Immanuel Sibero wrote:
> Rover,
>
> Just guessing..., any of the table involved is a linked table to an external
> file? Text file? Excel file?
> Or are all your tables native Access tables?
>
> Immanuel Sibero
>
>
>
> "Rover" < rover44614@RemoveThi
sStuffNetscape.net> wrote in message
> news:4266CC85. 2030902@RemoveThisSt
uffNetscape.net...
>
>
>
| |
| Rover 2005-04-21, 11:24 am |
| 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.
Rover wrote:
> 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_ClaimDispla
y", acNormal, , myWhere
> Else
> answer = DLookup(blah blah blah ")
> If Not IsNull(answer) Then
> DoCmd.OpenForm " frm_Ci02_ClaimantSel
ection", 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_ClaimDispla
y", acNormal, , myWhere
> Else
> Set dbs = CurrentDb()
>
> Set rst = dbs. openrecordset(sqlStr
ing, 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
>
| |
| Immanuel Sibero 2005-04-21, 8:25 pm |
|
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
"Rover" < rover44614@RemoveThi
sStuffNetscape.net> wrote in message
news:ZJGdncYbKK-cUvrfRVn-vw@massilloncabletv.com...
> 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.
>
> Rover wrote:
>
|
|
|
|
|