MS Access Front End App, SQL Back End and User Login Criteria (1 Viewer)

[bsmiller92]

I am currently maintaining a bridge inspection program that I created for all the County Engineers in the State of Ohio, that they use to submit bridge inspection and inventory data on a yearly basis to the Ohio Dept. of Transportation. The DB was originally a split DB, but now I am working on a new version that will be remotely accessed by the users as a MS Access front end with the data sitting on an SQL Express server. I have successfully transported the data (which exists in two tables - "dboBR86 and dboBR87" to the SQL Express server, and connected the SQL tables via ODBC to the front end. In the front end, when the app is launched, the user will put in a password, clicks OK, the password is associated with the correct criteria value from the password user table, and passes the criteria value to a login conformation form. The user then clicks the LOGIN button and the criteria value is then passed to the two queries BR86 and BR87 that are the Record Sources for the BR86 and BR87 forms. The problem I am having is with the BR87, because it is treated differently in the program than the BR86 data. When changes are made to any record in the BR87, only those records get marked for export. When the record is changed and you try to exit the form, the message:

Run-time Error '3254' ODBC--cannot lock all records.

appears. Both tables have a Key field, the Access record options are set to Open Mode: Shared - Record Locking: No Locks. The BR86 and BR87 forms have no record locking either.

The VBA code is as follows. The line in red is where the program halts:

Public Function UpdateCard(ByVal sSFN As String, ByVal sCard As String)
Dim rsBR87 As Recordset, iBegin As Integer, sSQL As String

sCard = UCase$(sCard)
sSQL = "SELECT TRAN_CODE FROM BR87 WHERE SFN = '" & sSFN & "'"
Set rsBR87 = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbDenyWrite, dbOptimistic)
With rsBR87
.Edit
If IsNull(!TRAN_CODE) Then !TRAN_CODE = Space(15) 'Need to watch these Nulls--see my documentation!
Select Case sCard
Case "A"
iBegin = 1
Case "B"
iBegin = 2
Case "C"
iBegin = 3
Case "D"
iBegin = 4
Case "E"
iBegin = 5
Case "F"
iBegin = 6
Case "G"
iBegin = 7
Case "H"
iBegin = 8
Case "I"
iBegin = 9
Case "J"
iBegin = 10
Case "K"
iBegin = 11
Case "L"
iBegin = 12
Case "M"
iBegin = 13
Case "N"
iBegin = 14
Case "O"
iBegin = 15
End Select

If iBegin = 1 Then
!TRAN_CODE = "C" & Mid$(!TRAN_CODE, 2)
ElseIf iBegin = 15 Then
!TRAN_CODE = Mid$(!TRAN_CODE, 1, 14) & "C"
Else
!TRAN_CODE = Mid$(!TRAN_CODE, 1, iBegin - 1) & "C" & Mid$(!TRAN_CODE, iBegin + 1)
End If
.Update
End With



End Function

Any help would greatly be appreciated.
bsmiller92