Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

Access Object Names With Spaces and NiceDLookup

by | Sep 5, 2024 | Blogging, MS Access, MS Access Features, MS Access Queries, MS Access Tables, MS Access VBA Coding | 0 comments

So first things first.  It is not recommended that you use spaces or special characters in your table names as it can cause problems referencing them or cases that are ambiguous and may cause unintended results in the code.

But maybe you have used them in the past, or have inherited databases with tables with commas, spaces, ampersands, and other weird special characters in the table name, how do you deal with them?

In Access, there is an optional ability to put brackets around a string to have it interpret the string as the name of a table, query, module, or any other Access object.  So let’s say you’ve named your table:

Lions, Tigers, & Bears = ‘Oh My’

Yes, that is a valid table name as far as Access is concerned. You could represent that in a SQL statement as:
SELECT * FROM [Lions, Tigers, & Bears = ‘Oh My’]

There are a few characters that are actually illegal.  Check out this Microsoft article for more info:
Guidelines for naming fields, controls, and objects – Microsoft Support

The reason I brought up this topic is because Mar10 brought to my attention that the NiceDLookup function does not allow spaces in table or query names like DLookup does. In DLookup, I can use a string of the table or query name with the spaces and special characters as the domain argument and it will correctly use that table or query. In NiceDLookup, I could work around this by using brackets around a domain argument I was passing, but this does not mimic the behavior of the DLookup function. Here is the original source of the NiceDLookup function:

DLookup and “NiceDLookup” | Access JumpStart

In order to remedy this, I am using two more functions which check for queries and tables in the current database. These were already in my library and can be added as a check to the NiceDLookup function so we can add brackets around it if it’s an actual object. Here are the QueryExists and TableExists functions from my library:

' ----------------------------------------------------------------
' Procedure Name: QueryExists (for DAO Db connection)
' Purpose:  Does the Query exist in the specified Database
' Procedure Kind: Function
' Procedure Access: Public
' Parameter QueryName (String):
' Parameter dbCurrent (Optional Database): default = CurrentDb
' Return Type: Boolean
' Author: steve
' Date: 10/7/2021
' ----------------------------------------------------------------
Public Function QueryExists(QueryName As String, Optional DbCurrent As DAO.Database) As Boolean
   Dim intFields As Integer, ReleaseDB As Boolean
   On Error GoTo ProcError
   If IsEmpty(DbCurrent) Then
      Set DbCurrent = CurrentDB
      ReleaseDB = True
   End If
   intFields = DbCurrent.QueryDefs(QueryName).Fields.Count
   QueryExists = True
ProcExit:
   If ReleaseDB Then
      DbCurrent.Close
      Set DbCurrent = Nothing
   End If
   Exit Function
ProcError:
   QueryExists = False
   Resume ProcExit
End Function

' ----------------------------------------------------------------
' Procedure Name: TableExists (for DAO Db connection)
' Purpose: Does the table exist in the specified database?
' Procedure Kind: Function
' Procedure Access: Public
' Parameter TableName (String):
' Parameter DbCurrent (Optional, Database): Default = CurrentDb
' Return Type: Boolean
' Author: steve
' Date: 9/20/2017, 10/7/2021, 12/21/21 fix bug when dbCurrent is not set
' ----------------------------------------------------------------
Public Function TableExists(TableName As String, Optional DbCurrent As DAO.Database) As Boolean
   Dim intFields As Integer, ReleaseDB As Boolean
   On Error GoTo ProcError
   If DbCurrent Is Nothing Then
      Set DbCurrent = CurrentDB
      ReleaseDB = True
   End If
   intFields = DbCurrent.TableDefs(TableName).Fields.Count
   TableExists = True
ProcExit:
   If ReleaseDB Then
      DbCurrent.Close
      Set DbCurrent = Nothing
   End If
   Exit Function
ProcError:
   TableExists = False
   Resume ProcExit
End Function
Visual Basic

Now we can write some code to update our function to check for the “domain” parameter full string as a table or query in the current database:

   If Len(Domain) < 65 And InStr(Domain, "[") = 0 Then
      ' Don't bother checking if the string is longer
      ' than the allowed Access Object name length
      ' or if it's already got an opening bracket in it
      If QueryExists(Domain) Or TableExists(Domain) Then Domain = "[" & Domain & "]"
   End If
Visual Basic

And here is the new function implementing the new code:

' ----------------------------------------------------------------
' Procedure Name: NiceDLookup
' Purpose: Replace the DLookup function so that the new CurrentDB
'          instance will be explicitly closed and released at termination
' Procedure Kind: Function
' Procedure Access: Public
' Parameter Expr (String): {same as DLookup}
' Parameter Domain (String): {same as DLookup}
' Parameter Criteria (Variant): {same as DLookup}
' Return Type: Variant {same as DLookup} Null if no record
'              satisfies criteria or if domain contains no records
' Author: jon
' Date: 2/3/2022
' Modified: 2/9/2022; Throw same error codes as DLookup
' Modified: 9/5/2024; Add checks for full Domain string as table or query in database
' ----------------------------------------------------------------
Public Function NiceDLookup(Expr As String, Domain As String, Optional Criteria As Variant) As Variant
   Dim DbCurrent As DAO.Database, rsTemp As DAO.Recordset
   Dim sCriteria As String, strSQL As String
   Dim newErrNum As Long
   Dim newErrDesc As String

   newErrNum = 0
   NiceDLookup = Null
   On Error GoTo ProcError
   If IsMissing(Criteria) Then
      sCriteria = ""
   ElseIf Criteria = Null Then
      sCriteria = ""
   Else
      sCriteria = Criteria
   End If
   Set DbCurrent = CurrentDb
   If Len(Domain) < 65 And InStr(Domain, "[") = 0 Then
      ' Don't bother checking if the string is longer
      ' than the allowed Access Object name length
      ' or if it's already got an opening bracket in it
      If QueryExists(Domain) Or TableExists(Domain) Then Domain = "[" & Domain & "]"
   End If
   strSQL = "SELECT " & Expr & " FROM " & Domain & _
      IIf(sCriteria <> "", " WHERE " & sCriteria, "")
   Set rsTemp = DbCurrent.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
   If Not (rsTemp.EOF) Then
      NiceDLookup = rsTemp(0)
   End If
ProcExit:
   On Error Resume Next
   rsTemp.Close
   Set rsTemp = Nothing
   DbCurrent.Close
   Set DbCurrent = Nothing
   On Error GoTo 0  ' we will propagate errors as would DLookup
   If newErrNum <> 0 Then
      Err.Raise newErrNum, "NiceDLookup", newErrDesc
   End If
   Exit Function
ProcError:
   ' Process error numbers so that they will be the same as DLookup
   newErrDesc = Err.Description
   Select Case Err.Number
      Case 3061 ' "To few parameters" when Expr or Criteria field name is not found
         newErrNum = 2471  ' what DLookup throws instead
         'newErrDesc = "The expression you entered as a query parameter produced this error: '|'@@@2@1@11805@1"
         If sCriteria = "" Then
            newErrDesc = "The expression you entered as a query parameter produced this error: '" & Expr & "'"
         Else
            newErrDesc = "The expression you entered as a query parameter produced this error: '" & Expr & "' OR '" & Criteria & "'"
         End If
      Case 3078 ' when Domain not found
         newErrNum = 3078  ' same for DLookup
      Case Else ' hopefully everything else will be the same
         newErrNum = Err.Number
   End Select
   Resume ProcExit
End Function
Visual Basic

Sign up For a Daily Email Adventure in Microsoft Access

Every business day (typically M-F), I'll send you an email with information about my ongoing journey as an advanced Access application developer. It will be loaded with my tips and musings.

    We won't send you spam. Unsubscribe at any time.
    Built with Kit

    Trackbacks/Pingbacks

    1. Error Handling in Access JumpStart | Access JumpStart - […] https://www.accessjumpstart.com/access-object-names-with-spaces-and-nicedlookup/ […]

    Submit a Comment