Dealing with Access NavPane visibility

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.
2. You can also set this option with the following line of code:

db.Properties("StartUpShowDBWindow").Value = False

Select all 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

Select all 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

Select all 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

Select all 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.

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

Select all 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

Select all 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