locked
Navigation Pane automatically opens RRS feed

  • Question

  • I have run into the strangest thing. I have the following code which creates a linked table named "Test Account" in my BE file:

    Application.Echo False
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim myTable As DAO.TableDef
    Dim myField As DAO.Field
    Dim myProp As DAO.Property
    Dim MyTableName As String
    Dim MyConnection As String
    MyConnection = DLookup("[Database]", "CurrentConnection")  'A query with my current connection string
        MyTableName = "Test Account"
        Set myTable = db.CreateTableDef(MyTableName)
        With myTable
            .Fields.Append .CreateField("TYPE", dbText)
            .Fields.Append .CreateField("SYMBOL", dbText)
        End With
    db.TableDefs.Append myTable
    DoCmd.TransferDatabase acExport, "Microsoft Access", MyConnection, acTable, myTable.Name, myTable.Name
    DoCmd.DeleteObject acTable, myTable.Name
    DoCmd.TransferDatabase acLink, "Microsoft Access", MyConnection, acTable, myTable.Name, myTable.Name
    Set myField = Nothing
    Set myTable = Nothing
    Application.Echo True

    The code works perfectly well, but when it executes the DoCmd.TransferDatabase acLink command, the Navigation Pane pops open automatically even with the Application.Echo False command in force.

    I have been using ACCESS since 1999 and have never seen this kind of thing where a line of code causes the Navigation Pane to pop open automatically.

    I know how to hide the Pane again using Daniel Pineaults custom HideNavPane function, but has anyone seen this before or know how to work around it?

    Saturday, May 2, 2020 3:24 PM

Answers

  • It's always been my experience that using DoCmd.TransferDatabase to link a table causes the navigation pane to be shown.  For applications where I don't want that to happen, I create the linked tabledef in code, using DAO objects.  For example, I have this function:

    Function LinkTableInCode(DatabasePath As String, SourceTableName As String, LinkedTableName As String) As String
    
        On Error GoTo Err_Handler
        LinkTableInCode = vbNullString
    
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set db = CurrentDb
        
        Set tdf = db.CreateTableDef(LinkedTableName)
        tdf.Connect = ";DATABASE=" & DatabasePath
        tdf.SourceTableName = SourceTableName
        
        db.TableDefs.Append tdf
    
    Exit_Point:
        Exit Function
    
    Err_Handler:
        LinkTableInCode = Err.Number & " - " & Err.Description
        Resume Exit_Point
        
    End Function

    The argument <DatabasePath> is the path to the database containing the table to be linked. The function returns an error message if an error is raised.

    This function does not affect the visibility of the nav pane.

    I do wonder why you first create the table in the current database, then export it to the other database, then delete it in this database, and then link it from the other database.  Why not just open a connection to the other database, create the table, and then link it?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, May 7, 2020 9:40 PM

All replies

  • or know how to work around it?

    Hi Lawrence,

    If I understand the code correctly, then you want to create a new table in an external Access-database, and make a link to that table.

    I also have that kind of functionality in my applications, but I do not use the TransferDatabase method. I only use TransferDatabase to move existing tables from one database to another.

    To modify tables, including create new tables or delete tables, in an external database, I open a hidden new Application for that database, and do there all the modifications.

    To link a table you can set the .Connect property and run the .RefreshLink method, in the current database or the external, depending on which table is linked to which.

    Imb.



    • Edited by Imb-hb Saturday, May 2, 2020 5:14 PM edit
    Saturday, May 2, 2020 5:12 PM
  • It's always been my experience that using DoCmd.TransferDatabase to link a table causes the navigation pane to be shown.  For applications where I don't want that to happen, I create the linked tabledef in code, using DAO objects.  For example, I have this function:

    Function LinkTableInCode(DatabasePath As String, SourceTableName As String, LinkedTableName As String) As String
    
        On Error GoTo Err_Handler
        LinkTableInCode = vbNullString
    
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set db = CurrentDb
        
        Set tdf = db.CreateTableDef(LinkedTableName)
        tdf.Connect = ";DATABASE=" & DatabasePath
        tdf.SourceTableName = SourceTableName
        
        db.TableDefs.Append tdf
    
    Exit_Point:
        Exit Function
    
    Err_Handler:
        LinkTableInCode = Err.Number & " - " & Err.Description
        Resume Exit_Point
        
    End Function

    The argument <DatabasePath> is the path to the database containing the table to be linked. The function returns an error message if an error is raised.

    This function does not affect the visibility of the nav pane.

    I do wonder why you first create the table in the current database, then export it to the other database, then delete it in this database, and then link it from the other database.  Why not just open a connection to the other database, create the table, and then link it?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, May 7, 2020 9:40 PM
  • Thanks so much Dirk. I will give your custom function a try. I'll let you know if I have any problems.

    Thursday, May 7, 2020 11:35 PM