locked
Access 2010 bug with TransferDatabase RRS feed

  • General discussion

  • I have found what for us is a serious Access 2010  bug.

    During the form open event I run the transferdatabase command and when I do it opens the Database (Navigation Pane)  window allowing users direct access to the tables.

    The code is as follows (I have included the copy object line as it may be dependant, I don't know):

    DoCmd.CopyObject Application.CurrentProject.path & "\SKTemp" & AccBExt, "InvShortCheck1part", acTable, "InventoryShortCheckHold"
    DoCmd.TransferDatabase acLink, "Microsoft Access", Application.CurrentProject.path & "\SKTemp" & AccBExt, acTable, "InvShortCheck1part", "InvShortCheck1part"

    This code has been working fine for years in Access 2002. I need a fix for this as a lot of our users have no business having direct access to the Dark Side (tables and queries). We turn off special keys for those users.

    This is aside from the fact that it just LOOKS horribly unprofessional. The nave pane opens and suddenly all of the forms the user has open are shifted to the right by several inches.

    it happens in both the accdb and accde.

    4/23/10 - addtional info..

    The following code SHOULD and WILL hide the nav pane window when run on its own, unfortunately it does NOT work when run immediately after running the transferdatabase command - adding a number of doevents did not help either.

    DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide

    I really need some help with this issue, this is a show stopper for us and I have about 500 users  at 100 companies we are trying to move from 2002 to 2010.

    Thanks for your help.

    Kim



    • Edited by MrKMosher Tuesday, April 24, 2012 3:53 AM
    Tuesday, April 10, 2012 11:04 PM

All replies

  • Are you still working on this?

    Another forum is also discussing and had an answer: http://answers.microsoft.com/en-us/office/forum/office_2010-access/banish-the-access-2010-navigation-pane/a5f854d7-978c-46d5-8262-d6be55ca8a74 Maybe that will help you.


    Joy Eakins


    • Edited by JoyinKS Saturday, April 28, 2012 6:09 PM
    Saturday, April 28, 2012 6:04 PM
  • DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide

    The post has one minor difference in the second line of code but I don't think it matters. "DoCmd." has been added to your code on the second line is not needed.

    But also in the post pointed out by Joy Eakins it is mentioned to make sure the Access settings  for this database are correct, Uncheck the Display Navigation Pane.

    Also I wonder, Since you are runnig this in the Form's Open Event, that you may want to put the code in earlier.

    Here is a little function I call from certain places maybe you can find some use for it.

    Option Compare Database
    Option Explicit
    
    Function ap_ShowRibbon()
       Dim db As DAO.Database
       Dim prop As DAO.Property
       Set db = CurrentDb()
          DoCmd.ShowToolbar "Ribbon", acToolbarNo
       Exit Function
    End Function
    
    So maybe your toolbar name could be inserted here and then add your code DoCmd.TransferDatabase acExport...

    Also comes to mind to make sure since you are now in 2010, that your Libraries are selected VBA.


    Chris Ward

    Sunday, April 29, 2012 6:00 AM
  • You will have to create your linked tables differently -- in otherwords, you cannot use DoCmd.TransferDatabase.  You can use the following code to create your linked tables ...

    Public Sub CreateLinkedTable(strLinkedTableName As String _
                                  , strSourceTable As String _
                                  , strConnectString As String)
    'Create a linked table
        
        Dim tdf As DAO.TableDef
        
        'Create the linked table
        With CurrentDb
        
            'Instantiate the table
            Set tdf = .CreateTableDef(strLinkedTableName)
        
            'Set the properties of importance
            tdf.Connect = strConnectString
            tdf.SourceTableName = strSourceTable
        
            'Append the object to the collection
            .TableDefs.Append tdf
        
        End With
        
        'Refesh the navigation pane/db window
        RefreshDatabaseWindow
        
    End Sub

    Hope that helps!

    Brent Spaulding | Access MVP

    Monday, April 30, 2012 4:34 AM
  • Chris,

    May I ask why you declare a DAO.Property object variable plus declare and set a DAO.Database object variable in the code you posted?

    I know that sometimes I have posted code by copy/pasting out of expanded code blocks I use and have gotten extraneous things in my reply --- I just want to make sure that if that was not the case in this circumstance, you understood that the db and prop variables are not needed in your posted code. :)


    Brent Spaulding | Access MVP

    Monday, April 30, 2012 2:38 PM
  • Good morning Brent,

    It was explained to me that if sometimes in a db you use some code as DAO and other code as ADO that you should in your function declare which it needs to be. But I really don't know that to be the case. Is it true that when running code it looks to the first Library in the list (between ADO or DAO) and only selects the first unless you specify otherwise?

    I have about 16 months ojt now and no formal education in computers and I welcome greater understanding.


    Chris Ward


    • Edited by KCDW Monday, April 30, 2012 3:39 PM
    Monday, April 30, 2012 3:39 PM
  • Hello Chris,

    >> It was explained to me that if sometimes in a db you use some code as DAO and other code as ADO that you should in your function declare which it needs to be. <<

    While it is true that you should explicitly declare your object variables if you have two or more references that have objects of the same name, in order to garentee you get the object type you want.for example:

    Dim rstD As DAO.Recordset
    DIm rstA As ADODB.Recordset

    If you do not fully qualify your object type, VBA will use the FIRST type it finds that matches your declaration.  The order of priority is determined by the order in which your references appear in the references dialog.  So ... if you do this:

    Dim rst As Recordset

    And the ADO library is ABOVE the DAO library, then the rst object variable will be constructed as an ADODB recordset. 

    For object  variables, I make it a habit to fully qualify the declaration simply because I never know when I will be referencing a library that may have an object type name that is shared with another library I had previously referenced.

    -----

    But .... the point I was tring to make was that the propery and database variable you declared are not needed to execute the command.  So your code should be written like this:

    Function ap_ShowRibbon()
       DoCmd.ShowToolbar "Ribbon", acToolbarNo
    End Function


    Brent Spaulding | Access MVP


    Monday, April 30, 2012 3:51 PM
  • Thanks Brent,

    Just replaced the code with yours and it works.


    Chris Ward

    Monday, April 30, 2012 3:59 PM
  • Chris, this is not a toolbar, it is the navigation pane which was new in (2007?)/2010 and is handled dfferently.

    Kim

    Monday, April 30, 2012 7:42 PM
  • Chris, you are absolutely correct. If you use both ado and dao it will use the first one in the list. Also, I have found out recently the more you FULLY reference things the better the chance that things will continue to work if you have a bad refererence to something else. Here is a good start for you on that topic.

    http://allenbrowne.com/ser-38.html

    Now can we stick to the topic  at hand.


    Kim

    Monday, April 30, 2012 7:55 PM
  • >> Now can we stick to the topic  at hand. <<

    Umm ... I am not Chris, but I did stick to the topic at hand.  Did you see the code I posted that you should use to create your linked table?  You will need to use it instead of DoCmd.TransferDatabase.


    Brent Spaulding | Access MVP

    Monday, April 30, 2012 8:47 PM
  • As an additional update this code didn't work because the code was run in a dialog so focus could not go to the navigation pane with eht edelectobject command.

    Running the code AFTER the dialog closed solved that problem but is still ugly since th3e nav pane shows until the dlg closes.

    also, this is an issue only witht the full version of Access, runtime it doesn't happen so ADDITONAL code is needed otherwise you wind up hiding whatever form happens to have the focus.

        If SysCmd(SYSCMD_RUNTIME) = False Then
            DoCmd.SelectObject acTable, , True
            'MsgBox Screen.ActiveForm.Name
            DoCmd.RunCommand acCmdWindowHide
        End If


    Kim

    Monday, April 30, 2012 8:57 PM
  • Chris, this is not a toolbar, it is the navigation pane which was new in (2007?)/2010 and is handled dfferently.

    Kim

    I only have experience in A2007 + a tiny bit in A2010. So while I use that code for the Ribbon and the Status Bar I read a thread that says the same can be used for the Navigation Pane. Unfortunately I can't find that thread. However i also didn't use it for that. I unchecked in the Access Options for the db to turn it off and then after embarrasingly finding out there were two function keys that could turn it on anyway, then I did an override with an AutoKeys Macro. But I suggest looking up Albert Kallal's web sight. He seems to be a master here.

    http://www.kallal.ca/


    Chris Ward

    Monday, April 30, 2012 8:58 PM
  • Brent, I am trying it now and it does not work. I get the error that the table already exists when I get to the append line.

    The table DOES exist in the external DB but there is definitely no link in my CurrentDB. If his code is simply adding the link to DAO and not to the list of Access objects it is no good to me. It must be added to the list of tables as a linked table.

    Here is the code as modified which has worked for years. I am copying a local table to an external temp DB, then linking to it. Note I remmed my transfer line and replaced it with your code.:

    Dim DB As dao.Database

        Set DB = OpenDatabase("SKTemp.accdb")  '6/24/09 - Kim
        DB.TableDefs.Delete "InvShortCheck1Part"  'just in case it exists
        DoCmd.CopyObject "SKTemp.accdb", "InvShortCheck1Part", acTable, "InventoryShortCheckHold"
    '    DoCmd.TransferDatabase acLink, "Microsoft Access", "SKTemp.accdb", acTable,  "InvShortCheck1Part", "InvShortCheck1Part"

    'Create a linked table
       
        Dim tdf As dao.TableDef
       
        'Create the linked table
        With CurrentDb
       
            'Instantiate the table
            Set tdf = .CreateTableDef("InvShortCheck1Part")
       
            'Set the properties of importance
            'tdf.Connect = "Provider=Microsoft.jet;" & "Data Source=" & "SKTemp.accdb" & ";" & "Persist Security Info=False"
            tdf.Connect = ";DATABASE=c:\sk2010\SKTemp.accdb;"
            tdf.SourceTableName = "InvShortCheck1Part"
       
            'Append the object to the collection
            .TableDefs.Append tdf
       
        End With
       
        'Refesh the navigation pane/db window
        RefreshDatabaseWindow


    Kim


    • Edited by MrKMosher Monday, April 30, 2012 9:31 PM
    Monday, April 30, 2012 9:18 PM
  • Let me make clear to all that turning off F11 and other possibly permanent methods are not an option. Some users DO have access to the tables but it needs to be when they want it and press F11. I don't want my code just popping up the nav pane and shoving all the forms over.

    Kim

    Monday, April 30, 2012 9:21 PM
  • oKay,

    How about removing the vba Code altogether and just add to your AutoExec Macro

    LockNavigationPane = Yes. Then the Autokeys should still work {F11}. Right?

    The F11 key code calls to expand the Navigation Pane not to open it or unhide it. So while F11 expands it even from a hidden state, pressing the F11 key again has the opposite affect of collapsing it rather than hidding it. So I would still suggest turning off the Auto Keys and reprogram the F11 key as a toggle to Unhide and Hide the Navigation Pane. Then in your code to Transferdata you could make a call to your function for the F11 key to hide the Navigation Pane.

    But I wonder what is causing the problem to begin with...I never heard anything about this before. So maybe the culprit is not Access at all but maybe there is a little corruption in your db. Have you tried Compact & Repair? if Yes, Decompile & Compile & Compact & Repair, If Yes then perhaps there is another piece of code that is calling for the Nav Pane to Expand. I would search your code tosee if there is another call. If that's not it then I would consider importing everything to a new shell.

    I apologize for hi-jacking your thread earlier - I sometimes see an opportunity for knowledge and jump on it.


    Chris Ward

    Monday, April 30, 2012 9:50 PM
  • The code indeed adds a Linked Table object to the CurrentDb.  I have modified your code slightly.  Please note that visibility in the NavPane does not neccessarily mean the Table object does not exist.  It could be that the NavPane had just not been refreshed yet to show the latest versions of the collection you are looking at.

    Sub fooo()
        Dim DB As DAO.Database
        Set DB = OpenDatabase("SKTemp.accdb")  '6/24/09 - Kim
        DB.TableDefs.Delete "InvShortCheck1Part"  'just in case it exists
        
        DoCmd.CopyObject "SKTemp.accdb", "InvShortCheck1Part", acTable, "InventoryShortCheckHold"
        'Create a linked table
        Dim tdf As DAO.TableDef
        
        'Delete the current linked table object if it exists.
        If DCount("*", "MSysObjects", "[Name]='InvShortCheck1Part' And [Type] In (1,4,6)") > 0 Then
            DoCmd.DeleteObject acTable, "InvShortCheck1Part"
        End If
        
        'Create the linked table object
        With CurrentDb
        
            'Instantiate the table
            Set tdf = .CreateTableDef("InvShortCheck1Part")
        
            'Set the properties of importance
            tdf.Connect = ";DATABASE=c:\sk2010\SKTemp.accdb"
            tdf.SourceTableName = "InvShortCheck1Part"
        
            'Append the object to the collection
            .TableDefs.Append tdf
        
        End With
        
        'Refesh the navigation pane/db window
        RefreshDatabaseWindow
    End Sub
    Please let me know if that works for you.

    Brent Spaulding | Access MVP

    Monday, April 30, 2012 10:05 PM
  • how to call this function?
    Tuesday, November 17, 2020 7:48 PM
  • 8 years on and this thread has just been resurrected by @sandanet

    Anyway, I have a solution that doesn't appear to have been mentioned earlier.

    This is a quote from the Modal Forms section of my website article Controlling the Application Interface.  The example app mentioned is called TestModal and can be found at that link

    Modal forms

    This was written in response to a very old thread at Access World Forums where AWF member tonyluke had  issues using modal forms when the application window was hidden. As stated in that thread, doing an action such as transfer database with a modal form running will make the navigation pane visible if it was hidden.

     

    For most purposes, this is useful behaviour so you can check if the action has worked but where you need it to remain hidden, you can use code to hide the navigation pane again immediately

     

    However this behaviour won't occur if you also hide the entire application window

     

    The example FE/BE databases attached should be saved in the same folder

    The BE just has a dummy table to test for linking

    The FE contains a modal form with the application window hidden by default

     

    i) Click the Link Table button - the nav pane remains hidden

       Click the button again to delete the linked table

    ii) Click the Show Navigation Pane button then click the Link Table button

    Hope that helps someone even if its 8 years too late for the OP



    • Edited by isladogs52 Tuesday, November 17, 2020 8:08 PM
    Tuesday, November 17, 2020 8:05 PM