About sydneygeek

I was a consultant in Excel and Access for about 14 years before taking a full time role as an analyst with an energy company. Tech interests include Excel, Access, VBA, HTML / CSS and Oracle. I am based in Sydney, Australia and am a moderator on MrExcel, where I go by the name SydneyGeek.

Renaming ranges in VBA

Recently a question came up on an Excel forum. The request was to rename hundreds of named ranges in VBA. The simplest option is to create an array from a 2-column table and loop through the array, making changes to each name in the first column. The table is called NameList. Populate and name the table (old names in the first column), paste this code into a new module, and run it.

Note: If you haven’t created a named range before,  follow the steps below.

1. Select the data (not the headings), highlighted here in blue


2. Click the Name Box (just to the left of the formula bar), type the name (no gaps) and press Enter.

Sub NameChanger()
    Dim arNames()
    Dim nm As Name
    Dim i As Integer

    arNames = Range("NameList").Value
    For i = LBound(arNames) To UBound(arNames)
        For Each nm In ActiveWorkbook.Names
            If nm.Name = arNames(i, 1) Then
                nm.Name = arNames(i, 2)
            End If
        Next nm
    Next i
End Sub

You can confirm the names changes by pressing F3 and then Alt+L (on a blank sheet).
Optional extra: If you want to check the range references (and list them next to the NameList table) paste this to your module and run it:

Sub NameRefs()
    Dim c As Range

    For Each c In WorksheetFunction.Index(Range("NameList"), 0, 2)
        c.Offset(0, 1) = "'" & Names(c.Value).RefersTo
    Next c
End Sub

A simple method for linking and viewing images in Access

Access has traditionally not been a good environment for storing media, images and files due to database bloating. Because of that many people prefer to store images in the file system and link to those files from Access. The approach shown below is just one of many methods, and has these features:

  • Files are stored in a predefined directory
  • The images are located / stored using a button on a subform. They are displayed in an unbound image frame on the main form.
  • The same button is used to browse for and display a new image, and to display an existing image.
  • Images are located on disk using the FileDialog object. Once selected you can open, save, rename or otherwise modify the file.

You will need to set up the following to use this code:

  • A command button on a subform, with the code shown in Listing 1.
  • A text field in the subform, named Photo
  • An unbound image frame in the main form, called imgDisplay. When you set up this control you will be prompted to browse for an image. Whatever you select will become the default image.
  • You will also need to save Listing 2 in the code module of the subform.

Listing 1

    If IsNull(Me.Photo) Or Len(Me.Photo) = 0 Then
        'no image; load an image before showing
        GoTo TryAgain
        Me.Parent!imgDisplay.Picture = Me.Photo
    End If

The If statement checks first to see whether the Photo field is empty. If it is the CopyImage function launches, prompting the user to locate an image for the record. Once the image is copied and the link written, the code returns to the top of the routine and loads the link into the image container.

Listing 2

Private Function CopyImage()
    ''You will need a reference to the Microsoft Office x.0 Object Library
    '' where x = 10,11,12 or 13

    Dim fd As Office.FileDialog
    Const SAVE_PATH = "H:\Documents\Images\" 'default folder for saving the images
    Dim strFname As String
    Dim i As Integer
    Dim vrtSelectedItem As Variant
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Add "All files", "*.*"
        .Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 1
        .AllowMultiSelect = False
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                strFname = ""
                For i = Len(vrtSelectedItem) To 1 Step -1
                    If Mid(vrtSelectedItem, i, 1) = "\" Then
                        strFname = Mid(vrtSelectedItem, i + 1)
                        Exit For
                    End If
                Next i
                'copy to the destination directory
                FileCopy vrtSelectedItem, SAVE_PATH & strFname
                ‘optional: delete the original, keeping only the copy.
                Kill vrtselecteditem
                ‘write the full path to the Photo field
                Me.Photo = SAVE_PATH & strFname
            Next vrtSelectedItem
            'The user pressed Cancel.
        End If
    End With
    'Set the object variable to Nothing.
    Set fd = Nothing
End Function

Regional settings cause report to fail in Access

Here’s a weird problem that I encountered recently. In a database that I had developed for a client, one of the reports refused to run when they switched their regional settings to another language — a problem, since some of the users were German and hence would be unable to use the report without changing back to English settings first.

The problem came down to the headers that I had used in the crosstab query that fed the report. It had text strings like “01_Mon”, “02_Tue”, etc for a total of 10 headers. And of course, these became fields for the report.

What I hadn’t realised what that Access interpreted these as dates, so when the regional settings changed, they became “01_Mo”, 02_Di”, etc … and the report broke. The fix was simple: use generic “Day_01”, “Day_02”, etc as the day headers, and update the report controls to read from these generic fields.

I guess it’s another case of crosstab queries and reports having unintended consequences. They are powerful, but they can bite. Here’s hoping they don’t bite you the same way.

Improving query performance

Sometimes a database will start to run slowly, and you will need to trace the problem. There are many reasons for poor database performance, particularly over a network. They include:

  • Opening a form based on a large table or query, without filtering it first
  • Linking to multiple images in a network directory
  • Using a single copy of a database front end on a network drive, and opening your database via that networked front end
  • In short, anything that causes you to pull large amounts of data to your desktop.

If you haven’t made these design errors the time has come to look at your queries; you may have one or more queries that form the basis for a group of forms and reports, and if they are running slowly you can make a big difference to the database’s responsiveness by redesigning the queries.

Checking your queries

When this happened to me recently I built a function that opened all of the queries in the database, timing how long they took to run, and writing the results to the Immediate code window. I ran the function 4 times, copied the output to Excel, built a pivot table to average the results by query, and sorted the times in descending order. It quickly became apparent that one group of queries was slower than the rest, which gave me a couple of targets to concentrate on. This code requires a reference to the Microsoft DAO 3.6 Object Library (2003 or earlier) or the Microsoft Office x.0 Database Access Engine Object Library (x is 12 for office 2007, 14 for Office 2010).

Function TimeQueries()
    Dim qdf As DAO.QueryDef
    Dim StartTime As Single, _
        EndTime As Single

    For Each qdf In CurrentDb.QueryDefs
        DoCmd.SetWarnings False
        If InStr(1, qdf.Name, "~") = 0 Then
            StartTime = Timer
            DoCmd.OpenQuery qdf.Name
            EndTime = Timer
        End If
        Debug.Print qdf.Name & "; " & (EndTime - StartTime)
        DoCmd.SetWarnings True
    Next qdf
End Function


I use subqueries fairly extensively and discovered that two queries in particular had subqueries in the criteria. In both cases those subqueries contained groups and / or domain functions like Max and First.

First fix: write the subquery values out to a table

The first query returned all records from an import table that matched specific criteria, and took around 3 seconds to open. The import is a monthly process and takes a couple of minutes to complete, so I decided to make a table using the query data, as part of the process. When the ‘slow’ queries were based on this table instead of the base query, execution time dropped to 0.15 seconds: a 95% saving. This particular query was the main culprit; after the redesign, the database performance returned to its original speed before the slowdown.

Second fix: join to a second query

The slowest query fortunately doesn’t get used very often; it took around 150 seconds to run. Removing the criteria caused the query to open almost immediately so I turned the criteria into a second query, and joined it to the first on the primary key. Re-timing the query showed that it now opened in 0.15 seconds. This was a thousand-fold improvement over the starting query.

The bottom line

There are many ways to build queries but you may find yourself using one or two favourite techniques most of the time. If things slow down, you can get a significant performance boost by redesigning a couple of key queries.

Exporting Oracle SQL to a text file

I’ve been working with Oracle for the past 12 months or so and needed to export the code and SQL for my project, as a documentation tool (and a safeguard against a system crash). If you’re a DBA there are a number of tools at your disposal; I’m not, and I needed something that I could run from Excel or Access any time I needed to.

You will need to build yourself a couple of views based on the user tables, and then use VBA to loop through those views to build the text files. Although the concept is similar for both SQL and code, there are important differences in how the data is parsed so they will be treated separately.

SQL extraction

Oracle SQL is stored in the user_views table. I always include an identifier in views for a particular project, and you can use that identifier (shown hereafter as XX) to pull out all of your SQL for that project.

Create a view in Oracle

In your favourite editor (I use TOAD) create and commit the view.

   SELECT view_name,
          text view_sql
     FROM user_views
    WHERE view_name LIKE '%XX%';


Where I work we assign permissions on a group basis. Make sure that you assign SELECT permission on the view to a role that (1) includes your user name and (2) can at least select data. You could also just grant the SELECT permission to your user name. You will also need to connect with the credentials of the user who created the views.

If you look at the view you will see that VIEW_NAME is the name of the view, and VIEW_SQL contains the formatted SQL. The multiple lines are separated by line feeds Chr(10), and we will use this to write the SQL into the text file.

Create the VBA

The VBA code is shown below. Copy and paste into the IDE in Excel or Access. Change paths, connection strings and suffixes as required.

'You will need to update this code to suit your setup,
'in the following places:
'sPATH, sFILE, database, user id, password, strSQL
'You will also need to set a reference to the
'Microsoft ActiveX Data Objects 2.x Library
Sub Harvest_SQL_from_view()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strProc As String
    Dim sFILE As String
    'include the trailing backslash
    Const sPATH = "G:\some path\" 
    Dim strOpenFile As String
    Dim strOutFile As String
    Dim x
    Dim FF As Integer
    Dim i As Integer
    'open the text file. 
    'If it doesn’t exist it will be created.
    sFILE = "xx_sql"
    strOpenFile = sPATH & sFILE & ".txt"
    strOutFile = sPATH & sFILE & "_" 
        & Format(Now(), "yyyymmdd") & ".txt"
    FF = FreeFile
    Open strOpenFile For Output As #FF
    'Create the connection
    Set cnn = New ADODB.Connection
    With cnn
       .ConnectionString = "Provider=MSDAORA;" _
            & "Data Source=YOURDATABASE.WORLD;" _
            & "User Id=your_user_name;" _
            & "password=your_password
    End With
    'SQL for the recordset
    strSQL = "SELECT * FROM zz_v_xx_views"
    'pull the data and write out to a text file
    'first, create the recordset, then loop through it
    'NOTE: For Oracle you MUST use the 
    'Execute method to set a recordset.
    Set rst = cnn.Execute(strSQL)
    With rst
            Print #FF, " "
            Print #FF, "++++++++++++++++++++++"
            Print #FF, rst!view_name
            Print #FF, " "
            'the SQL is split using Chr(10) 
            'as the delimiter
            x = Split(rst!view_sql, Chr(10))
            For i = LBound(x) To UBound(x)
                Print #FF, x(i)
            Next i
        Loop Until .EOF
    End With
    'close and save the file
    Close #FF
    'copy the generic file to one with a datestamped name
    FileCopy strOpenFile, strOutFile
    'clean up object references
    Set rst = Nothing
    Set cnn = Nothing
    'open the output file
    Call Shell("Notepad.exe" & " " & strOutFile, vbNormalFocus)
End Sub

The Split function does most of the work in extracting the SQL. If you haven’t encountered it before, Split creates an array by splitting a text string on a common delimiter (here, we used Chr(10)). Looping through the array and using the Print command puts each element onto a new line, thus rebuilding the query SQL with its original formatting.

Once the file is written, the Shell command displays the output. Each time you run the routine, a datestamped file is created so you can keep dated versions of your SQL.