A friend of mine has a small business that he manages on a custom-developed Access 2003 application. He needed to migrate this database onto the newer Access versions, but the fact that the newer versions have a Ribbon interface instead of the usual toolbar interface meant that all the old custom toolbars are no longer as easily accessible. Instead, Access versions beginning with 2007 lump all the custom toolbar buttons into one ribbon called “Add-Ins” regardless of how they were originally organized and there is no way to edit the contents of that Ribbon.
Searching online I could not find any reliable way to convert the custom toolbars into an equivalent set of custom ribbons. Some websites suggested that custom toolbars are stored in a hidden Access 2003 table called
MSysToolbars, but try as I may I could not access that table (It could be because the Access file I was dealing with was an
.adp file which meant that all tables are hosted on an external data source, like SQL Server).
Not wanting to copy the custom toolbar buttons one by one into a custom Ribbon XML I wanted to see if I could reverse engineer the binary database file. I downloaded HexEdit and looked into the database file, searching for the names of the buttons on the custom toolbar and looking for patterns. Luckily there turned out to be an easily discernible pattern around each custom toolbar button, it looked like this (in hex):
80 00 00 00 00 07
That byte sequence preceded the definition of every custom toolbar button. The subsequent definition looked something like:
Custom Button Name<some non-ASCII bytes>Open form 'Some form name'<more non-ASCII bytes>
So it was pretty easy to write a python script that extracted the button names along with the forms they trigger (a similar trick can be used for custom toolbar buttons that call stored procedures or macros).
Here is the python script:
import os import re import sys a_name = sys.argv a_file = file(a_name, 'rb') a_size = os.stat(a_name).st_size a_contents = a_file.read(a_size) results = a_contents.split('\x80\x00\x00\x00\x00\x07') output_file = open('custom_toolbar_buttons.txt', 'w') for result in results: temp = result.replace('\x00', '').replace('\n','').split('Open form') if len(temp) > 1: clean_temp = re.sub(r'^[^A-Za-z0-9]+', '', temp) clean_temp = re.sub(r'[^A-Za-z0-9]$', '', clean_temp) output_file.write(clean_temp) output_file.write(' => ') output_file.write(temp.split("'")) output_file.write('\n') output_file.close()
Note that you may see some garbage in the resulting text file. My hypothesis is that sometimes Access moves data around within the binary and leaves some unused blocks with partial old data in them, which confuses the script above. But as far as I can see, if you ignore the garbage output, the script above produces a complete extract of all the custom buttons that open user-forms in Access.