<

Go Premium for a chance to win a PS4. Enter to Win

x

Dealing with Access NavPane visibility

Published on
13,932 Points
3,632 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
Comment
Author:Dale Fye
  • 2
3 Comments
 
LVL 26

Expert Comment

by:Nick67
As of 15-Apr-15, Google shows 121,00 results for 'MS Access NavPain' and only 132,000 results for 'MS Access NavPane'  We'll get that name formally changed one of these days so the [sic] can go away.
:)

I have also seen cases where theses two code blocks are NOT equivalent
Dim db As Database
Dim TD As TableDef
Set db = CurrentDb
For Each TD In db.TableDefs
  ...whatever
Next

Open in new window

And

Dim TD As TableDef
For Each TD In CurrentDb.TableDefs
  ...whatever
Next

Open in new window


With the second code block -- even though it looks functionally equivalent -- failing.
Some of that failure can come as a result of scope
CurrentDb exists, presumably when the app opens
It has its listing of objects from the time of its creation
If you've done tabledef deletion/creation, CurrentDb won't reflect that
When you instantiate your own object, the engine round-trips and ensures that the most current collection of objects is in the CurrentDb it gives you for your object.
@JimDettman and I had had an interesting discussion of that a while back.

You clearly haven't tripped on it yet--which is good
0
 
LVL 49

Author Comment

by:Dale Fye
Nick,

The db vs. Currentdb issue has been around for a while, but I think you've got it backwards.  If my recollection is correct, the Currentdb reference will always force Access to refresh the various object collections, while the db version creates a static object based on the Currentdb when the db object is instantiated.

Whenever I'm doing loops in code that reference a database object, I will generally declare the db object and use it, but in cases like this, I don't think it is necessary.

Another point about using db vs Currentdb is that if you are working in the immediate window, use the db method, otherwise, references to various database objects will loose their scope.
0
 
LVL 49

Author Comment

by:Dale Fye
Nick,

I hope this is useful, I know you seemed really frustrated with the NavPain during that thread we were working on together the other day.

Dale
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Join & Write a Comment

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month