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.


Friday, September 03, 2010

Global Error Handler v2 now released!

Previously known under the name of "SimplyVBA Global Error Handler", we've just released version 2 of the product, now called vbWatchdog.

Designed specifically for VBA, the product offers a robust mechanism for catching and logging errors that occur in your VBA code on a global scale.

The main features are;

- No DLLs required - vbWatchdog is coded neatly inside your VBA environment
- Identify the source procedure and module name where the error occurred
- List the exact line number to identify the line of code that failed
- Report the full callstack that lead up to the exception
- List values of variables within each procedure on the callstack
- Offers a Try-Catch paradigm for simplifying local error handling
- Prevents the Access Runtime from closing on unhandled errors
- Includes a customizable HTML based error dialog at your fingertips
- Includes a detailed online manual

Why not take a look at the Sample.MDB to see what you're missing out on...

More Information

Wayne Phillips

Friday, August 20, 2010

Outlook Exchange Online

I recently purchased a HTC HD2 (a good) phone running windows mobile 6.5. More importantly I upgraded to Outlook Exchange Online edition. Now my phone syncs thru the web and not thru the PC. This is fantastic. If your pc is on and downloads emails or contacts or tasks, it doesn't disapear off the web. If you change anything on the phone or the pc, it is immediately available on either of the 3 devices. And then there is the online version of Outlook which is truly the most feature rich web program that I have ever used by a long way and it behaves almost the same as Outlook on the desktop.

Monday, August 09, 2010

Google vs Bing Images

Have a look at how google images has changed to look like Bing.  There are now selections down the LHS for country and image size just like Bing had a while back. Now google images are laid out in a really cool way that seemed to copy the Bing image display.  Search for "fox cartoon" and select Image to see what I mean.

Friday, July 30, 2010

Workbench 10 is coming

A new version of the popular workbench for Access 2010, 2007, 2003, 2002 is coming.

Wednesday, July 28, 2010

Rounded Corners

Just picked up a old database and all the buttons were square and looked old (see picture below).  Use the Windows Themed Contols option in Access Options and you will instantly have Rounded buttons. 

Friday, July 16, 2010

Setting Up A Trial Access 2010 Installation

You have an existing version of Microsoft Access and want to try out Access 2010. Well why not download the trial of Office Professional 2010 (600mb) onto one of your least important computers. Once you have done that and made sure you kept your trial registration number from Microsoft, you simply want to make sure that you DON"T REPLACE your older version of Access & Office.  For the file location, select a different folder than the one you currently use for Office.

So follow these pictures and only install Access (not Word or Excel or Notes etc).

I have followed this system for 16 years without problems.  I use the Access Workbench www.vb123.com/workbench to switch versions ( you will find both versions under the Microsoft Office menu in All Programs )

NOTE:  Only edit software and objects in databases that are not needed in earlier versions.


Tuesday, June 29, 2010

Monday, June 28, 2010

Australian Office Developers Conference

Office DevCon is back in November.

Office DevCon is the largest and most popular annual community-driven conference that allows Microsoft Office developers and power users to come together in one location to hear expert speakers present on a wide range of Microsoft Office-related topics.

2010 will be our fourth year, and we are planning to build on the unprecedented success of the first three years.

Come along and discover how to do things you never knew you could do with the Microsoft Office suite of products.

Friday, June 25, 2010

14 Times Faster is that sad old Microsoft

Microsoft Windows 7 is selling at a rate of 7 per second.  Thats fourteen times faster than ipads. I think I would be buying Microsoft shares rather than Apple.  Click to go to article

Thursday, May 27, 2010

Its a sad state of affairs

On this day Apple overtook Microsoft as the most valuable software company by stock market value.  If apple had sold its original mac and earlier products at PC prices all those years ago, we would have all been mac gurus.  Alas whilst the world believes that a music device with a white cord, a pc that doesnt do much for a business and a phone that has 100,000 mostly distracting downloads is important, this state of affairs will remain. Meanwhile China is building a power station a week, a city of a million people people a month, builds railroads at speeds of upto 5 km a week and has factories that make everything, its time for the white cord listeners to get back to work and innovate to make America the great country that it once was.

Friday, May 14, 2010

Office VB7 meets 64 Bit computing

One of the least publicized achievements for the teams at Microsoft is 64 bit computing.  What is that some of you might say, simply 64 bit computing is the only way that windows, office and other programs could actually use more than 3.3 gigs of memory on a Windows PC. Given that computers are now coming with 4 and 8 gigs of memory, this was obviously very important.  Anyway Office programmers,  read what my old pal from Microsoft, Frank Rice, has to say about the next version of VBA and 64 bit computing

Thanks to my other old pal and top Access programmer Rickard Olsson for bringing this to my attention. Its great to have pals, especially smart ones.

Access User Group National Seminar Thursday 27th May 2010

We are pleased to announce the line up for our National Seminar which is being held at Microsoft headquarters in Reading. As usual we will be featuring key speakers from the UK to give you expert and informative advice.

Sessions include: Access 2010, how to use data macros to their best advantage; Access Case Study:- rostering database for management of sales staff at London airports; A demonstration of an Access product which sells into the asbestos consultancy market; Access 2010 productivity in development.

Cost: £100.00 + VAT for members - £135.00 + VAT for non-members

For further information or details on how to book please contact Margaret Chamberlain on at http://www.ukaug.co.uk/

Converting local time to and from GMT

 Code to convert time to GMT here

Wednesday, May 05, 2010

vbMAPI - Outlook Security Evader goes 64 bit!

The first major update to vbMAPI now adds full 64 bit support when running under Office 2010 64 bit edition.

vbMAPI - Version History (scroll down to the bottom)

Whilst this might not sound like a big deal, this means two things:

#1) a 64-bit version of the developer add-in is available (as far as I'm aware, this is the first VBA7 64 bit add-in available). Both versions are installed automatically.

#2) and the native machine code stuff (Virtual-COM) now contains both x86 and x64 binaries and automatically switches between them. This means that you don't have to distribute anything differently (i.e. independence of the bitness of the Office version installed).

A lot of work went into supporting 64 bit, and this now paves the way for updating some of our other products similarly. Keep an eye out for the updates :)

Tuesday, April 27, 2010

More than just pictures

Here is another reason why you need to consider your google habits.  If you have had a google van drive down your street taking photos, they may also have been recording your wireless address as well. http://www.theregister.co.uk/2010/04/22/google_streetview_logs_wlans/

Monday, April 12, 2010

MUST Express Helper

Hi Garry,
Just to let you know of our new product called MUST Express Helper which fills the need to run maintenance plans, backups and other timed activities which full SQL Server would use the SQL Agent to do.
Express Helper is a companion tool for servers running SQL Express and provides a user friendly interface for scheduling Backups, Database Tuning and executing TSQL tasks on a scheduled basis. Just like full SQL Server the backups are executed in cycling sets of files which get over-written as the cycle progresses through the set.

Express Helper comes with a .net windows application interface and is executed as a true windows service on the server, it will generate it’s own SQL Server database called ExpressHelper on the same SQL Server that you are administrating which is where it logs all activity. Any errors and a general monitoring of activity is also supported by the service writing messages into the windows application event log.

Shown below is the simple multi-tabbed .net windows application interface for controlling backups and database tune.

And below we see the windows service which the product installs for you.

MUST Express Helper is licensed on a per-server basis. I use this for my own customers and being able to schedule up sets of backups and running database maintenance plans makes server maintenance a lot simpler.

Andy Couch

Friday, April 09, 2010

vbMAPI - Outlook Security Evader

A new method for sending e-mails programmatically through Outlook without the dreaded security warnings.

Unlike competing solutions (such as Outlook Redemption), this new technique requires no DLLs or any third-party add-ins - it is a pure self-contained VBA solution (also works with VB6).

You can download the trial version here. Getting started manual here

Cost is only £30 (US $50/EUR €40) per developer. Check it out.


Sunday, April 04, 2010

XML Importing Into Access

Albert Kallal has posted sample code for Importing XML into Access (if the Access importing falls short of the mark)

BTW. Access Importing Rules Of Thumb

  • if it imports then Access is a great tool.
  • If it doesn't then watch out as it could be tricky.
  • XML Notepad is a great free editor from Microsoft.
  • When importing, you may need to use temp tables
  • Make sure that your primary keys stop multiple imports from duplicating data

Thursday, March 11, 2010

Access small business database book

A few of the Access MVPs got together to produce a book specifically for small business people Access Small Business Solutions

Coverage includes:

Elements of a Microsoft Access Database | Relational Data Model |  Dealing with Customers and Customer Data | Customer Relationship Management Database Solutions | Marketing Database Solutions | Sales Database Solutions  |  Producing and Tracking the Goods & Services  |  Production and Manufacturing Database Solutions  | Inventory Management Database Solutions  | Services Database Solutions  |  Tracking and Analyzing Financial Data 1 | Accounting Systems: Requirements and Design Database Solutions  | 
Accounting: Budgeting, Analysis, and Reporting Database Solutions  |  Managing Memberships  |
SQL Server and Other External Data Sources

Monday, March 08, 2010

A good writing blog

Writing is an art, I am a programmer so don't think "tink" I can write.  Here is a blog on writing to whet your appetitie http://www.idratherbewriting.com/

Sunday, March 07, 2010

Raining Fish

We have had a lot of rain in Australia lately with some floods reaching record levels but this town in the desert reporting fish falling from the sky.  They are 300 miles from the coast.

Wednesday, March 03, 2010

Google Apps - Not So Easy

This week I had a ambition, I wanted to see if I could respond to my emails online and on a phone and then file them away into a folder so I didnt have to read them again in Outlook.  So I went to Google as the Calendar App mentioned sharing and 2 way sync. It worked pretty well except for the fact that google sync keeps trying to open Outlook even when its not open which is not so hot for backups.  The google calender online works well but looks pretty 90's. 6/10
Then I decided to move onto the Google Apps to do my emails and maybe even tasks.  Little did I know that I was heading into a product that basically would move all of my current email ISP offerings to Google and leave me making the decisions that a Microsoft Exchange manager would make. After a few hours, I gave up.  All I wanted was to sync my emails, not move the whole lot. I dont see Microsoft being too worried about google in that area yet.  2/10 or maybe a 7/10 if you hate MS Exchange and the $$$

Read a different opinion here Google-gains-momentum-with-apps

Tuesday, March 02, 2010

Outlook VBA Programming

Outlook 2010 VBA programming is discussed on this Microsoft Outlook page  Good to see VBA still getting top billing.

Accessing detailed file information provided by the Operating System

Have you ever wanted to find out generic properties about a file, such as the duration length of an MP3 file, or the date a JPEG picture file was taken on a digital camera?

Windows Explorer shows us this sort of information, so surely we can tap into this facility to programmatically access these properties?

Read on to find out how to do this in VBA.

Requires at least Windows Vista or newer.

Tuesday, February 23, 2010

Web Services

A post in the Access blog on the new thing, Web Services.  Click here to link

My notes:  When thinking of moving your Access database to the web, think like this. Porting existing rich Access databases to the web will be tricky. Think about which part of your Access database needs to be on the web. Then consider the Access web services as an easy way to learn web programming compared to learning ASP.net or PHP from scratch. Then write your smaller Access app from first principles using all the available Access templates for examples.

Thursday, February 04, 2010

How do you search an Access Report

I was working on a report that ran to 100 pages and we needed to find a code in that report.  As an Access report is not text based, you either need to save the report to PDF or use the Publish to Word wizard.  Then you can search that document.

Monday, February 01, 2010

Friday, January 15, 2010

2010/2007 Backward compatibilty - it keeps us in business

Every new version introduces new features that render the old version incompatibles.  Here is list of items published by Microsoft that will make your Access 2010 version incompatible with Access 2007.  http://msdn.microsoft.com/en-us/office/cc907897.aspx

Tuesday, January 05, 2010

Get to the desktop

If you want to get to the desktop quickly, either use the Windows and D keys together or Click on the funny little bar at the end of the TaskBar