Update a record in an SQL database

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

Guest

I'm placing my question here because it involves getting Access to do read,
lock and then write a column in an SQL database table. I need some general
orientation to the task of reading a row from an SQL table from within my
Access code, having a lock on that record so no one else can write to it
until a perform an update, update the value of a column and then release the
lock. If this uses a stored procedure in SQL, then I need to particulars how
to call the procedure and return values. Referral to an article would be
appreciated. I know how to connect to an SQL database and perform ordinary
SELECT. Thank you.
 
I guess you mean "SQL Server", not "SQL"?

(They're not the same. One is a product, the other a language. The SQL
language is used in lots of database products including but not limited
to SQL Server.)

In Oracle SQL, the SELECT FOR UPDATE construct locks a single row. I
can't remember if that is standard ANSI syntax (which might also exist
in SQL Server's dialog of SQL), or whether it's an Oracle specific
thing.

Alternatively, wouldn't something like this be performed as an atomic
action;:
UPDATE <table> SET <col1>=<exp1>, <col2>=<exp2>, ...
ie. no need to lock the record, at all?

In any case, the answer to your question would lie in the SQL Server
docs. It's really an SQL Server question, not an Access one, afaics.

HTH,
TC
 
Dear TC,

OK point taken, I mean SQL Server. However, I still have never been able to
find documentation on how to run SQL Server stored procedures from my Access
Code, or how to do what I asked about: Read a row, update a value and write
it back, but no one else can update the row until I complete my operation.
 
Dear TC or others,

Here is the code in an Access Module to read a record from a table in the
SQL Server database, modify a value and rewrite the record. However, this
gives me the error 3027 (Database Read only). Can you help me to open the SQL
Server table so I can write to it (and if possible deny others ability to
read or write until I reclose the table). If you say this is an SQL Server
question, but the code is written in an Access Module. Thank you:

Public Sub AllocateUserVarsMSR(MSRCount As Long)
On Error GoTo ErrorHandler

Dim mydb As DAO.Database
Dim myq As DAO.QueryDef
Dim myrs As DAO.Recordset
Dim sqltext As String, strConnect As String, LastMSR As Long

Set mydb = CurrentDb

Set myq = mydb.CreateQueryDef("")
sqltext = "SELECT NextMSR FROM UserVars"
strConnect = "ODBC;DSN=" & _
varLookup("Lookup", "tlkpStoredStrings", "VariableName = 'DSN'") &
";;" & _
"Network=DBMSSOCN;Trusted_Connection=Yes"

With myq
.Connect = strConnect
.SQL = sqltext
Set myrs = .OpenRecordset
End With

With myrs
' ... code to work with recordset ...
'.MoveFirst - not needed. only one record in UserVars
LastMSR = !NextMSR 'Designer mislabeled the column. Should be
LastMSR.
!NextMSR = LastMSR + MSRCount
.Update
.Close
End With
MsgBox "LastMSR is " & LastMSR 'Beta testing only

AllocateUserVarsMSR_Exit:
myq.Close
Set myrs = Nothing: Set myq = Nothing: Set mydb = Nothing
sqltext = vbNullString: strConnect = vbNullString
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 3151
MsgBox "Information not found due to ODBC connection failure." &
vbCrLf & _
"Open Maintenance, Lookup and Correct DSN", _
vbInformation, Err.Number & " - " & Error$
Case Else
MsgBox Err.Number & " - " & Err.Description, vbInformation
End Select
Resume AllocateUserVarsMSR_Exit

End Sub
 
Not sure why you're getting a read-only error. If it's an SQL Server
issue, I can't help with that unfortunately.

But I can sure see why you're worried about the locking issue. There is
definitely a great big multi-user hole in the existing code, as you
clearly know already.

The following approach would manage to increment the value atomically:
UPDATE UserVars SET NextMsr = NextMsr + 1
but then you'd have a similar multi-user hole when you tried to read
the value back.

So, I get back to what I asked you before: did you follow-up on the
SELECT FOR UPDATE suggestion? If that is an ANSI thing (not just an
Oracle thing), maybe that would be the solution. Or maybe LOCK TABLE?

HTH,
TC
 
Dear TC, Thank you for all your suggestions and sorry if I lack knowledge to
fully use your help. I'm going to keep searching elsewhere. I can define my
question as no longer about incrementing a counter. I can now define it: From
MS Access code (not Oracle and not an Access Project), storing the data in
SQL Server 2000, in a multi-user environment, I want a user to be able to
read a row in an SQL table, edit it and write it back, during which no one
else may change this row. I do not want to link the table to my Access
database because I don't want to expose it to users. So, I will use a
..Connect method as shown in the example. I don't know the correct syntax to
either open the record set exclusively or select a row with the right kind of
lock. I worked around this in the code I sent you by spliting off the
write-back into a separate procedure with a "pass-through" query with the
UPDATE <table> SET <col1>=<exp1>, <col2>=<exp2>, ... that you suggested, but
that still leaves the multi-user hole.

Anyway, thanks for your help and sorry if I have not understood your answers.
 
Richard, I've given you two suggestion that might achieve what you've
asked for:

(1) The sql SELECT FOR UPDATE statement;

(2) The sql LOCK TABLE statement.

All that you need to do, is to check your SQL Server documentation, to
see if either of those two statements is defined, in SQL Server sql.

If one of them /is/, then, read the description, and see if that
statement would do what you want.

I'm not sure how I could put it more clearly.

Good luck!
TC
 
Back
Top