Contextures Blog

Recently, I’ve been making changes to an Access database that someone else built. In most cases, that’s enough of a challenge, but this database has an extra hurdle – it’s set up for replication.

Access Database Design Master

One copy of the database is set up as a Design Master, and replicas are made from that copy. Everyone can make changes and additions in their copy, then all the copies are synchronized, to pull all the data together.

I hit a snag, right near the end of the development phase, so I’m posting this solution, in case it helps someone else — or me, later, when I need the code again. 😉

Don’t Move the Design Master

I’ll spare you the gory details, but all the structural changes have to be made in the Design Master. If that file is lost or corrupted, you can create a new Design Master from one of the replicas in the set.

My client sent me the Design Master to work on, and I’ve been busily making changes for several weeks. Now, it’s time to send it back, so they can use all the fancy new forms and reports.

Unfortunately, I learned that when the Design Master is moved from its original folder (i.e. when the client sent me a zipped copy), the moved copy becomes a replica.

Create a New Design Master

So, I had to get another copy of the database, with all the current data, and strip out everything except the tables. Then, I imported all the queries, forms and reports from the new version, and will send it back to my client.

When they get the database, they’ll make it the Design Master, and create new replicas for everyone to use.

It’s an annoying workaround, but we’ve done a few tests, and it works fine.

Make the Queries Replicable

It’s easy to do an import of all the forms, queries and reports that are in another database, and that step took just a couple of minutes. However, all the imported objects should come in marked as “Replicable”, because that is property setting in the other database.

The forms and reports were fine, but none of the queries had the Replicable property turned on. There are over 100 queries, and I wanted a way to turn on that property programmatically, not manually.

You’d think that would be easy, but I spend a long time searching in Google, Bing, and my shelf full of Access books. Nothing helpful appeared in the search results, but I didn’t give up!

Finally, I found an article, written in 1999, on the Microsoft website, and it had the code I was looking for — Implementing Database Replication with JRO

Set a Reference

The code has to run from another database, so I used a different copy of the client’s database. The database with the queries to update is closed.

In the database where I put the replication code, I had to set a reference to JRO in the Visual Basic Editor (Tools > Reference)

The Replication Code

The following code will change Replicable setting for the specified object to True.

Sub MakeObjectReplicable(strDBPath As String, _
                         strObjectName As String, _
                         strObjectType As String)
   Dim repMaster      As New JRO.Replica
   repMaster.ActiveConnection = strDBPath
   repMaster.SetObjectReplicability strObjectName, strObjectType, True
   Set repMaster = Nothing
End Sub

Loop Through the Queries

The sample code in the article only changed one query, but I wanted to change them all. The database I used to run this code had all the same queries as the target database, so I looped through its queries.

As you can see in the code comments, even though I’m updating queries, the object type is “Tables”. I tried “Queries” first, but that didn’t work, and then I noticed this warning in the Microsoft article:

Important   Even though the SetObjectReplicability property provides an ObjectType argument to specify the type of object you are working with, the argument only accepts a value of “Tables” for both queries and tables.

The code runs quickly, and this will be handy if we ever have to rebuild the Design Master. Use this at your own risk though – I’m certainly not a Replication expert, and it might not work in all cases.

Sub MakeAllQueriesReplicable()
Dim strPath As String
Dim strFile As String
Dim qry As QueryDef
strPath = "C:\Data"
strFile = "MyNewDesignMaster.mdb"
on error resume next
For Each qry In CurrentDb.QueryDefs
	'NOTE: object type 'Tables' is used for both tables and queries
	MakeObjectReplicable strPath & "\" & strFile, qry.Name, "Tables"
Next qry
End Sub

____________________________