Tuesday, October 19, 2010

Protecting Your Application when Upgrading to Access 2010 from Access 2003

Here is a question and answer from the UK Access Users Group
I am not a huge fan of Switchboard Manager in Access but it does save a bit of form coding and does serve a purpose. Anyway, I have one in an Access 2003 application which works fine.

Today, I opened the 2003 application in Access 2010 for the first time.

Our company it moving to 2010 early next year so I am playing. I was quite expecting to have to upgrade the application to 2010 or at least, recompile it but I didn't seem to need to.

Once I got around the Security Macros stuff all was fine but a strange thing happened with the Switchboard Manager form.

The application opens with a Home (Startup) form and that looked fine with just the ribbon visible at the top (not sure about the ribbon being visible for a deployed [shipped] application). A button is available on the Home form that opens the Switchboard Manager. When the SB Mgr form opened, the left hand navigation pane was suddenly visible with access to all the database objects - tables, forms, queries, modules etc.

Has anyone experienced this or have any ideas about stopping the Navigation pane suddenly appearing? I don't want Users poking around in the queries etc.

Thanks,  Andy.

A. from Bob Cresswell

I hide the ribbon
DoCmd.ShowToolbar "Ribbon", acToolbarNo

and disable the Bypass Key on Startup (as I am Macro-intolerant these statements are in my Splash form which is the start up form in the application).

SetProperties "AllowByPassKey", dbBoolean, False

The Navigation Pane is hidden using the Access Options.

For authorised users, I have an option on the Main Menu (or Switchboard) that gives them options to show the ribbon, navigation pane and enable the Bypass key (close database and restart with shift required for this).

I have three check boxes on the form, and an Apply button has this code:

' Show/Hide Ribbon
If Me!chkShowRibbon Then
  DoCmd.ShowToolbar "Ribbon", acToolbarYes
  DoCmd.ShowToolbar "Ribbon", acToolbarNo
End If

' Enable/Disable Bypass Key

SetProperties "AllowBypassKey", dbBoolean, Me!chkBypassKey

' Show/Hide Navigation Pane

If Me!chkShowNavigationPane Then
  Application.SetOption "Show Navigation Pane Search Bar", True
  DoCmd.SelectObject acTable, , True

  DoCmd.SelectObject acTable, "tCATVersion", True
  DoCmd.RunCommand acCmdWindowHide
End If

Friday, October 08, 2010

Access Unlimited Newsletter October

Includes these topics

Data Macros in Access 2010

Over at Database Journal, Danny Lesandrini writes a practical introduction in Access 2010 data macros. This covers "the AfterUpdate event of the table and figuring out how to debug the macro."

I've Got Plenty of Nothing

Doug Steele starts by looking at a technique for finding unused fields in tables

Handling Price Ranges in Microsoft Access

Rebecca Riordan provides two methods for handling the common case of multiple prices for a given product, depending on the quantity ordered–the customer pays $1.79 each for buying up to nine items, but only $1.69 each when buying 10-14 items, and so on.

Embedded quotes in SQL statements - DAO Code

Adrian Murphy pointed out to Doug Steele that Parameter queries are another approach to solving the problem of having embedded quotes in the values being used in SQL statements. He sent along the following code sample:

Advanced Data Shaping - Using Hierarchial Recordsets

Mike Gunderloy provides some examples of ADO's SHAPE_APPEND statement in action, including how to synchronize child and parent Recordsets. Mike also introduces the SHAPE_COMPUTE statement.

Doug Steele shows how to simulate Cue Banners in Access

Internet controls such as search boxes have something called "cue prompting" that can help indicate what the user should be entering. Is there some way I can still do something like this?

Creating Paired Listbox Controls, Part 2

Rebecca Riordan continues her examination of paired Listbox controls by adding two additional functions: deferring data updates until users explicitly commit their changes, and restoring the contents of the paired Listbox controls to their initial state.

# Convert Access to SQL Server #

Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query translation and web form conversion. MUST now supports Access 2010.

PivotTables in Access

Russell Sinclair shows how to summarize data using PivotTables

An Access E-mail Application

Adding an e-mail facility to your Access application is a convenient way to communicate critical data to multiple users, while at the same time producing a trail linked to key records in your database. Keith Bombard shows you how.

From Zoom Box to Custom Dialog

For some applications you just can't get enough screen real-estate, especially when you have fields that display a large amount of text. Mike Toole describes an alternative to the Zoom box that not only looks and works better but avoids the Zoom box's spurious updates. His design can be used for creating any sophisticated custom dialog.

Manage your Update Tables with Query Lists

In this article, Keith Bombard shows off a routine that can reduce the time it takes to create a form to manage your update tables. This general-purpose routine can be used with any small table for editing, updating, adding, and deleting records.

Making the grade

Doug Steele looks at a common problem in database design: converting from one data classification schema to another. He then moves on to a related question: ensuring that there are no overlapping records in a list of ranges (for example, a list of scheduled events). This results in some thorny SQL, but the results can be used in a wide variety of circumstances.

Transfer All Tables from One Database To Another

This page is updated to show how I transferred from a database to another database (It was actually a SQL back to an Access ACCDB format) using a third database that was linked to both. There was no relationships in the target database so order of transfer wasn't important.