Access Addin

Because Access add-ins (in previous versions of Access) didn't add buttons to command bars (menus or toolbars), you can't replace old code referencing CommandBars with new code referencing the Ribbon. However, you can replace a set of menu add-ins with a single menu add-in to load a custom Ribbon, and place the rest of your add-in's commands on the Ribbon. As an example, I made a version of my Extras 2007.accda add-in (the sample database for Chapter 14) and modified it to load a custom Ribbon.

The sample library database for this section is Extras (Ribbon).accda, i A'r r Q^ig If you are attempting to install an add-in in Access 2007 running on Windows Vista, you may get the security warning shown in Figure 15.14. Some special techniques are needed to get Access Ribbon add-ins to work in Vista; see the "Getting Your Add-ins to Work in Vista" sidebar for details. This is not a problem when installing add-ins for Access 2007 running on Windows XP.

FIGURE 15.14

A security warning when attempting to install an Access add-in for Access 2007 running on Windows Vista.

FIGURE 15.14

"r5.'r."r[ See PC Magazine Windows Vista Security Solutions (Wiley, 2007) for more information '..j-j.Iir- ___ ■ ■•______.________•. •______

on dealing with Vista security issues.

Tr T" r 'n Windows Vista, callback functions won't run from Ribbon buttons unless you include afcaa&J.jBgffl»- the name of the add-in database project before the function name, as I have done in the XML code listed below.

The technique for creating the Ribbon XML and storing it in a table is the same as for a regular Access 2007 database, as described in the previous section. The Extras (Ribbon) library database has only one add-in with three records in its USysRegInfo table, to load the ExtrasRibbon from the USysRibbons table. Its XML code is listed next:

<customUI

xmlns="http://schemas.microsoft.com/office/2 006/01/customui"> <ribbon startFromScratch="false"> <tabs>

<tab id="dbCustomTab" label="Extras" visible="true"> <group id="dbListingGroup" label="Listing Fields"> <button id="btnListTableFields" label=MList Table Fields" enabled="true" imageMso="CreateTable" size="normal"

onAction="Extras(Ribbon) .ListTableFields"/> <button id="btnListQueryFields" label="List Query Fields" enabled="true"

imageMso="CreateQueryInDesignView" size="normal"

onAction="Extras(Ribbon) .ListQueryFields"/> <button id="btnOpenOptionsDialog" label="Select Options" enabled="true"

imageMso="CreateFormBlankForm" size="normal"

onAction="Extras(Ribbon) .ExtrasOptions"/>

<group id="dbBackupGroup" label="Database Backup"> <button id="btnBackupFrontEnd"

label="Back up current database"

enabled="true"

imageMso="Copy"

size="normal"

onAction="Extras(Ribbon) .BackupFrontEndDB"/> <button id="btnBackupBackEnd"

label="Back up back-end database" enabled="true"

imageMso="Copy" size="normal" onAction="Extras(Ribbon).BackupBackEndDB"/> </group> </tab> </tabs> </ribbon> </customUI>

The USysRegInfo table with the Enable Extras Tab menu add-in records is shown in Figure 15.15.

FIGURE 15.15

The USysRegInfo table with a set of records for a single menu add-in.

Subkey

- Typ -

ValName

Value

j H KEY_CU RRE NT_ACDESS_PRO FILEUflenu ! Add-ins\&Enable Extras Tab

0

HKEY_CURRENT_ACCESS_PROFILE\Menu 1 Add-ins\&Enable Extras Tab

Library

fACCDIR\Extras (Rlbbon).accda

! HKEY_CURRENT_ACCESS_PROFILE\Menu 1 Add-insUkEnable Extras Tab

Expression

=LoadRibbons()

#Nam e?

Getting Your Add-ins to Work in Vista

Because of Vista's new security features, you need to take a few more steps when creating and installing an Access 2007 add-in to get it to install and work in Vista. The first step is to run Access as an administrator. Right-click the MSACCESS.EXE file in the Office 12 subfolder under the Microsoft Office folder, and select "Run as administrator":

continued continued continued

Open the add-in from the Access window, open the UsysReglnfo table and (if necessary) change the capitalization of "Menu Add-ins" to "Menu Add-lns" (capitalizing the I). This is only necessary for running add-ins in Vista; "Menu Add-ins" works fine for Access 97 through 2003.

One extra step may also be needed: If your Access 2007 add-in creates a Ribbon, and its buttons have callback functions, you also need to include the VBA project's name before the callback function name in each onaction argument in the USysRibbons table. Instead of just onAction="ListQueryFields"

you need onAction="Extras (Ribbon).ListQueryFields"/>

After making the above changes to the add-in's system tables, compile the add-in's code, and save and close it. Now you should be able to install the add-in and run its menu add-in(s), and its Ribbon buttons should work.

The Extras (Ribbon) add-in has only a single menu add-in: Enable Extras Tab, which runs a procedure (listed next) that copies the USysRibbons table into the calling database, and attempts to load the table.

Loading a table programmatically with the LoadCustumUI function doesn't always work; if the table is not automatically loaded, you can load it manually, as described earlier in this chapter (see Figure 15.8).

The basExtrasRibbon module also contains several callback procedures, which are the same as those in the standard Extras 2007.accda add-in, except for the ByVal control As IRibbonControl argument (plus one new procedure, for backing up a back end database).

How did I find the GUID for Office 12's object library? I found the path from the References dialog, then searched for it in the Registry, using the RegEdit utility; the GUID is on the line above the one listing the path.

Public Function LoadRibbons()

'Must be a function so it can be run from USysReglnfo table. On Error Resume Next

Dim i As Integer

Dim strRibbonName As String

Dim strRibbonXML As String

Set dbsCode = CodeDb

Set dbsCalling = CurrentDb

Add a reference to the Office 12 object library (if there isn't one already).

Application.References.AddFromGuid _

"{000C012 6-0000-0000-C000-000000000046}", 1, 0

Copy the USysRibbons table to the calling database, after deleting any existing table of that name, if there is one.

Set dbsCalling = CurrentDb strCallingDb = CurrentDb.Name

Set tdfsCalling = dbsCalling.TableDefs strTable = "USysRibbons"

Set tdfCalling = tdfsCalling(strTable)

DoCmd.SetWarnings False

If tdfCalling Is Nothing Then

Debug.Print strTable & " not found; about to copy it" DoCmd.CopyObject destinationdatabase:=strCallingDb, _ newname:=strTable, _ sourceobjectType:=acTable, _ sourceobjectname:=strTable

Else

Table found; delete it and then copy current version.

tdfsCalling.Delete (strTable)

DoCmd.CopyObject destinationdatabase:=strCallingDb, newname:=strTable, _ sourceobjectType:=acTable, _ sourceobjectname:=strTable Debug.Print " Old "; strTable _

& "deleted; about to copy current version"

End If

Set rst = dbsCalling.OpenRecordset(strTable)

rst.MoveFirst

Do While Not rst.EOF

strRibbonName = rst![RibbonName] strRibbonXML = rst![RibbonXML]

Load the Ribbon from the table record (if it has not already been loaded).

Application.LoadCustomUI _

customuiname:=strRibbonName, _ customuixml:=strRibbonXML rst.MoveNext Loop dbsCalling.Close

Set dbsCalling = Nothing

ErrorHandlerExit: Exit Function

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit End Function

Once the add-in has been loaded, you can select Enable Extras Tab from the Add-ins menu on the Database Tools tab of the Ribbon, as shown in Figure 15.16.

Selecting a menu add-in to load a custom Ribbon from an Access add-in.

See Chapter 14 for information on installing an Access add-in.

Close and reopen the database. If the ExtrasRibbon was not automatically loaded, select it manually, then close and reopen the database again; you should now see the Extras tab, as shown in Figure 15.17.

FIGURE 15.17

The Extras tab loaded from an Access add-in.

Access to Outlook

Home Create External Data Database Tools

SI List Table Fields ItfH List Query Fields □ Select Options gji Back up current database 4=1 Back up back-end database

The "Select Options" button opens the dialog where various options can be selected; the other buttons run functions to list table or query fields, or back up the current database or its back end.

Figure 15.18 shows the Select Options dialog:

FIGURE 15.18

The Select Options dialog for selecting a backup folder and entering prefixes to exclude.

FIGURE 15.18

The Ribbon is a new feature for Office, and (not surprisingly) even in the release version of Access 2007 it is not entirely stable, especially when the buttons run procedures from an add-in. You may find that you have to repeatedly uninstall and reinstall an add-in, unload and reload a Ribbon, and (most of all!) repeatedly close and reopen a database to get a Ribbon that calls add-in procedures to work. Hopefully this instability will clear up in an upcoming patch or service release of Office 2007.

If you select the List Table Fields command, a table is filled with the names of tables and their fields, and a message box asks if you want to print the report bound to the table now, as shown in Figure 15.19:

A table filled with names of tables and their fields.

A table filled with names of tables and their fields.

Selecting the "Back up back end database" command extracts the name of the back end database from the Connect string of a linked table, and presents an InputBox with a proposed save name, which can be edited as desired. The InputBox is shown in Figure 15.20

FIGURE 15.20

An InputBox with a proposed save name for a back-end database.

FIGURE 15.20

If you run this command in a database that has no linked tables, you will instead get the message shown in Figure 15.21:

FIGURE 15.21

A message when attempting to back up the back end for a database that has no linked tables.

"t^eare rr- linkecltables in this-ii^lgtigss; filn^e H"i~rt:1 if Databasa^Eiftfiiarid instead

The procedures for the "List Table Fields", "List Query Fields", "Select Options", "Back up current database", and "Back up back end database" buttons are similar to those in the Extras 2007.accda add-in, except that they use the ByVal control As IRibbonControl argument that is needed to run them from Ribbon buttons; their code is not listed here.

0 0

Post a comment