Results 1 to 11 of 11
  1. #1
    NickMDal is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2017
    Posts
    18

    Access 2003 replication question

    Hi. We are still using replication. A 10 year stretch without major issues. Recent corruption of hub replica required recreation of the replica set.

    There is a design master, a hub, 2 (LAN) locals and 1 (VPN remote) local replica.



    1. I first TSI unreplicated a local replica and re-replicated it, making it the design master (global)
    2. I then created new hub (global) from DM and from hub, the 3 local replicas.

    Now when attempting synch from local to hub, the local replicas do not contain the path back to the hub. If I browse to the hub path and attempt sync, I get...

    "local or anonymous replicas must synch only to their designated hub replica"

    Is this because I need to first sync from the hub to the new replicas? I can't test that approach for a couple of days.

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, I never worked with Access replication, but have some years experience using SQL Server replication. When changing the replication we :
    * first stop the current replications
    * reconfigure the settings and startup the new replication with a fresh snapshot
    * on startup replicated tables on subscribing (local) servers are emptied and filled with the data from the publishing site.

    So in your case with access I would first create a non-replicated new database that contains all data. You could crate a new database and import all objects into this new database. This way no information from previous replications is retained.
    Create from this database new local replica's.

    As replication is not supported anymore in new Access versions you might consider moving to SQL server. As the (free) SQL express version does not contain the SQL agent, it is not possible using only SQL Express, but I believe there are possibilities to replicate from a full SQL server to local SQL express. Or you can use a combination of Powershell and SQL procedures to set up an automated replication process using only free SQL Express versions.
    Last edited by NoellaG; 08-01-2020 at 04:08 AM. Reason: typo

  3. #3
    NickMDal is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2017
    Posts
    18
    Thanks for helping. No possibility of upgrading Access or switching to another DB. We have a complex Access front end and a process that requires replication.

    I followed the standard procedure. I think the issue is selection of global/local and the only other parameter "prevent deletes." I've followed this creation procedure in the past with no issue. Must be something simple I'm forgetting.

    Only other possibility that comes to mind is TSI Unreplicator not clearing out all of the old replica data so that the new hub is misidentified and mismatched to the new local replicas.

    To bad no one else us still using this feature. It is really reliable. I don't believe it was buggy-ness that really prompted MS to discontinue. It was their desire to have users switch to their web based data apps. Abiility to replicate a desktop app on the fly gives our business an essential advantage.

  4. #4
    NickMDal is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2017
    Posts
    18
    I recreated the set again. Checked replicas table and all paths and UNC paths were correct. Before re-replicating, I checked the (TSI) unreplicated DB and there were no residual tables that would contain data about replication.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We have a complex Access front end and a process that requires replication.
    FWIW I've read that the reason for dropping replication support was that subsequent methodologies and technologies made it somewhat redundant. I'm referring to (for example) Terminal Services and apps like Citrix, which made it possible to access a TS or Citrix server where a db back end was on a LAN and the user could log in remotely to their own fe from anywhere as long as they had access to it. If it was due to web based Access db's as you say, then you'd have to wonder why support for web based Access db's has been dropped. It's interesting to note that the other technologies still provide a means of accessing an Access db remotely. So I wonder if your app really needs replication, or if you're not yet prepared to adopt any of the other technologies that would make replication a thing of the past.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if it is only data you need to replicate and not updates to forms/reports/code i.e. synchronisation, then it may be time for you to bite the bullet and upgrade.

    You can 'roll your own' in access using a number of left join queries all of which can be automated using vba, or as Noella says upgrade the backend to sql server which has data replication functionality. Express doesn't but here is a link as to how you can do it https://www.codeproject.com/Articles...in-SQL-Express

    I suspect it won't be too long, perhaps a couple of years, before you will be in a position where upgrading from 2003 is not an option which would result in requiring a complete rewrite of your application.

    But going back to your current setup - is the db split? each user with their own front end and replicated backend?

    If not have you tried decompiling/recompiling your code after you have created a new design master and before you create the replicas?

    And if not split, suggest you do this - if the front end is stable then you do not need replication - any changes you simple send out a new copy of the front end. You can still replicate the back end if necessary.

  7. #7
    NickMDal is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2017
    Posts
    18
    The DB is split. There is no code in the backend, just tables. All code is in the front end, which is stable. Each user has a copy of the front and replicates the back.

    For a moment, it looked like the problem was caused by my not letting the new DM immediately create a replica. I am able to create a replica set (dm, hub and local) on a single machine that synchs, but so far it only worked once or twice and then the error. Could this be some sort of network issue? The replica table has both local and unc paths.

    I am a sole proprietor and so upgrading would be too expensive. We're currently paying $180/hr just to ask questions that don't get answered.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    who are you paying $180/hr to and why?

    The technology you are using is 20+ years old. The last time I used the access replication process was 15 years ago when network speeds were slow!

    Since then I have 'rolled my own' for clients who literally had people in the field, but they would sync when they got back to the office.

    If you explain your process I might be able to suggest an alternative. From your description, I presume the master is on the server and accessed by the two office users and the replica on your remote users machine.

    I'll ping isladogs to visit this thread - he has access manuals going back to the stone age so might be able to fill any gaps in your memory

  9. #9
    NickMDal is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2017
    Posts
    18

    Hub lists itself 2x in MSysReplicas. Isn't this the cause?

    Click image for larger version. 

Name:	Replicas.jpg 
Views:	8 
Size:	124.9 KB 
ID:	42571

    Surely this is the cause of the problem. Since the error is...

    "local or anonymous replicas must synch only to their designated hub replica"

    The replica IDs expected by the initiator are not being found. Can't be much more complex than that.

  10. #10
    NickMDal is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2017
    Posts
    18
    WSI from Iowa. Was going to send them a link, but probably not now.

    Replication has been completely reliable since around 2007. It is the basic Access direct sync setup. Replicated design master with a hub replica on the same machine (both global replicas). All others are local replicas. 2 on desktops and 1 on a laptop on same network. One other desktop remote with sync by VPN.

    David Fenton set up our indirect sync. After he passed, we went with direct sync for all replicas. Reliable internet connections have really favored this approach.

  11. #11
    NickMDal is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2017
    Posts
    18
    Found the solution from David Fenton's post on MSGroups. Never use local or anonymous replicas. They orphan too easily. We set all terminal replicas to global and everything works perfectly!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-05-2014, 09:29 AM
  2. Converting Access Backend from Access 2003 to SQL Question
    By seattlebrew in forum Database Design
    Replies: 0
    Last Post: 03-07-2013, 07:14 PM
  3. Access 2003 Averaging Question
    By swicklund in forum Queries
    Replies: 1
    Last Post: 02-11-2012, 02:31 AM
  4. Replication ID Question
    By RayMilhon in forum Access
    Replies: 0
    Last Post: 12-29-2011, 11:44 AM
  5. Access runtime 2003 compatability question
    By TwdCncl01 in forum Access
    Replies: 1
    Last Post: 02-14-2011, 08:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums