Conflict management code
Tim
code I've written. I've got multiple remote users that I'd like to
manage conflicts automatically without having any intervention on
their part. I'm not getting many conflicts (I do my best to prevent
them programatically), but I do get one every two or three weeks.
After logging the conflicts in a replicated table, I delete the record
from the conflict table. I tried to delete the conflict table
altogether using a SQL DROP statement, but I get an error indicating
that the table is locked. Any ideas why and how I could mitigate
that? I'd prefer to delete the table when I'm done with it instead of
deleting each record individually.
Here's the code I'd like to run after every indirect synchronization:
<code>
Private Const JET_SCHEMA_REPLCONFLICTTABLES = "{e2082df2-54ac-11d1-
bdbb-00c04fb92675}"
Dim bolShowMsg As Boolean
Dim cnn As ADODB.Connection
Dim strDBDir As String
Dim rst As ADODB.Recordset
Dim rstBase As ADODB.Recordset
Dim rstConflict As ADODB.Recordset
Dim rstConflictLog As ADODB.Recordset
Dim rstConflictTables As ADODB.Recordset
Dim j As Integer
Dim k As Integer
Set cnn = New ADODB.Connection
'this gets the path of the current front end database
strDBDir = GetCurrentPath()
'this opens a connection to the back end replicated database,
which sits in the same directory as the front end
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & strDBDir & "SQDS_new_be.mdb"
cnn.Open
Set rst = cnn.OpenSchema(adSchemaProviderSpecific, ,
JET_SCHEMA_REPLCONFLICTTABLES)
Set rstConflictLog = New ADODB.Recordset
rstConflictLog.Open "Replication_Conflicts",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set rstConflictTables = New ADODB.Recordset
rstConflictTables.Fields.Append "TableName", adVarChar, 255
rstConflictTables.Open
Set rstBase = New ADODB.Recordset
Set rstConflict = New ADODB.Recordset
Do Until rst.EOF
rstBase.Open "Select " & rst.Fields("TABLE_NAME") & ".* FROM "
& rst.Fields("TABLE_NAME"), cnn, adOpenKeyset, adLockOptimistic
rstConflict.Open "Select " & rst.Fields("CONFLICT_TABLE_NAME")
& ".* FROM " & rst.Fields("CONFLICT_TABLE_NAME"), cnn, adOpenKeyset,
adLockOptimistic
'Now run through the tables and look at every field. If the
fields don't match, store them in the conflict table
'j will be the counter to identify the number of conflict
records in the conflict table
For j = 1 To rstConflict.RecordCount
'log the table name to delete it later
With rstConflictTables
.AddNew
.Fields("TableName") =
rst.Fields("CONFLICT_TABLE_NAME")
.Update
End With
rstBase.MoveFirst
rstBase.Find "[s_GUID] = '" & rstConflict.Fields("s_GUID")
& "'"
If rstBase.EOF = False Then
'a matching record in the rstbase table was found
'k will be the counter to determine the number of
fields in the conflict table/base table
For k = 0 To rstBase.Fields.Count - 1
'First, check to see if it's a replication field;
if so, don't log it
Select Case rstBase.Fields(k).Name
Case "s_ColLineage", "s_Generation", "s_GUID",
"s_Lineage"
'don't do anything here
Case Else
'use the base field name to find a match
in the conflict table field name
If Left(CStr(Nz(rstBase.Fields(k),
"null")), 255) <>
Left(CStr(Nz(rstConflict.Fields(rstBase.Fields(k).Name), "null")),
255) Then
'There was a mismatch found, so log
the mismatch
With rstConflictLog
.AddNew
'The WhoAmIForm is loaded upon
a user entering the data and contains login information
If
CurrentProject.AllForms("WhoAmIForm").IsLoaded Then
.Fields("UserName") =
Forms![WhoAmIForm].[Login]
Else
.Fields("UserName") = "Not
logged in"
End If
.Fields("Table") =
rst.Fields("TABLE_NAME")
.Fields("Field") =
rstBase.Fields(k).Name
'the ChangedFrom and ChangedTo
fields are strings that are 255 characters long. I the left function
to cut any memos down to size, the nz function to deal with nulls and
the cstr function to convert over to text
.Fields("ChangedFrom") =
Left(CStr(Nz(rstConflict.Fields(rstBase.Fields(k).Name), "null")),
255)
.Fields("ChangedTo") =
Left(CStr(Nz(rstBase.Fields(k), "null")), 255)
.Fields("DateChanged") = Now()
.Fields("RowGUID") =
rstConflict.Fields("s_GUID")
.Update
End With
End If
End Select
Next k
Else
'couldn't locate a matching record in the base table;
log that fact
With rstConflictLog
.AddNew
If
CurrentProject.AllForms("WhoAmIForm").IsLoaded Then
.Fields("UserName") = Forms![WhoAmIForm].
[Login]
Else
.Fields("UserName") = "Not logged in"
End If
.Fields("Table") = rst.Fields("TABLE_NAME")
.Fields("Field") = "could not locate record"
.Fields("ChangedFrom") = "could not locate
record"
.Fields("ChangedTo") = "could not locate
record"
.Fields("DateChanged") = Now()
.Fields("RowGUID") =
rstConflict.Fields("s_GUID")
.Update
End With
End If
'Delete the conflict row and move to the next
rstConflict.Delete
rstConflict.MoveNext
Next j
rstBase.Close
rstConflict.Close
rst.MoveNext
Loop
Set rstBase = Nothing
Set rstConflict = Nothing
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
</code>
David W. Fenton
news:1189186112....@d55g2000hsg.googlegroups.com:
> Set cnn = New ADODB.Connection
I stopped reading there because I think it's completely senseless to
be using ADO with Jet. Of course, I'm only thinking in terms of
Access applications -- if your app is VB, then I guess ADO is
somewhat viable, but if you're going to be deleting Jet tables and
the like, seems to me you ought to be using DAO all around.
I wouldn't worry about the conflict tables. Just empty them out and
leave them alone. Life is too short to worry about cosmetic issues
that are really beyond your control in the first place.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Mitch Bird
If you are using Access as a repository only and your app is written in VB6 then ADO is 'the only way to go'.
If you carry the logic through, then, if you are writing your app in Access, why not make them a compatible codebase to your other VB6/Access apps. Lastly, ADO is just plain newer and better. If I am not mistaken DAO has been in QFE ... like forever, while classic ADO matured over its lifetime to become one of the main reasons I held off 'going Dot Net' for some time.
My personal experience is that ADO just works better and is more reliable. It served me very well in an app that 'paid off my last house' :) . In that app all that Access was used for was to store tables; all of the RDBM, CRUD, and the interface were written in VB6.
It was recommended that DAO not be used for new development over ten years ago.
There is an ADO.NET but there will never be a DAO.NET!
No offense intended, cause you are pretty sharp and I may hit you up someday as I am currrentl locked in a nightmarish world of Access apps at work now; as I attempt to push my organization, 'kicking and screaming' into the 21st century using a whole new paradigm.
> Set cnn = New ADODB.Connection
>
>> On Friday, September 07, 2007 7:17 PM David W. Fenton wrote:
>> Tim <timothy...@hotmail.com> wrote in
>> news:1189186112....@d55g2000hsg.googlegroups.com:
>>
>>
>> I stopped reading there because I think it's completely senseless to
>> be using ADO with Jet. Of course, I'm only thinking in terms of
>> Access applications -- if your app is VB, then I guess ADO is
>> somewhat viable, but if you're going to be deleting Jet tables and
>> the like, seems to me you ought to be using DAO all around.
>>
>> I wouldn't worry about the conflict tables. Just empty them out and
>> leave them alone. Life is too short to worry about cosmetic issues
>> that are really beyond your control in the first place.
>>
>> --
>> David W. Fenton http://www.dfenton.com/
>> usenet at dfenton dot com http://www.dfenton.com/DFA/
>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>> ASP.NET Caching Concepts
>> http://www.eggheadcafe.com/tutorials/aspnet/78de4d09-b013-48c0-8d4a-bedd68f675f5/aspnet-caching-concepts.aspx
David-W-Fenton
news:2010102511...@eggheadcafe.com:
> I disagree, David.
> If you are using Access as a repository only and your app is
> written in VB6 then ADO is 'the only way to go'.
No, it's not the only way to go. It's one way to go.
> If you carry the logic through, then, if you are writing your app
> in Access, why not make them a compatible codebase to your other
> VB6/Access apps.
Or, flip the logic, why not make your VB6 app consistent with
Access. Access has a longer future than VB6, and DAO is the clear
future in Access. Classic ADO is unquestionably the dead-end
technology here, so to me, it makes no sense whatsoever to be
writing new code for it, particularly when there's an alternative
that has a future.
> Lastly, ADO is just plain newer and better.
No, it's not. Classic ADO is old and broken and abandoned by MS.
ADO.NET has very little to do with Classic ADO, but ADO.NET is not
something you can use in either VB6 or Access, so it's not really
relevant.
> If I am not mistaken DAO has been in QFE ... like forever,
You are mistaken. Since A2007, DAO is being updated to stay in synch
with the new ACE (which is just a renamed version of Jet). Classic
ADO hasn't been updated in about 10 years. It was dead in the water
when it was introduced into Access (A2000) because Classic ADO
wasn't .NET, so it had to be replaced with something that was .NET
compatible. ADO.NET really has nothing at all to do with Classic
ADO.
You are simply mistaken (likely because you are conflating ADO.NET
and Classic ADO, even though they have virtually nothing to do with
each other besides the name and a few cosmetic similarities that
have more to do with the nature of the task they are designed for
than with any actual commonality at any lower level).
> while classic ADO matured over its lifetime to become one of the
> main reasons I held off 'going Dot Net' for some time.
Classic ADO didn't mature. It was abandoned by MS when they realized
it couldn't work with the .NET platform, and was replaced entirely
by ADO.NET.
> My personal experience is that ADO just works better and is more
> reliable. It served me very well in an app that 'paid off my last
> house' :) . In that app all that Access was used for was to store
> tables;
This is perfectly consistent with what I said, that if you're using
something other than Access, ADO might be a more comfortable fit.
> all of the RDBM, CRUD, and the interface were written in VB6.
> It was recommended that DAO not be used for new development over
> ten years ago.
But that's because the development tools were being replaced with
.NET. You're recommending Classic ADO, which is just as much
inappropriate in the .NET world as DAO (since both are COM-based).
ADO.NET can't be used with VB6 nor with Access, so it's really not
relevant to your point.
> No offense intended, cause you are pretty sharp and I may hit you
> up someday as I am currrentl locked in a nightmarish world of
> Access apps at work now; as I attempt to push my organization,
> 'kicking and screaming' into the 21st century using a whole new
> paradigm.
You are mistaken on so many points that I'm afraid you have failed
to make your case.
It's all pretty much moot at this point, since with A2010 and
Sharepoint 2010, there's very little justification for using Jet
Replication in future development. It's certainly a very useful
legacy technology, but the whole ADO vs. DAO thing becomes
completely irrelevant in the A2010/Sharepoint scenario.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/