<

Dealing with Access NavPane visibility

Published on
15,872 Points
5,572 Views
13 Endorsements
Last Modified:
Awarded
Professional Access developers know that applications should never expose database objects directly to users. Unfortunately, the tendency of the NavPain [sic] to become visible when merely importing or linking to external files, even after having been hidden, is infuriating.
Background:
In Access 2007, Microsoft did away with the database window and replaced it with the Navigation Pane, frequently referred to as the NavPane, or more appropriately, the NavPain [sic]. I really like the search feature of the NavPane, but I keep seeing forum posts asking how to resolve the problem that no matter how you hide it, it will not remain hidden when you import or link to external database, spreadsheet, or text files using the Docmd.TransferXXXXXx methods. This is a serious problem as experienced developers know that Access applications should never expose database objects directly to users, which is what happens when the NavPane is exposed. Fortunately, there are ways to hide the NavPane at run-time and to overcome the deficiencies of the TransferXXXXX methods.

There are several ways to hide the NavPain:
1. There is a checkbox in the Access Options dialog box (image below is for A2007); clearing it will hide the Navigation Pane the next time the database is opened.
NavPaneOptionSetting.jpg2. You can also set this option with the following line of code:
db.Properties("StartUpShowDBWindow").Value = False

Open in new window

However, this will also only hide the NavPane the next time you open the current database. Additionally, if that database property has not already been created, then this may raise an error.

3. You can simply hide the NavPane at runtime using code similar to the following. This is the code I've been using for years, but I recently posted this in response to a question here on Experts Exchange and the OP indicated it did not work for him.
Public Sub DisplayNavPane(Optional IsVisible As Boolean = True)

    DoCmd.SelectObject acTable, , True
    If IsVisible = False Then DoCmd.RunCommand acCmdWindowHide

End Sub

Open in new window

The solution that did work used one of the following code segments, both of which I have seen in recent question solutions.

DoCmd.NavigateTo "acNavigationCategoryObjectType", "acNavigationGroupTables"
DoCmd.RunCommand acCmdWindowHide


DoCmd.NavigateTo "acNavigationCategoryObjectType", "acNavigationGroupTables"
DoCmd.Minimize

Open in new window


Frustrating NavePain [sic] behavior:
The simple act of linking or importing a table using one of the TransferXXX (database, spreadsheet, text) methods will override any of these settings and display the NavPane immediately following completion of the import/link operation. When I initially discovered this behavior, I scoured the Internet looking for a solution, but the only solution I found was to run the code shown in option #3 (above) immediately after using one of those methods. This had the unfortunate effect of a screen flicker when the NavPane was briefly displayed and then immediately hidden (not very professional).

Eventually, I discovered that you can avoid this behavior when linking (I cannot recall having ever imported a table from Access, Excel, or a text file into an application developed for a client) by creating a new database tabledef object and assigning properties to that object. The code below will link to an Access database table to your current database without causing the NavPane to become visible (more extensive error handling is left up to the user).
 
Public Function LinkTable(Filename As String, SourceTableName As String, _
                          Optional DestinationTableName As Variant = Null) As Boolean

    Dim tdfDest As DAO.TableDef
    Dim strDestName As String
    
    On Error GoTo ProcError
    
    strDestName = Nz(DestinationTableName, SourceTableName)
    Set tdfDest = CurrentDb.CreateTableDef(strDestName)
    tdfDest.Connect = ";DATABASE=" & Filename
    tdfDest.SourceTableName = SourceTableName
    CurrentDb.TableDefs.Append tdfDest
    LinkTable = True
    
ProcExit:
    On Error Resume Next
    Set tdfDest = Nothing
    Exit Function
    
ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, , "LinkTable error!"
    Debug.Print "LinkTable error", Err.Number, Err.Description
    LinkTable = False
    Resume ProcExit
    Resume

End Function

Open in new window

The syntax changes a bit when linking to Excel worksheets because the connection string and source table names are referenced slightly differently. Additionally, the reference to the particular version of Excel may vary so you may need some error handling to loop through the possible Excel connection strings, depending on the version of Office you are using.

image.gifimage.gif
Public Function LinkExcel(Filename As String, SheetName As String, _
                          Optional DestinationTableName As Variant = Null) As Boolean

    Dim tdfDest As DAO.TableDef
    Dim strDestName As String
    
    On Error GoTo ProcError
    
    strDestName = Nz(DestinationTableName, SheetName)
    Set tdfDest = CurrentDb.CreateTableDef(strDestName)
    tdfDest.Connect = "Excel 12.0;HDR=YES;IMEX=2;ACCDB=YES;" _
                & "DATABASE=" & Filename
    tdfDest.SourceTableName = SheetName & "$"
    CurrentDb.TableDefs.Append tdfDest
    LinkExcel = True
    
ProcExit:
    On Error Resume Next
    Set tdfDest = Nothing
    Exit Function
    
ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, , "LinkExcel error!"
    Debug.Print "LinkExcel error", Err.Number, Err.Description
    LinkExcel = False
    Resume ProcExit
    Resume

End Function

Open in new window

I've had limited success with linking delimited text files because I have not figured out how to identify whether there is a delimiter other than the default (comma). Additionally, if the text fields in the file are not wrapped in quotes, then long numeric strings will be interpreted as numeric and will obviously cause errors if the value of the string exceeds the limits of the long number data type. However, for well-formatted CSV files with text delimiters, the following code has worked well for me.

Public Function LinkCSV(Filename As String, DestinationTableName As String) As Boolean

    Dim tdfDest As DAO.TableDef
    Dim strDestName As String
    Dim strPath As String
    Dim strFileFull As String, strFileShort
    
    On Error GoTo ProcError
    
    strPath = Left(Filename, InStrRev(Filename, "\") - 1)
    strFileFull = Mid(Filename, InStrRev(Filename, "\") + 1)
    'Drop the file extension
    strFileShort = Left(strFileFull, InStrRev(strFileFull, ".txt") - 1)
    strDestName = DestinationTableName
    Set tdfDest = CurrentDb.CreateTableDef(strDestName)
    tdfDest.Connect = "Text;DSN=" & strFileShort & " Link Specification;FMT=Delimted;" _
                    & "HDR=NO;IMEX=2;CharacterSet=437;" _
                    & "ACCDB=YES;DATABASE=" & strPath
    tdfDest.SourceTableName = strFileFull
    CurrentDb.TableDefs.Append tdfDest
    LinkCSV = True
    
ProcExit:
    On Error Resume Next
    Set tdfDest = Nothing
    Exit Function
    
ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, , "LinkCSV error!"
    Debug.Print "LinkCSV error", Err.Number, Err.Description
    LinkCSV = False
    Resume ProcExit
    Resume

End Function

Open in new window

I have not had the need to link to fixed field width text files in any of my production applications. To do so, you can simply change the "FMT=Delimted" (no, that is not a misspelling, that is the way it appears in the connection string) with "FMT=Fixed".  However, I cannot see how this would be helpful without knowing or being able to set the width of the columns at run-time.

Conclusion:
I hope you find these techniques useful.

Dale Fye
Microsoft Access MVP since 2013
13
Author:Dale Fye
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free