AW: [AccessD] Working with TSQL Timestamp values in Access VBA?

Hi Jim,

thank you for the code sample! But the question still is: how do I
declare/set glTimestampNumber?
If I assign the timestamp value to a variant, comparing does not work as
expected. Vartype(rst!timestampnumber)
tells me that it is an array of byte values, but assigning it to a byte
array gives me a type conflict.

(The app uses a grid control that works unbound, so all of the methods of
locking/transactions etc. you mentioned
will not work here)

Michael



-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von Jim Lawrence
(AccessD)
Gesendet: Freitag, 16. Juli 2004 18:18
An: Access Developers discussion and problem solving
Betreff: RE: [AccessD] Working with TSQL Timestamp values in Access VBA?


Hi Michael:

Assuming that the time-stamp value is recorded in SQL and you have a key in
the record in question and you are using ADO then:

...
Set objCmd = New ADODB.Command
With objCmd
 .ActiveConnection = gstrConnection
 .CommandText = "CheckRecord"
 .CommandType = adCmdStoredProc
 .Parameters.Append .CreateParameter("@intEmployeeNumber", adInteger,
adParamInput, , Employeenumber)
End With

Set rs1 = New ADODB.Recordset
With rs1
 .Open objCmd, , adOpenStatic, adLockReadOnly
 If .BOF = False And .EOF = False Then
   .MoveLast
   If ![TimestampNumber] = glTimestampNumber Then
	SaveRecord = True
   Else
	SaveRecord = False
   End If
  Else
    SaveRecord = True
  End If
End With
...

...but you should not have to be using the timestamp method to track whether
a record on the SQL server was updated on client station. There are three
considerations:

1. The initial recordset that retrieved the data from the server should have
locked the record.

...
rsEmployee.Open objCmd, , adOpenDynamic, adLockOptimistic
...

2. Setting transaction lock, when updating the record and responding to any
error, by rolling the transaction back and notify the station user can catch
record conflicts.

On Error GoTo Err_UpDateRecord
...

objConn.BeginTrans
...
objConn.Execute strSQL

Exit_UpdateRecord:
Exit Function

Err_UpdateRecord:
objConn.RollbackTrans
ShowConflictMsg ("SaveEmployee")
Resume Exit_UpdateRecord
...

3. The SQL server has methods for handling/buffering record conflicts built
into it.

Even though the previous code samples are not complete I hope there is
sufficient information to make the snippets useful and informative.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Michael
Brösdorf
Sent: Friday, July 16, 2004 7:57 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Working with TSQL Timestamp values in Access VBA?


Dear group,

for an unbound form in my Access 2K application I want to compare the value
of a timestamp column to that of the same record on the server to find out
if modifications have been made by other users.

Unfortunately I can't find a way to store such timestamp values in a
variable.

Is it possible to convert timestamp values to a number or a string in a
deterministic way?
(In TSQL, the CONVERT-function does not allow for explicit type conversion
of timestamp values.)

Michael

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com