Button to Replicate Over Multiple Network Drives (

Topic

New Reply

I am creating a button to replicate backend databases from the frontend. Since users may map to the drive with different letter assignments I have tried using the network addresses.
Access thinks the network address is a directory on C drive. I receive the Runtime Error 3044.
Is there a way to use network addresses independent of a specific letter drive?

Private Sub cmdReplicateWithMsp_MilesAve_Click()

Dim dbsMasterReplica As Database

‘Opens the master replica – mimv06siteinfoHESContractorsContractorsA97_be.mdb
Set dbsMasterReplica = OpenDatabase(“mimv06siteinfoHESContractorsContractorsA97_be.mdb”)

‘Sends changes made in each replica to the other – the MSP database replica.
dbsMasterReplica.Synchronize “mieb01siteinfoHESContractorsReplica_2ContractorsA97_be.md”, dbRepImpExpChanges

‘Sends changes made in each replica to the other – the Miles Avenue database replica.
dbsMasterReplica.Synchronize “miesc0siteinfoHESContractorsReplica_1ContractorsA97_be.mdb”, dbRepImpExpChanges

dbsMasterReplica.Close

End Sub

Reply | Quote

Replies

The path to your master db only has one backslash at the start instead of two – i.e. it should be mimv06...
Does that fix it?

Reply | Quote

Yes, that works. All I needed was another “” in the first address. I also found an error in the second address, the file extension was “md” instead of “mdb.

I tested it and it worked!

Thank you very much.

Reply | Quote

It looks like you are using direct synchronization to send changes to two different computers. Are they on the same LAN? If so, why don’t you just use one of them? If they are not on the same LAN, are you aware that using direct synchronization over a WAN is not recommended? In a WAN situation, Indirect Synchronization as supported by the Replication Manager software is much more reliable. Lots of information at http://www.trigeminal.com[/url%5D

As for your original question, UNC names are acceptable for doing direct synchronization, so you don’t need to use consistent drive mappings.

Reply | Quote

Thank you for your response. I have bookmarked the page and will check it out.
The problem was that I had typos in the address.

Reply | Quote

I read some of the articles on replication on trigeminal.com.

My application is to use an Access97 backend for the data (all the tables) and Access 97, 2000, & XP frontends.

I plan on using a button on the startup form to replicate, two way replication, the Master and two replicas.

Will I have problems in replication with this scenario?

Reply | Quote

Replication isn’t your only problem in that scenario. An Access 97 backend gives very poor performance against a 2000 or 2002 front end, replication aside. If you are working on a network, the performance will be even worse.

Reply | Quote

Oh boy! The only thing that may save this is that there are infrequent updates to the data, only by 2 or 3 people. The database will be used mostly by users searching the data and the number of records is unlikely to exceed 2000 records for the next decade.

Would an application spread over three network drives be a good candidate for SQL server?

I really like Access, but the conversion to new versions is really a pain.

Reply | Quote

That small an application wouldn’t likely be a candidate for SQL Server, but I’m confused by your description. Synchronizing multiple servers is not simple or foolproof and I’m confused by what you’re doing and why. The design master should never be directly synced to multiple replicas. You should have an intermediate replica that syncs to the 3rd generation replicas on the several servers. Trust me, the design master will blow up at some point and you need that intermediate replica to recover as a new design master. Alternatively, the intermediate replica may blow up and you need the design master to create a new middle level and generate new replicas for the other locations. Replication is a two way process, and all you need do is sync the middle level to the design master and then sync the 3rd generation replicas with the middle level. Structural changes are made first during synchronization, then data changes are synced. With that in mind, you should never try to do both simultaneously or you’re in for difficulties. In other words, if you make a design change to the tables in the design master, sync that change before there is any data manipulation.

Are you creating multiple versions of the front end and distributing that to several servers? How are people using the data for searches? Do they have custom interfaces for it, are they querying, or what? A replicated table has a different structure from one that is not replicated, so you need to take that into consideration.

Reply | Quote

Adding my , if you can avoid using replication, do so. I have had to unreplicate several client databases where they needed to upgrade to a later version, and it’s a major pain. In addtion, in order to synchronize a replica of the backend with the design master of the backend, you either need to do it from the backend, or you need to write lots of VBA code to run in another database (like the front-end) including the conflict manager. Not an approach I would recommend. So why do you need replication? About the only situation where we ever use it is when you have copies of a database on a laptop that travels and is only occasionally connected to the LAN. Tell us more about your situation, in particular about your network resources and we may be able to suggest other alternatives.

Reply | Quote

This is my first replication project. I chose trying replication for the following reasons. The users are spread over three plant locations and the response time connecting to a drive that is located at a different plant location is slow.

My plan is to have frontends for A97 and XP, (user and editor versions). The backend is A97 with just tables in it. I would like to have a frontend for the editors, 3 of them, which would allow them to click a button to update the master replica and the other replica (replicas, master, Rep1 & Rep2). The bulk of users would just search for data on forms in a User frontend (A97 or XP).

Should I open the backend and then run the replication? Any advice is appreciated. Thank you for your input.

Reply | Quote

Replication is a technology well suited to your situation, but before embarking on replication, you must consider some very important issues. Within the replication technology, there are three flavours of synchronization. DIRECT sync is what happens on a LAN, when the two replicas that are to be synchronized are in direct contact with one another. INTERNET sync is supported by a software called Replication Manager, but internet sync is shunned by most replication experts. INDIRECT sync is also supported by Replication Manager, and is the method favoured to use over a WAN by most replication experts.

Therefore, if your physical setup goes outside your LAN and involves the use of a WAN, you are strongly urged to use Replication Manager and INDIRECT synchronization. DIRECT sync may appear to work in the short term, but is prone to database corruption over a WAN because both replicas must be opened simultaneously over a low-bandwidth connection that is potentially prone to flakiness.

Replication Manager was included with the Office Developer’s Edition of Access 97, and with the similar (but differently named) products for A2000 and (I think!) A2002. It is not available for A2003, but apparently the version from A2002 will work. I have not used anything later than RM from A97.

If you use Replication Manager, then it includes all the scheduling tools that are necessary to keep all replicas up to date. There is no need for the users to even concern themselves with that task. Note: do NOT include your design master in the synchronizing schedule. You (ie, the developer) should synchronize with the DM only as required to propogate design changes, or to prevent it from becoming ‘stale’.

Second major issue is whether or not a frontend that uses Jet 4 (ie, Access 2000 and later) is able to edit a backend that uses Jet 3.5 (ie, A97). I tried this once with an in-house application, and if I recall correctly, edits are prohibited under these conditions. What this means is that your editors MUST use Access 97. That may or may not present a problem for you…

There is a wealth of information about replication on Usenet news. http://groups.google.ca/group/microsoft.pu…n?lnk=srg&hl=en%5B/url%5D

Good luck!

Reply | Quote

Thank you for your input.

I am abandoning replication in light of new events. The other editors are physically moving to my plant site so that keeping records synchronized is not the issue that it was. I am going to be using “DoCmd.TransferDatabase acExport” so that I copy the updated tables to the Public User’s six versions of the database, three network drives and two versions (97 & XP). I have buttons that transfer just the tables that were updated.

Your comments on replication are very interesting and I will bookmark this thread for future use.

Reply | Quote

That sounds better than trying to use replication, but I don’t understand the need for having it available on three network drives, and with two flavors of backends. Working with Access 97 backends using XP front-ends is not desirable of course, but have you considered using SQL Server (either the MSDE or a full flavored version)? It offers many advantages, as you can connect to it using both 97 and XP – of course you would still need separate front-end versions. More food for thought.

Reply | Quote

I need it on three network drives as the drives are located in different cities and the response time over the networks is quite slow.

I was planning on using just an Access backend, but since all the editors have moved to my site I have decided not to use a backend. The editors will use Access XP to edit the data and the user versions data will be updated using a button to copy the updated tables to the Access 97 and XP user copies of the database. “DoCmd.TransferDatabase acExport…”

I thought about SQL server but since there will not be many records SQL Server is overkill. I would like to get some experience using SQL Server, perhaps in the future.

Thank you for your input.

Reply | Quote

IIRC, a replicated A97 database is *unable* to be updated by anything other than an A97 frontend.

Then there are all the usual caveats about never moving a replica once it has been created, except with the tools provided by Replication Manager, etc. Replication is not for the faint of heart.

Reply | Quote