Re: Store SQL Timestamp locally for comparison

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

Thanks,

-David

"Brian Wilson" wrote:


"DBG" <DBG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5A931A51-BD48-43F4-97D8-A5556D55A2BB@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Store SQL Timestamp locally for comparison
    ... deleted from the server. ... Dim rst As DAO.Recordset ... Dim lngRecords As Long ... Set rst = dbs.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)
  • Server Object Error.
    ... Server object, ASP 0177 ... set mycon = server.CreateObject ... set rst = server.CreateObject ... Windows 2000 Server with SP4, ...
    (microsoft.public.inetserver.asp.db)
  • Re: Reading Access DB: get someteimes Data - sometimes not
    ... I don't get no data on this specific server - it works fine until a certain time, after this I only get EOF and after another while the server crashes. ... "Val Mazur" wrote: ... > Set rst = cmd.Execute ...
    (microsoft.public.data.ado)
  • Need Help | Rolling out new App. | Trouble with some computers.
    ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ... ' Data file not found in application folder - try to ask the user ...
    (microsoft.public.access.modulesdaovba)
  • Re: Requerying ComboBox in NotInList event
    ... Response As Integer) ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ... Dim lngBlankFound As Long ...
    (comp.databases.ms-access)