Make All Queries Replicable in Access

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.

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


Top 100 Canadian Singles in Excel

canada If you’re looking for love, move along — the “Canadian Singles” in the article title refers to hit songs, not eligible bachelors. Last week, a new book was published with a list of top 100 Canadian singles, based on a poll of music professionals and fans.
In his J-Walk blog, John Walkenbach posted a link to the Canada’s Top 100 Singles list, and there was a lively discussion in the comments section.

Top 100 Canadian Singles List

No discussion is complete without a spreadsheet, so I copied the list into Excel, and cleaned it up. To make it more interesting, I found the release date for each hit song, and split them into decades, using the FLOOR function.
From that data, I created a pivot table, showing the count of songs from each decade, listed by rank. Was most of the best music released in the 1970s, or were most of the voters from that era?
The top 100 songs are grouped by 10s, to summarize the data.
I added conditional formatting to highlight the decades with the largest number of songs.

Repeat Pivot Table Item Labels

A new feature in Excel 2010 pivot tables is the ability to repeat the field item labels. In another copy of the pivot table, I put the decade in the row label area, and changed the pivot table report layout to Outline Form.
Then, I right-clicked on the Decade field, and clicked Field Settings. On the Layout & Print tab, I added a check mark to Repeat Item Labels, and clicked OK.
After changing that setting, the decade is repeated in each row, instead of showing just once, at the top of the section.

Download the Top 100 Canadian Single File

To see the list, and create your own pivot table, you can download the sample file. There’s a Top 100 Canadian Singles list in Excel 2007/2010 format, and Top 100 Canadian Singles list in Excel 2003 format.

The Excel Newsgroups Disappeared

helpicon In the past, I highly recommended the Excel newsgroups as a place to go for help. Earlier this month, Microsoft shut down their newsgroup servers, where thousands of people every month had gone to post their Excel questions. Over the past several years, I spent lots of time in those newsgroups, reading and answering questions. I was able to help people, and learned lots from the questions and answers that were posted there.
The people who visited the newsgroups on a regular basis might have noticed the announcements about the impending shutdown. But most people probably visited the newsgroups only a few times a year, when they needed help. They didn’t see the shutdown coming!
It’s not like going to your favourite store, and finding a “We’ve Moved” sign, with helpful directions to the new location. No, it’s like going to that store, and finding out that the building has magically disappeared overnight. There’s no clue as to what happened, or where to find them.

Where Do You Go Now?

Since the newsgroups disappeared, several people have emailed me, to find out where they went, and where to get Excel help now.

Microsoft Forums

One option is to use the web-based forums that Microsoft has set up:

[update 2010/07/01] There’s a list of deleted newsgroups, and links to their related web-based forums here:
To post a question or answer in the Microsoft forums, you’ll need to register, and sign in, using a Microsoft Windows Live ID. After you’ve registered, and set up a profile, you can follow Ron de Bruin’s step-by-step instructions for setting up and using the Community Forums NNTP Bridge. The bridge lets you connect to the forums with a newsreader, like Outlook Express or Thunderbird, which I prefer over the web interface.

Public New Servers

If you don’t like web-based forums, you can use a public news server, like to access the Excel newsgroups. Even though Microsoft pulled the plug at its end, other servers carry the newsgroups, so there will still be some traffic, although much less than previously.

Google Groups Search

If your search skills are good, you can use Google to find an answer to your Excel questions, with either a general search, or a newsgroup search. If nothing turns up there, the Microsoft Knowledgebase is another good place to search for Excel solutions.

Cry for Help in Twitter

As Twitter becomes more popular, people even post their Excel questions and comments there. There are examples every weekday in my Excel Twitter posts.

Get Good Results

If you’re posting a questions in one of the forums or newsgroups here are a few suggestions for getting the best results:

  • search the forum first,  to see if your question has already been answered
  • some forums have multiple sections for questions — read the overviews, and select the best venue for your question
  • in your question, provide context, such as what version of Excel and operating system, any error messages, etc., and show some sample data, if possible
  • explain what you’ve tried so far, and other suggested solutions that didn’t solve the problem (include links to those suggestions, if possible)
  • check back frequently, to read any replies and answer any follow up questions
  • even if you’re frustrated, stay calm and polite, and remember to thank the people who helped, or tried to help you

Other Microsoft Resources

There are plenty of training resources on the Microsoft site, including some for Office 365.

Your Recommendations

When I posted about finding Excel help last summer, commenters recommended as a good place to get Excel help. There are other Excel forums too, like the Excel User Group, which is run by Excel MVP Nick Hodge.
[updated 2010/07/01] You can also ask questions on the Excel-L list: .
Where are you finding or providing Excel help these days, now that the Microsoft newsgroup servers have been shut down? Please share your suggestions in the comments.

Future of Productivity Giveaway Winner

Did you enter the Excel 2010 Future of Productivity Giveaway, that ended at noon yesterday, Tuesday, June 8th? The giveaway prize was a copy of Microsoft Office 2010, a Flip video camera, and a Seagate 1TB hard drive.
Of course, I used Excel to select the winner, from all the valid comment entries. I typed the comment numbers onto an Excel worksheet, with RAND functions in the adjacent column. Then, I calculated the workbook 3 times, to mix up the RAND results.
Finally, I sorted the RAND column in ascending order, and the comment number at the top was the winner. To satisfy the auditors, here’s a video of the random comment number selection.

And the Winner Is…

As you saw in the video, the random number selected was 22 — the comment by Naomi B. Robbins. Congratulations, Naomi! You win the prize package:

  • one (1) copy of Microsoft Office 2010: Home and Business
  • a Flip Ultra HD Camcorder
  • a Seagate FreeAgent Desk Hard Drive (1 TB)

The prize value is approximately $589 USD, and someone from Ignite Social Media will be in touch with Naomi to arrange delivery.

Thanks for Entering

Thanks for entering the giveaway, and thanks to Ignite Social Media for sponsoring it. I hope you saw a few new features in Excel 2010 that you’re looking forward to using!

Resolve to Stay Safe in Excel

As the storm clouds rolled in yesterday afternoon, I tweeted about saving my Excel files more frequently, as a safety precaution. There were a couple of responses, asking why I didn’t use Excel’s AutoSave feature, and Jon Peltier reminded me to use AutoSafe — Jan Karel Pieterse’s free add-in.
I mentioned AutoSafe in my Excel New Year’s Resolutions 2010 blog post, oh so long ago. Of course, I’ve kept all my resolutions — how about you?
And speaking of safety, remember to enter the Excel 2010 Future of Productivity Giveaway. You could win a copy of Microsoft Office 2010, a Flip video camera, and a Seagate 1TB hard drive — that should keep plenty of your data safely stored!

Installing AutoSafe

Even though I’m a creature of habit, and like to manually save my files, or click my Backup macro button, I decide to install AutoSafe. Good timing too, since Jan Karel uploaded a new version on June 1st.
While downloading AutoSafe, I also grabbed a copy of the companion add-in, AutoSafe VBE. It backs up your Excel code, so that should also be handy to have.
I unzipped the download file, and double-clicked on the setup.exe file. The installation wizard warned me to close my Excel files, and the installation was quick and easy.

Setting up AutoSafe

When I opened Excel 2007, the add-ins appeared on the Ribbon’s Add-ins tab. Click the AutoSafe command to open a dialog box where you can change the settings.
Choose the folder where you want the backup files stored, and the interval for the saves, and select any other options you want. To check for new versions, click the Update button.

Setting Up AutoSafe VBE

The settings for AutoSafe VBE are similar, and you can also set the number of generations that you want to save. The CleanUp button clears out all the old files for you.

Old Habits

It’s hard to break old habits, so I’ll probably continue to press Ctrl + S every few minutes, to save my work. It won’t hurt to have some extra help though, especially when working on code revisions. Thanks Jan Karel, for this wonderful free add-in.
And I don’t know why, but typing “old habits” reminded me of The Flying Nun. Ah yes, the golden age of television! Here’s the opening segment, for those who don’t know what I’m talking about.


Back In Time With Microsoft Excel

A very nice email, from someone who visited the Contextures website, made me think about how long I’ve been using Excel. My guess was that I’d started around 1987, so I fired up the old Mac laptop, and dug some old floppies out of the storage cupboard.
The laptop is a Mac PowerBook 170, from late 1991, and it’s running the Mac version of Excel 3.0. There’s a Mac 128K model in the back room too, but I didn’t have the strength to dig that out. (I hope the producers of the tv series, The Hoarders, don’t call me now.)
As you can see, the application files were much smaller in Excel 3.0. Of course, that still took a good chunk out of my 40MB hard drive.

Flashy Excel Charts

Even though the machine didn’t have colour, I was still able to make some pretty flashy 3-D charts in the old days. I have no idea what this was supposed to show, but maybe it was the results of an Olympic ski jumping event.
I’m sure that I never wasted any time playing TETRIS, when I was supposed to be working on Excel files.

Sorting a List

Way back then (and until Excel 2007), we were only able to sort by 3 levels, and we couldn’t sort by colour. That didn’t matter much to me, since I didn’t have colour!

My Oldest Excel File

There may be something older on a floppy disk at the back of the storage cupboard, but I finally found an Excel file that I’d worked on in April 1987. I was creating some Excel training files, to be used by Apple vendors.

The Excel 3.0 File and Excel Toolbar

Here’s what the file looked like, and it’s interesting to see the minimalist toolbar too. Do you remember what all those icons were for?

How Long Have You Used Excel?

There’s an Excel poll on my Debra D blog, so if you have a minute, please go and answer the question – How Long Have You Been Using Excel?

It’s About Time

When I was thinking of a title for this blog post, a really old (and really bad) tv series popped into my head – It’s About Time. The show was about two astronauts who accidentally break the time barrier, and go back to prehistoric days, where they live with a cave family. In a strange coincidence, Wikipedia says that one of the astronauts in It’s About Time was named Mac. By the way, they changed Imogene Coca’s character name to Shadd, after the pilot episode.
The series was by the creator of Gilligan’s Island, and used sets, props and music from that series. Here’s a clip from YouTube that shows the opening and closing credits and theme song. Please don’t blame me if the song gets stuck in your head.


Automatically Update Access Front End Files

When sharing an Access database, you should have the data in one file (the back end) and the queries, reports, forms and modules in a separate database (the front end).
Give each user their own copy of the front end, linked to the shared back end.
Keep a master copy of the front end file, tucked away in a safe place. If you need to update the front end, such as adding new features to a form, make the changes in the master copy of the front end file (make a backup copy first!)
To distribute the revised front end to all users, you can use a utility like Tony Toews’ Auto FE Updater. I use this utility for most of the databases that I’ve installed at client sites, and it works beautifully. The utility adds a shortcut to the user’s desktop, which they double-click to open the database. If the master copy of the front end has been updated, the utility downloads a copy of the revised version.

Remember to Compile

The only problem that I’ve had was with one user, Mary, who has Access 2000 installed. I’m using Access 2003 to update the master file, and all the other users have Access 2002 or 2003 installed.
Sometimes, after I’d updated the master file, Mary couldn’t open the new version. Error messages would appear, and the VBA code looked like pieces were missing. Very strange.
When I made the latest change to the database, I compiled the code before closing the file.
It dawned on me that maybe I’d occasionally forgotten to do this, and that could be causing the problem. I checked the previous version, and it wasn’t compiled. When I uploaded the new compiled version, Mary was able to open it with no problem.
That’s not definitive proof that compiling will prevent future problems, but I’m keeping my fingers crossed.

Sort a Book List in Word

You don’t have to put all your lists in Excel – Word has a Sort feature too. And Word has an extra feature that makes it easy to sort a book list.
For example, in this list, several of the book titles start with “The”. When sorting the list, I’d prefer to ignore that word, and sort by the second word in the title. (Picture books for 2-year-old boys sure have interesting titles!)

Format as Hidden Text

I’ll format those “The” words as hidden text, before sorting.

  1. Select all the titles in the book list.
  2. On the Ribbon’s Home tab, click Replace
  3. In the Find What box, type ‘The ‘, including a space character, and with an upper case T.
  4. Click the More>> button, to see the options.
  5. Add a check mark to Match case
  6. In the Replace With box, type ‘The ‘, including a space character, and with an upper case T.
  7. Click the Format button, and click Font.
  8. All the Effects should have grey check marks. Click Hidden, to add a black check mark.
  9. Click OK to close the Replace Font dialog box.
  10. Click Replace All, to replace the text, and click any confirmation messages that appear.
  11. Close the Find and Replace dialog box.

Sort the List

When the text is hidden, and hidden text is not visible, that text will be ignored when sorting.

  1. With all the book titles selected, click the Sort button on the Ribbon’s Home tab.WordSortRibbon
  2. Click OK to sort the list.

Note: In Excel 2003 and earlier versions, the Sort command is on the Table menu.

Show All Hidden Characters

To toggle the hidden characters on and off, use the keyboard shortcut Ctrl + *  (Ctrl + asterisk)
Note: This is an asterisk, and you may have to use the Shift key with the Ctrl key.

Show the Hidden Text

If you’d like to see the full book titles, including the hidden text, but not the other hidden characters, you can change an option in Word.

  1. Click the Office button, then click Word Options.
  2. Click the Display category, and add a check mark to Hidden text, in the Always show these formatting marks on the screen section.

Note: In Word 2003 and earlier versions, the Hidden Text check box is found in Tools>Options, View tab.
The characters formatted as Hidden Text will have a dotted underline.

Identify a Contact in Outlook

Occasionally you might get an email in which the sender mentions someone else. In the example shown below, the client promises to ask Xavier to contact you. That name sounds vaguely familiar, but perhaps you can’t remember exactly who Xavier is, or what role he has in the client’s company.
If you’re using Outlook 2007, it can help you remember.

Who Is…

Right-click on a name in the body of an email, to open a shortcut menu.
Click Who Is…, and Outlook will check your contact information, to see if there’s anyone with that name.
If there’s more than one person with that name, a Check Names dialog box will open, with a list of possible matches.
Click on a name in the list, then click OK, to open that person’s Contact information.
If there’s only one person with the name, their Contact information will open automatically.

Change Signatures in Outlook

I’ve got a few different Signatures set up in Outlook 2007. Most of the time I use my business signature, but occasionally I’d like to use one of the others. Usually I use the Ribbon commands, but I’ve just discovered a mouse shortcut that makes it much easier. I’ll be using the shortcut from now on.

Use the Ribbon

To insert a different Signature with the Ribbon commands:

  1. Create a new Outlook message
  2. On the Ribbon, click the Insert tab.
  3. Click Signature, then click a Signature name, or click Signatures, to open the Signatures and Stationery dialog box.


Use the Mouse Shortcut

With a mouse shortcut, it’s even easier to change or create a Signature.

  1. In a Outlook message right-click on the Signature
  2. In the popup menu, click a Signature name, or click Signatures, to open the Signatures and Stationery dialog box.