• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Replication using Access 97

I have looked all over and I am unable to find much information on this...when replicating data in Access 97, what happens with the records that contain errors?  For example, what happens if database A and database B both contain a record with the primary key = 1234?  In this case, we would have a primary key violation....what happens during synchronization?  Are these records dumped into a table where a report can be generated and the data manually cleaned?   From what I've read, Access will not try to resolve conflicts but I've not found anything that says what it WILL do in these cases.  Thanks!
0
Silvey
Asked:
Silvey
  • 16
  • 12
  • 6
  • +1
1 Solution
 
mgrattanCommented:
If both are new entries (not updates of old entries), then the record that was entered first will be kept.  During synchronization, an error will be generated; this error will surface when the database is opened (after synchronization is completed) and the conflict manager is launched.  You will be notified of the GUID, table name, and database name that contain the error.  In order to correct the error, you will need to open the replica database and delete the record that contains the duplicated primary key.

In order to minimize the risk of Primary Key duplication in replicas, it is recommended that you assign your Primary Key fields as AutoNumber data type with Random as the format instead of Incremental (actually, once the database has been replicated, AutoNumber fields are automatically changed to Random for both old and new fields in the database).

Hope that helps.

Mike
0
 
mgrattanCommented:
Addendum:  In the last sentence of my first paragraph ("In order to correct the error, you
will need to open the replica database and delete the record that contains the duplicated primary key.") you can also add that you have the option of manually changing the value of the primary key (as long as it's not an AutoNumber) in the replica database instead of deleting the record.
0
 
SilveyAuthor Commented:
So with this in mind, how would you handle this situation:  I have inherited an Access 97 application that contains a master and two replicas.  If the user enters data and an error occurs during synchronization, if I understand correctly, the user will receive an error msg. the next time they open their program...is there a way that I can control the errors from the master?  A user isn't going to know what to do with these errors when they open their program and guess who they're going to call.  How would you handle that?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
mgrattanCommented:
Is the Primary Key value for the new records determined by the user or by a business rule?
0
 
gregdaveyCommented:
I manage a site with 3 replica databases.

I used code to generate the primary keys for each table in each replica database.

To get around your problem I appended the site code eg BR, PR, KG to the front of each ID

eg. ID 1234
Brisbane will generate BR1234
Perth will generate PR1234
Kalgoorlie will generate KG1234

As you can see, the keys will never be the same in any copies of the replica.

Hope this helps

Greg
0
 
dhinkleCommented:
I have a replicated 97 db also, design master and 2 replicas. And the users are, well, rather severely technically challenged. But even THEY can handle the errors. When they are synchronizing it gives them a msg telling them they have errors and an option to correct them. I showed them how to use it and they don't have a problem. Access displays both versions of the record side-by-side and lets them choose which to keep.
0
 
SilveyAuthor Commented:
DHinkle:

How do you get Access to display the records and allow the user the option to choose the record to keep?  I just created some errors and all it gave me was the table name, record id, operation, and reason...when I clicked on "Show me how to correct Replication errors", it just took me to a help file.  What I'm seeing here is too advanced for users however, what you describe sounds much more user friendly.  
0
 
dhinkleCommented:
Silvey - I think we're in trouble now. I didn't do anything to make that happen. When I set it up for replication that's just the way it worked. I will look at it and see if there's a place to set that up but you're right about the documentation in this area. It really stinks! I'll let you know what I can find
0
 
dhinkleCommented:
I found this much - open the design master see if there is a function that is handling conflicts. The creator may have put in a custom conflict handler that is overriding the built-in Access handler. This I got from the Help file -

Settings

Set the ReplicationConflictFunction property to a text string that's the name of the Function procedure you wish to call. Note that the setting must be the name of a Function procedure; it can't be the name of a Sub procedure. Unless this property has been set, Microsoft Access calls the Conflict Resolver.
0
 
SilveyAuthor Commented:
Will this allow the errors to be corrected at the MASTER level or will the user be left to clean them up?  Or will this provide a simplified means to the user for cleaning up their own mess?
0
 
dhinkleCommented:
If you can get it to use the built-in Access resolver it happens on the client end. I haven't seen a conflict since I showed them what to do with it. They do everything themselves. They will see an msg telling them there are conflicts. They will have the option to click a box that says Resolve Conflicts. Then they will see a screen with the first conflicting record, one version on either side. They can compare the data and then they click a button at the bottom of the screen to choose the one to save. If there are other conflicts the next one will then show on the screen. They just keep resolving until they get through the list.
0
 
dhinkleCommented:
Sorry Silvey - it's been one of those mornings! If I had just read the help screen a little further down - this should help you---

To return control to the built-in Conflict Resolver again, remove this property from the Properties collection.

You can also add or delete this property on the Custom tab of the DatabaseName Properties dialog box, available by clicking Database Properties on the File menu.

Remarks

If the same record in a replicated database has been changed in one or more replicas, conflicts will result when you synchronize a replica with the replica set. When you choose to resolve these conflicts, Microsoft Access calls the built-in Conflict Resolver. The wizard presents each conflict to the user, who must manually determine which changed record contains the correct data.
0
 
SilveyAuthor Commented:
I do not see where there has been any kind of a custom function generated...I wonder why my conflict resolver appears differently than yours?
0
 
SilveyAuthor Commented:
What's happening now is the user receives a msg. when they open the program saying they have replication errors.  If they choose VIEW DATA ERRORS, they are taken to a screen that shows them the table name, record id, etc.  At the top of that screen, they have a button saying, "Show me how to correct replication errors"  When they press that button, it gives them an Access help file.  As you can see, this is too technical for most users.  I'd like to at least get the screen your users get as it sounds much more user friendly.

Resolve replication data errors

A replication data error can't be automatically resolved by choosing one version of a record over another using the Conflict Resolver. Instead, a data error requires that you find the record in your replica set that is causing the error and manually change the data or delete the record.

1     Read the text in the Reason box on the Replication Data Errors page. The Reason box describes the type of data error, and suggests steps you can take to reconcile the data in your replica set with the data in another replica set member.
2     Note the name of the replica set that is causing the error, and the name of the table containing the error. On the Replication Data Errors page, the replica set is identified in the Source Machine box and the next Path box. The table is identified in the Table Name
 box.

3     Select the RecordID value on the Replication Data Errors page, and copy it to the clipboard by pressing CTRL+C.
4     Close the Conflict Resolver.
5     Open the replica set that is causing the data error, if it isn't already open.
6     On the Tools menu, click Options.
7     On the View tab, select System Objects.
8     Open the table that contains the error, and click Filter by Form    on the toolbar.

9     Paste the RecordID value from the clipboard into the s_GUID field and click Apply Filter    on the toolbar. This locates the record that is causing the data error.

10     Update or delete the record, as appropriate.

For example, if a field value in the replica set violates a data validation rule being enforced by the Design Master, update the field value to follow the validation rule. If a record in the replica set has a key field value that duplicates a key field value in another replica set member, but has different values in the other fields of the record, delete the record. You can then add the record, with a new key value, back to the replica set.

11     Close the table and synchronize your replica set again.
0
 
SilveyAuthor Commented:
What's happening now is the user receives a msg. when they open the program saying they have replication errors.  If they choose VIEW DATA ERRORS, they are taken to a screen that shows them the table name, record id, etc.  At the top of that screen, they have a button saying, "Show me how to correct replication errors"  When they press that button, it gives them an Access help file.  As you can see, this is too technical for most users.  I'd like to at least get the screen your users get as it sounds much more user friendly.

Resolve replication data errors

A replication data error can't be automatically resolved by choosing one version of a record over another using the Conflict Resolver. Instead, a data error requires that you find the record in your replica set that is causing the error and manually change the data or delete the record.

1     Read the text in the Reason box on the Replication Data Errors page. The Reason box describes the type of data error, and suggests steps you can take to reconcile the data in your replica set with the data in another replica set member.
2     Note the name of the replica set that is causing the error, and the name of the table containing the error. On the Replication Data Errors page, the replica set is identified in the Source Machine box and the next Path box. The table is identified in the Table Name
 box.

3     Select the RecordID value on the Replication Data Errors page, and copy it to the clipboard by pressing CTRL+C.
4     Close the Conflict Resolver.
5     Open the replica set that is causing the data error, if it isn't already open.
6     On the Tools menu, click Options.
7     On the View tab, select System Objects.
8     Open the table that contains the error, and click Filter by Form    on the toolbar.

9     Paste the RecordID value from the clipboard into the s_GUID field and click Apply Filter    on the toolbar. This locates the record that is causing the data error.

10     Update or delete the record, as appropriate.

For example, if a field value in the replica set violates a data validation rule being enforced by the Design Master, update the field value to follow the validation rule. If a record in the replica set has a key field value that duplicates a key field value in another replica set member, but has different values in the other fields of the record, delete the record. You can then add the record, with a new key value, back to the replica set.

11     Close the table and synchronize your replica set again.
0
 
dhinkleCommented:
I have no clue. Anybody out there got any ideas? You must have gotten lucky like me and inherited your stuff without documentation!! I'll keep looking.
0
 
dhinkleCommented:
That is exact copy of a sample file I just saw that comes with Access. Pull up wzcnf80.mda and have a look. Sounds like somewhere in your program they have used this code.
0
 
SilveyAuthor Commented:
You are absolutely RIGHT - no documentation whatsoever.  The previous programmer traveled to the different locations whenever there was a conflict...I don't believe that should be necessary.  
0
 
dhinkleCommented:
Wati a sec - I just caused a conflict in my data and went thru the process. I have the View Data Error and View Design Error buttons but they are greyed out. You are getting a different error than I am. It is not a conflict error. That is a different selection. I wonder what the diff is between data error and conflict. Corrupt data?
0
 
SilveyAuthor Commented:
When you open the application, you are greeted with the following messagebox:  "This member of the replica set has errors from synchronizing changes with another member.  Do you wish to see those errors now?"  When you say YES,  you are taken to a RESOLVE REPLICATION CONFLICTS screen with a box listing the tables with conflicts.  (Nothing appears in the tables box), although there is a button on the right of it that says VIEW DATA ERRORS.  When you click on that, you are taken to the screen I described earlier that lists the table name, record id, etc.
0
 
SilveyAuthor Commented:
When you open the application, you are greeted with the following messagebox:  "This member of the replica set has errors from synchronizing changes with another member.  Do you wish to see those errors now?"  When you say YES,  you are taken to a RESOLVE REPLICATION CONFLICTS screen with a box listing the tables with conflicts.  (Nothing appears in the tables box), although there is a button on the right of it that says VIEW DATA ERRORS.  When you click on that, you are taken to the screen I described earlier that lists the table name, record id, etc.
0
 
SilveyAuthor Commented:
When you open the application, you are greeted with the following messagebox:  "This member of the replica set has errors from synchronizing changes with another member.  Do you wish to see those errors now?"  When you say YES,  you are taken to a RESOLVE REPLICATION CONFLICTS screen with a box listing the tables with conflicts.  (Nothing appears in the tables box), although there is a button on the right of it that says VIEW DATA ERRORS.  When you click on that, you are taken to the screen I described earlier that lists the table name, record id, etc.
0
 
dhinkleCommented:
That's what I mean - it doesn't seem to have a conflict with any of the tables. And since I don't know what the 'data error' means I can't recreate the problem. I have that View Data Errors button but it has never been available because apparently I've never had that kind of error. I HAVE seen the data go corrupt a couple of times and had to restore from backup but never the data error. I just ran it again to see if I could at least get a help button on that screen and of course no such luck.
0
 
SilveyAuthor Commented:
What kind of errors are you receiving?  The explanation I get for my errors all involve duplicate records...
0
 
mgrattanCommented:
Hi Silvey and dhinkle,

Looks like I've missed quite a bit of discussiont this morning!  

You are both receiving different errors based on the types of conflicts you are seeing.  dhinkle's conflicts are the result of an existing record in two replicas having been updated with different information between synchronizations.  Silvey's conflicts are the result of new records being entered in two different replicas and having the same Primary Key value.  Their are only two ways to resolve Silvey's type of confict; edit the conflict record at the replica and change the Primary Key value OR delete the record at the replica.

If the user's are being allowed to type in a value as the Primary Key you will need to change this process and automate it so that a sound business rule is used determine the Primary Key value for each new record.  Even this, however, can result in duplications.  You will probably need to train your users how to identify the conflict records and either change the Primary Key value or delete the record and re-sync the database to get the version of that record from the primary replica.
0
 
dhinkleCommented:
The 3 buttons are Resolve Conflicts, View Data Errors and View Design Errors. The only one I ever have available is the Resolve Conflicts button. Do you have that one on the screen that pops up? The other 2 on mine are always dimmed.

Duplicate records - how is your key field set up? I know that you cannot use just a regular AutoNumber. You have to use the random number to prevent the dups.
0
 
dhinkleCommented:
Hi mgratten,

Took me this long and you hit it on the first try :)) I bet it has to do with the way the key is defined. Wish you had looked in this morning :))))))))) I knew someone out there knew what was going on.
0
 
mgrattanCommented:
Actually, I've just reiterated what I said in my first comment (posted yesterday) and gave some more details....

;-)
0
 
SilveyAuthor Commented:
DHinkle and MGrattan:

You have both provided me with some helpful information.  Is it possible to award points to more than one person, or are you only able to award to one?  I've not encountered this before...thank you!
0
 
SilveyAuthor Commented:
DHinkle and MGrattan:

You have both provided me with some helpful information.  Is it possible to award points to more than one person, or are you only able to award to one?  I've not encountered this before...thank you!
0
 
mgrattanCommented:
Silvey,

You can post a separate question for one of the experts and just title it "Points for xxxxx - Question 20134202".  Just replace the xxxxx with the expert's name so he knows it's for him.  You can either post the new question for the full 100 points or you can ask Community Service to reduce the points on this question to 50 and then post your additional point award for 50.
0
 
SilveyAuthor Commented:
MGrattan, I've placed another "question" out there for you to accept your points...thanks for your help!
0
 
SilveyAuthor Commented:
MGrattan, I've placed another "question" out there for you to accept your points...thanks for your help!
0
 
dhinkleCommented:
Thanks a lot Silvey. Did changing your record key to a random number help?
0
 
SilveyAuthor Commented:
This application has additional problems that I would not have encountered as quickly if I did not have your suggestions.  Hopefully I will be able to rewrite it very shortly!  ;)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 16
  • 12
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now