Store SQL Timestamp locally for comparison

[DBG]

I'm currently downloading data from SQL Server tables and storing that data
locally for use at application run time. This data doesn't change often,
select list data, etc.

The SQL Server tables have timestamp fields on them which I want to store
locally in a scripting dictionary, so that I can perform a comparison in
certain scenarios to verify if the records have changed on the SQL server.

As such, I have the following code running a pass-through query:

For Each varName In varTableList
' Iterate the list of tables, and call the code which loads them
blnResult = GetTimestampData("Select max(Updated) as Updated From tbl"
& varName, "" & varName)
Next varName

GetTimestampData runs creates the connection, runs the query and opens the
recordset to it.

So my issue is this:
a debug.print rec("Updated") prints a list of ?'s and I'm not sure what that
means.
Do I just throw that data into a variant or something which preserves the
data even though access can't interpret it?

If I Select Convert(Datetime,max(Updated),100) as Updated From tbl" & varName

I can get a date back (obviously) but without milliseconds displayed it
seems like im loosing the timestampiness of the timestamp. If I try to use
the 109 format # of convert, it seems to be ignoring it.

*sigh* I hope this explanation isn't too muddled for someone to help with
what I'm trying to accomplish!

Thanks,

-David

 

[Brian Wilson]

I'm currently downloading data from SQL Server tables and storing that
data
locally for use at application run time. This data doesn't change often,
select list data, etc.

The SQL Server tables have timestamp fields on them which I want to store
locally in a scripting dictionary, so that I can perform a comparison in
certain scenarios to verify if the records have changed on the SQL server.

As such, I have the following code running a pass-through query:

For Each varName In varTableList
' Iterate the list of tables, and call the code which loads them
blnResult = GetTimestampData("Select max(Updated) as Updated From tbl"
& varName, "" & varName)
Next varName

GetTimestampData runs creates the connection, runs the query and opens the
recordset to it.

So my issue is this:
a debug.print rec("Updated") prints a list of ?'s and I'm not sure what
that
means.
Do I just throw that data into a variant or something which preserves the
data even though access can't interpret it?

If I Select Convert(Datetime,max(Updated),100) as Updated From tbl" &
varName

I can get a date back (obviously) but without milliseconds displayed it
seems like im loosing the timestampiness of the timestamp. If I try to
use
the 109 format # of convert, it seems to be ignoring it.

*sigh* I hope this explanation isn't too muddled for someone to help with
what I'm trying to accomplish!

Thanks,

-David

You do realise that timestamp fields are not really about storing dates or
times, but storing a rowversion for the record?
If you have a field of type Timestamp in SQL Server, which type is used to
hold this in the local table in the mdb file?

I'm not quite sure what you're doing with the scripting dictionary bit, but
in general if I wanted to compare two tables: local and server, and get a
list of the modified ones, I could write a query like this:

SELECT A.ID, A.F1
FROM A INNER JOIN B
ON A.ID=B.ID
WHERE A.Stmp<>B.Stmp

In other words I don't care what is contained in the timestamp field, I just
need to know whether or not it is the same - ie has the row been updated on
the server since the time I downloaded my local copy.

 

[DBG]

You do realise that timestamp fields are not really about storing dates or

times, but storing a rowversion for the record?

Yes, I don't care about the actual data of the timestamp.

If you have a field of type Timestamp in SQL Server, which type is used to
hold this in the local table in the mdb file?

I don't want to hold it in a table, I want to hold in a variable form.
Array, variant, whatever. I just don't know the assoicated type to store it
as.

In other words I don't care what is contained in the timestamp field, I just
need to know whether or not it is the same - ie has the row been updated on
the server since the time I downloaded my local copy.

Right, I appreciate that I could use a bit of code like that, but all I care
about is the MAX value of the timestamp fields. Ie, the value when any row
in the table was last updated. In this way I can check my local value vs.
the value on the server, to see if anyone has made any changes to any records
without checking them all.

Thanks for the input so far!

-David

 

[david epsom dot com dot au]

Time stamps are not date times, and don't have milliseconds,
(and aren't called time-stamps in the SQL Server documentation
any more).

Time stamps are a sequential (?) number, unique per database
(?? or unique per server ??)

The server has a 'list' of these numbers, and takes the next
number from the list every time a record with a TS field is
updated.

So coercing the value to a date-time is meaningless, and
the absence of a millisecond format for date-time is not
relevant.

If the numbers are sequential, then MAX will work. If the
numbers are not sequential, then you won't be able to use
that method.


Offhand, I can't tell you what format would make the most
sense for a ts field. I think a variant will probably work
(and will probably store the value as 'text'), but that
is just theory.

(david)

 

[Brian Wilson]

Yes, I don't care about the actual data of the timestamp.


I don't want to hold it in a table, I want to hold in a variable form.
Array, variant, whatever. I just don't know the assoicated type to store
it
as.


Right, I appreciate that I could use a bit of code like that, but all I
care
about is the MAX value of the timestamp fields. Ie, the value when any
row
in the table was last updated. In this way I can check my local value vs.
the value on the server, to see if anyone has made any changes to any
records
without checking them all.

Thanks for the input so far!

-David

OK I understand what you wish to do, but I would still recommend downloading
the timestamp field into your local Access database. This does not mean
that you have to check every row for changes, you could write a function to
efficiently find out if at least one row in the table has changed.
Assuming you are also using another field as a primary key, this makes a
very powerful combination, because you can now find not only if a table has
been changed, but how many rows and also list all the rows which exist in
one dataset but not in the other (ie records which have been added or
deleted).

 

[DBG]

Time stamps are not date times, and don't have milliseconds,
(and aren't called time-stamps in the SQL Server documentation
any more).

Time stamps are a sequential (?) number, unique per database
(?? or unique per server ??)

The server has a 'list' of these numbers, and takes the next
number from the list every time a record with a TS field is
updated.

So coercing the value to a date-time is meaningless, and
the absence of a millisecond format for date-time is not
relevant.

Yeah, datetime was a crappy idea, but I was trying just about anything.

If the numbers are sequential, then MAX will work. If the
numbers are not sequential, then you won't be able to use
that method.
Right.


Offhand, I can't tell you what format would make the most
sense for a ts field. I think a variant will probably work
(and will probably store the value as 'text'), but that
is just theory.

I slept on it and when I woke up I had the same "revelation" about variant.
I'll probably give that a try if I go that direction.

Thanks,

-David

 

[DBG]

OK I understand what you wish to do, but I would still recommend downloading

the timestamp field into your local Access database. This does not mean
that you have to check every row for changes, you could write a function to
efficiently find out if at least one row in the table has changed.

Good point. Might you have a code snippet of what you are thinking here?

Assuming you are also using another field as a primary key,

(yes an autoincrementing pk field)

this makes a
very powerful combination, because you can now find not only if a table has
been changed, but how many rows and also list all the rows which exist in
one dataset but not in the other (ie records which have been added or
deleted).

Even better point.

You comments above got me to thinking that if I had the timestamp values
stored locally, I could do a select count(*) using the SQL you posted earlier
and if it returns a number that should tell me that 1 or more records have
changed, and I need to reload my list.

Thanks again.

-David

 

[DBG]

Lest I forget, what do you think is an appropriate database table Data Type
for storing the timestamp information. Text? Obviously it's only being
stored locally for an exact comparison to what is stored remotely, and I have
a feeling if I store it in a date/time in Access it will get mangled. I
could be wrong though.

-David

 

[Brendan Reynolds]

For whatever it may be worth ...

If you import or link a SQL Server table with a timestamp field, Access uses
Binary for the datatype.

There doesn't, as far as I can tell, seem to be any way to create a Binary
field via the Access UI, but you can do it with SQL ...

CurrentProject.Connection.Execute "ALTER TABLE SomeTable ADD COLUMN TestBin
BINARY"

 

[Brian Wilson]

Lest I forget, what do you think is an appropriate database table Data
Type
for storing the timestamp information. Text? Obviously it's only being
stored locally for an exact comparison to what is stored remotely, and I
have
a feeling if I store it in a date/time in Access it will get mangled. I
could be wrong though.

-David

As you know, Access doesn't have the timestamp field, but I believe this
maps to a Binary (8) field. However you cannot create this via the GUI and
you would have to do it in code, nor am I sure whether there is any suitable
substitute.

Anyway, here is an example of the sort of code you could write. It assumes
that all the changes happen to the server dataset so that if a record is
present in the local table but not the linked table then it must have been
deleted from the server (rather than added to the local table). Similarly,
if a record is missing from the local table which is in the linked table,
then this must have been due to adding a record to the server (rather than
deleting from the local table)


Public Sub CompareTables()

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim lngRecords As Long
Dim strInfo As String

Set dbs = CurrentDb


' ------------------------------------------------------------------
strSQL = "SELECT Count(*) AS DeletedFromServer " & _
"FROM tblLocal LEFT JOIN tblLinked " & _
"ON tblLocal.ID=tblLinked.ID " & _
"WHERE tblLinked.ID Is Null"

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

If Not rst.EOF Then
lngRecords = CLng(rst.Fields(0).Value)
strInfo = strInfo & vbCrLf & CStr(lngRecords) & _
" record(s) have been deleted from the server"
End If

rst.Close

Set rst = Nothing
' ------------------------------------------------------------------



' ------------------------------------------------------------------
strSQL = "SELECT Count(*) AS AddedToServer " & _
"FROM tblLinked LEFT JOIN tblLocal " & _
"ON tblLinked.ID=tblLocal.ID " & _
"WHERE tblLocal.ID Is Null"

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

If Not rst.EOF Then
lngRecords = CLng(rst.Fields(0).Value)
strInfo = strInfo & vbCrLf & CStr(lngRecords) & _
" record(s) have been added to the server"
End If

rst.Close

Set rst = Nothing
' ------------------------------------------------------------------



' ------------------------------------------------------------------
strSQL = "SELECT Count(*) AS UpdatedOnServer " & _
"FROM tblLinked INNER JOIN tblLocal " & _
"ON tblLinked.ID=tblLocal.ID " & _
"WHERE tblLocal.MyStamp<>tblLinked.MyStamp"

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

If Not rst.EOF Then
lngRecords = CLng(rst.Fields(0).Value)
strInfo = strInfo & vbCrLf & CStr(lngRecords) & _
" record(s) have been updated on the server"
End If

rst.Close

Set rst = Nothing
' ------------------------------------------------------------------

If Len(strInfo) > 2 Then
strInfo = "Assuming the local table has not changed:" & _
vbCrLf & strInfo
End If

MsgBox strInfo, vbInformation


Exit_Handler:

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

 

[DBG]

Finally had a few free moments, and got everything set up to this point.
Good code sample, very helpful.

Thanks,

-David