Solved

Replication using Access 97

Posted on 2001-06-12
35
204 Views
Last Modified: 2011-09-20
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
Comment
Question by:Silvey
  • 16
  • 12
  • 6
  • +1
35 Comments
 
LVL 14

Expert Comment

by:mgrattan
ID: 6183788
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 6183796
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
 

Author Comment

by:Silvey
ID: 6183850
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 6183864
Is the Primary Key value for the new records determined by the user or by a business rule?
0
 
LVL 2

Expert Comment

by:gregdavey
ID: 6184838
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6186723
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
 

Author Comment

by:Silvey
ID: 6186951
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6186971
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6187015
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
 

Author Comment

by:Silvey
ID: 6187027
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6187061
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6187082
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
 

Author Comment

by:Silvey
ID: 6187177
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
 

Author Comment

by:Silvey
ID: 6187213
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
 

Author Comment

by:Silvey
ID: 6187215
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6187216
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6187239
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Silvey
ID: 6187241
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6187254
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
 

Author Comment

by:Silvey
ID: 6187278
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
 

Author Comment

by:Silvey
ID: 6187282
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
 

Author Comment

by:Silvey
ID: 6187287
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6187320
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
 

Author Comment

by:Silvey
ID: 6187417
What kind of errors are you receiving?  The explanation I get for my errors all involve duplicate records...
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 6187499
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
 
LVL 1

Expert Comment

by:dhinkle
ID: 6187510
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
 
LVL 1

Accepted Solution

by:
dhinkle earned 100 total points
ID: 6187516
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 6187535
Actually, I've just reiterated what I said in my first comment (posted yesterday) and gave some more details....

;-)
0
 

Author Comment

by:Silvey
ID: 6196526
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
 

Author Comment

by:Silvey
ID: 6196695
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 6196766
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
 

Author Comment

by:Silvey
ID: 6196803
MGrattan, I've placed another "question" out there for you to accept your points...thanks for your help!
0
 

Author Comment

by:Silvey
ID: 6196806
MGrattan, I've placed another "question" out there for you to accept your points...thanks for your help!
0
 
LVL 1

Expert Comment

by:dhinkle
ID: 6196818
Thanks a lot Silvey. Did changing your record key to a random number help?
0
 

Author Comment

by:Silvey
ID: 6196831
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now