ACCESS Watch

Creative Certificates with Word our latest ebook. Pre-publication special from $5.95

Helen Feddema discusses updated database backup and totaling calculated fields on reports
19 September 2006 - Vol 8 No. 15
 

5Ads make Office Watch possible, please support our sponsors5How to advertise click here5

Q&A

Summing a calculated field on a report

Q:  Marie H. needs a total in a report group footer, based on a calculated field in the report detail section.  =Sum([txtWeekTotal]) doesn't work – how can this field be totaled?

A:  You can't total a textbox, but you can total the text box's control source, so in this case it would be something like =Sum([MonAmt] + [TuesAmt] + [WedsAmt] + [ThurAmt] + [FriAmt]).  Another possibility is to create the calculated field in the report's record source query or SQL statement, so it is a field in the report, and can be totaled as =Sum([WeekTotal]).

>>>  Get Access / SQL Server Database Recovery Tools at http://ref.OfficeRecovery.com/access/?waf <<<
Professional-grade, easy to use database recovery software for Access, SQL Server, Oracle, Interbase, Paradox and more. 12 different database formats supported. Assess data recoverability with demo versions.
*** Download free demo at http://ref.OfficeRecovery.com/access/?waf ***

Compact, compile, backup and switch Access databases with ease ? Try Garry Robinson's Workbench 

5Ads make Office Watch possible, please support our sponsors5Info on advertising click here5

CREATIVE CERTIFICATES WITH WORD

Peter Deegan and Maryjane Almer are delighted to announce the upcoming release of their latest ebook Creative Certificates with Word with easy step-by-step instructions, examples ready for to use and low price.

You've got Microsoft Word already, we'll show you how to use it to create gorgeous certificates for home or office just using Microsoft Word. http://shop.office-watch.com/cert

• Step-by-step guide to making an effective certificate from blank page to finished product with plenty of color screen shots to guide you.
• You discover some of the tricks that Word experts use to make text and images appear exactly where they should.
• Fonts - a whole chapter on fonts, getting good free fonts to supplement the commonly seen Windows typefaces and suggested combinations of fonts.
• Tips for advanced users. Easy access to common symbols (including many you probably didn't know were there), choosing page orientation, selecting a page border, using WordArt effectively and more.

This isn't just another computer how-to book, the tips on using Word is combined with practical design advice from a professional graphic designer, Maryjane Almer.

Premium Edition

You want more? Our Premium Edition has even more example certificates and advice:

• Step-by-step instructions on printing many certificates from a list of recipients (in Excel, Access or other source). How to convert raw data (like a grade) into something that looks good. printing out only some certificates (top students) and sorting.
• Extra examples -- more than 10 extra professionally designed certificates. Word documents ready for you to use.
• More links - selected examples of other resources available.
• Page borders. A full easy-to-read list of all 164 page borders in Word 2003.

We're putting the finishing touches on Creative Certificates with Word in the meantime you can SAVE with our usual pre-publication special. We've done this for previous ebooks and many of our customers take advantage of this advance offer.

• save $5 by buying NOW before standard retail pricing kicks in.
• download a small preview edition right away and
• get the full ebook before it goes on sale later this month.

Go to http://shop.office-watch.com/cert/FastTrack.asp  

New! If you'd like to try the new Google Checkout service you can do that buying our new ebook http://shop.office-watch.com/cert/FastTrack_Google.asp

'Creative Certificates with Word' works for Word 97, through Word 2000, Word 2002 (XP) and Word 2003. The Certificate Gallery of ready-to-use examples are Word documents compatible with Word 97 through Word 2007. The versions of Word also include Microsoft Works packages that include a full version of Microsoft Word.

Where Is It?

You won't find an exact equivalent to the Startup dialog (Tools|Startup) in Access 2007, but its functionality can be found in the Current Database page of the Access Options dialog.  To get there, first click on the File button at the upper-left corner of the Access window (it is the one with the Office logo) to open the Access 2007 File menu:

Figure B.  The Access 2007 File menu

Click on the Access Options button to open the Access Options dialog, and select the Current Database page.  This large page (and several of its buttons) give you the functionality that used to live on the Startup dialog, and lots more as well.

Figure C.  The Current Database page of the Access Options dialog

Outlook add-ins save time and help you be more productive:
* New! Mobile Email Redirect - get your Outlook email on your mobile
* Vcard Converter - easily transfer contacts to your iPod and back
* Auto Print - automatically print incoming emails and attachments
* Now with 32 Outlook add-ins

5Ads make Office Watch possible, please support our sponsors5>5Info on advertising click here5

Updated Database Backup

Access Archon #151

Introduction

A few years ago, in Access Archon #115, I described a way to create backups of the current database, using several objects that you could import into a database, to make it easy to regularly save backup copies of the database.  Over the years, I streamlined this method, paring it down to just a table, a module, and a set of macros to run the backup functions.  Most recently, I updated the backup procedures for my upcoming book on Office 2007 (for this book, they will be part of an Access add-in that also includes the table and query field listing procedures that were featured in Access Archon #150).

Modifications to Backup Procedures

That book won't be out for a while – it should be published around the time of the release of Office 2007 – but in the meanwhile I have prepared an Updated Backup database, with code for backing up both front-end (or standalone) databases and back-end databases.  Unlike the older version of the backup code, there is no requirement for any specific syntax or location for the back end database; the path to the back end is picked up from the Current property of a linked table.

Another change:  Since Access 2007 has a new database format, and a new extension (.accdb instead of .mdb), I had to modify the code to deal with either type of extension.  The updated backup code will work with databases in formats from Access 2000 to Access 2007 (beta).

As with the earlier versions of the Backup technique, the dates and incrementing numbers for the backup databases are stored in a table, now called zstblBackupInfo (the zs prefix indicates that this is a system table).  I added two extra fields, so that backup numbers could be incremented separately for the front-end and back-end databases.  All the code is in the basBackup module; the two procedures that do the backups are called from the macros mcrBackupFrontEnd and mcrBackupBackEnd.

Using the Backup Procedures

If you want to add backup capability to an Access database, all you have to do is import zstblBackupInfo, basBackup, mcrBackupFrontEnd and (for databases with linked back ends), mcrBackupBackEnd into the database.  Check that you have references to the DAO and Scripting Runtime libraries, compile the code, and you are ready to go.  To back up the current database, run mcrBackupFrontEnd; to back up the back end, run mcrBackupBackEnd.  The proposed save name for the database backup copy is presented in an InputBox, as shown in Figure A; you can edit the name as desired, say to indicate a milestone achieved.

Figure A.  An InputBox that lets you modify the database copy name

VBA Code

The two procedures that back up a front-end (or standalone) database, or a back-end database, and the procedures that create incrementing numbers for them, are listed below:

Public Function BackupFrontEnd()

On Error GoTo ErrorHandler

   Set dbs = CurrentDb

   Set tdfs = dbs.TableDefs

   'Components of the FileSystemObject object library are used
   'to work with files

   Set fso = CreateObject("Scripting.FileSystemObject")

   strCurrentDB = Application.CurrentProject.Name

   Debug.Print "Current db: " & strCurrentDB

   intExtPosition = InStr(strCurrentDB, ".")

   strExtension = Mid(strCurrentDB, intExtPosition)

   intExtLength = Len(strExtension)

   'Create backup path string (Backups folder under database folder)

   strBackupPath = Application.CurrentProject.Path & "\Backups\"

   Debug.Print "Backup path: " & strBackupPath

   'Check whether path is valid

On Error Resume Next

   Set sfld = fso.GetFolder(strBackupPath)

   If sfld Is Nothing Then

      'Create folder

      Set sfld = fso.CreateFolder(strBackupPath)

   End If

On Error GoTo ErrorHandler

   'Create proposed save name for backup

   strDayPrefix = Format(Date, "d-mmm-yyyy")

   strSaveName = Left(strCurrentDB, _

      Len(strCurrentDB) - intExtLength) & " Copy " & SaveNo _

      & ", " & strDayPrefix & strExtension

   strProposedSaveName = strBackupPath & strSaveName

   Debug.Print "Backup save name: " & strProposedSaveName

   strTitle = "Database backup"

   strPrompt = "Save database to " & strProposedSaveName & "?"

   strSaveName = Nz(InputBox(prompt:=strPrompt, _

      title:=strTitle, Default:=strProposedSaveName))

   'Deal with user canceling out of the InputBox

   If strSaveName = "" Then

      GoTo ErrorHandlerExit

   End If

   Set rst = dbs.OpenRecordset("zstblBackupInfo")

   With rst

      .AddNew

      ![SaveDate] = Format(Date, "d-mmm-yyyy")

      ![SaveNumber] = SaveNo

      .Update

      .Close

   End With

   fso.CopyFile Source:=CurrentDb.Name, _

      destination:=strSaveName

ErrorHandlerExit:

   Exit Function

ErrorHandler:

   MsgBox "Error No: " & err.Number & "; Description: " & _

      err.Description

   Resume ErrorHandlerExit

End Function

Public Function BackupBackEnd()

On Error GoTo ErrorHandler

   Dim strBackEndDBNameAndPath As String

   Dim strBackEndDBName As String

   Dim strBackEndDBPath As String

   Dim strFilePath As String

   Dim strFullDBName As String

   Dim strFileName As String

   Dim strFullPath() As String

   Dim strDBName As String

   Dim intUBound As Integer

   Set dbs = CurrentDb

   Set tdfs = dbs.TableDefs

   'Components of the FileSystemObject object library are used

   'to work with files

   Set fso = CreateObject("Scripting.FileSystemObject")

   strCurrentDB = Application.CurrentProject.Name

   'Debug.Print "Current db: " & strCurrentDB

   strDayPrefix = Format(Date, "d-mmm-yyyy")

   intExtPosition = InStr(strCurrentDB, ".")

   strExtension = Mid(strCurrentDB, intExtPosition)

   intExtLength = Len(strExtension)

   'Check whether there are any linked tables, and exit if not

   strBackEndDBNameAndPath = ""

On Error Resume Next

   For Each tdf In tdfs

      'Debug.Print "Table name: " & tdf.Name

      'List table properties, and get back end database

      'path from Connect property of a linked table

      If Left(tdf.Name, 4) <> "MSys" Then

         For Each prp In tdf.Properties

            Debug.Print vbTab & "Property name: " & vbTab & prp.Name _

               & vbCrLf & vbTab & "Property value:" & vbTab & prp.Value

            If prp.Name = "Connect" Then

               If Nz(prp.Value) <> "" And _

                  Left(prp.Value, 1) <> "?" Then

                  'Debug.Print "Connect string: " & prp.Value

                  strBackEndDBNameAndPath = Mid(prp.Value, _

                     InStr(prp.Value, "=") + 1)

                  'Debug.Print "Back end db name and path: " _

                     & strBackEndDBNameAndPath

                  GoTo ContinueBackup

               Else

                  'Skip other properties

                  GoTo NextTable

               End If

            End If

         Next prp

      End If

NextTable:

   Next tdf

On Error GoTo ErrorHandler

   'No linked tables found

   strTitle = "No back end"

   strPrompt = "There are no linked tables in this database"

   MsgBox strPrompt, vbExclamation + vbOKOnly, strTitle

   GoTo ErrorHandlerExit

ContinueBackup:

   'Extract back end name and path from Connect property string

   strFullPath = Split(strBackEndDBNameAndPath, "\", -1, _

      vbTextCompare)

   intUBound = UBound(strFullPath)

   strBackEndDBName = strFullPath(intUBound)

   strBackEndDBPath = Mid(strBackEndDBNameAndPath, 1, _

      Len(strBackEndDBNameAndPath) - Len(strBackEndDBName))

   Debug.Print "Back end Database name: " & strBackEndDBName

   Debug.Print "Back end Database path: " & strBackEndDBPath

On Error Resume Next

   'Check whether back end path is valid

   Set sfld = fso.GetFolder(strBackEndDBPath)

   If sfld Is Nothing Then

      strTitle = "Invalid path"

      strPrompt = strBackEndDBPath _

         & " is an invalid path; please re-link tables and try again"

      MsgBox strPrompt, vbOKOnly + vbExclamation, strTitle

      GoTo ErrorHandlerExit

   End If

   'Create backup path string (Backups folder under back end

   'database folder)

   strBackupPath = strBackEndDBPath & "Backups\"

   'Debug.Print "Backup path: " & strBackupPath

   'Check whether path is valid

   Set sfld = fso.GetFolder(strBackupPath)

   If sfld Is Nothing Then

      'Create folder

      Set sfld = fso.CreateFolder(strBackupPath)

   End If

On Error GoTo ErrorHandler

   'Create proposed save name for backup

   strDayPrefix = Format(Date, "d-mmm-yyyy")

   strSaveName = Left(strBackEndDBName, _

      Len(strBackEndDBName) - intExtLength) _

      & " Copy " & BackEndSaveNo _

      & ", " & strDayPrefix & strExtension

   strProposedSaveName = strBackupPath & strSaveName

   Debug.Print "Backup save name: " & strProposedSaveName

   strTitle = "Database backup"

   strPrompt = "Save database to " & strProposedSaveName & "?"

   strSaveName = Nz(InputBox(prompt:=strPrompt, _

      Title:=strTitle, Default:=strProposedSaveName))

   'Deal with user canceling out of the InputBox

   If strSaveName = "" Then

      GoTo ErrorHandlerExit

   End If

   Set rst = dbs.OpenRecordset("zstblBackupInfo")

   With rst

      .AddNew

      ![BackEndSaveDate] = Format(Date, "d-mmm-yyyy")

      ![BackEndSaveNumber] = BackEndSaveNo

      .Update

      .Close

   End With

   fso.CopyFile Source:=strBackEndDBNameAndPath, _

      destination:=strSaveName  

ErrorHandlerExit:

   Exit Function

ErrorHandler:

   MsgBox "Error No: " & err.Number & "; Description: " & _

      err.Description

   Resume ErrorHandlerExit

End Function

References

The code in the sample database needs the following references (in addition to the default references):

Microsoft DAO 3.6 Object Library

Microsoft Scripting Runtime

If you import code or objects into a database of your own, you may need to set one or more of these references.  The version number may differ, depending on your Office version; check the version you have.  References are set in the References dialog, opened from the VBA window.  For more information on working with references, see Access Archon #107, Working with References.

Supporting Files

The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site.  It is accarch151.zip, which is the last entry in the table of Access Archon columns for Access Watch.

Entertainment Niche

Michael Gruber – Night of the Jaguar

Expert One-on-One Microsoft Access Application Development, Award-winning (#1 in the Desktop and Office Applications books category at the Waterside 2004 Conference) Access 2002 Inside Out and DAO Object Model: The Definitive Reference are written by AW editor, Helen Feddema