ODBC Cannot Lock All records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Working in Access 2003 & SQL 8.0. On a form I have a combo box which includes
an 'all' statement. A query pulls the data. When the table was in Access, the
combo box worked correctly. When I put the table into SQL, I get an 'odbc
cannot lock all records' error message. I have checked the SQL table and have
a unique key set up. I have also check to make sure on the Access database
that under the Options/ Advanced tab that it is a shared db and that no locks
is checked. I'm at a loss... can anyone help me???

Thanks, Jani
 
When does the error occur exactly? As soon as you open the form, as
soon as you change the value of the combo, or only when you select the
"All" item? (I'm not sure what you mean by an "All" statement - do you
mean an additional item in the combo box list?)
Can you post the SQL used to populated the combo?
 
The error occurs when I select the command button after making the selection
in the combo box, whether 'All' or single item. Here's the code for the
combo: SELECT "*" AS LocNumber, "ALL" AS LocName, 0 AS SortOrder
FROM dbo_uVOPLocation
UNION SELECT LocNumber, LocName, 1
FROM dbo_uVOPLocation
ORDER BY SortOrder, LocNumber;

Here's the code for the command button when the error occurs:
Private Sub Command118_Click()
On Error GoTo Err_Command118_Click

Dim stDocName As String

stDocName = "qryUtilitiesAllLocations"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command118_Click:
Exit Sub

Err_Command118_Click:
MsgBox Err.Description
Resume Exit_Command118_Click

End Sub

Thanks so much for looking at this for me. Jani
 
Hi

I think the problem is in qryUtilitiesAllLocations, not in the combo's
Rowsource as I thought. Can you post the SQL for the query?

cheers


Seb
 
Hi Seb,

Here's code for the qryUtilitiesAllLocations:
SELECT dbo_uVOPUtilities.Location, dbo_uVOPUtilities.Department,
dbo_uVOPUtilities.DIWater, dbo_uVOPUtilities.SoftWater,
dbo_uVOPUtilities.SteamPressure, dbo_uVOPUtilities.AirPressure,
dbo_uVOPUtilities.AirConditioning, dbo_uVOPUtilities.SpecialProcess
FROM dbo_uVOPUtilities
WHERE (((dbo_uVOPUtilities.Location) Like [Forms]![frmMain]![SelectLoc] &
"**")) OR ((([Forms]![frmMain]![SelectLoc]) Is Null));

Jani
 
Jani

I can't see any problems in the SQL. Have you checked the form's
Recordlock's property? It sounds as though this is set to "All
Records". The record-locking setting in the Options is only the
default record-locking behaviour - a form can over-ride this through
its Recordlocks property.

cheers


Seb
 
All I can think of is that it could be to do with some setting in
either QryUtilitiesAllLocations (try Design, View Properties for the
query), or in the attached table dbo_uVOPUtilities.

cheers


Seb
 
Back
Top