Access 2010 bug with TransferDatabase

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Here is a long shot. Read this thread about Windows Focus Issues.

http://answers.microsoft.com/en-us/windows/forum/windows_7-performance/windows-7-x64-pro-window-focus-issueswhen-i-open-a/a55aec2b-ee7e-40f2-bc8b-8f4728671ca4?auth=1

---

Chris Ward

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

Thanks Brent,

Just replaced the code with yours and it works.

---

Chris Ward

Answer 9

Chris, this is not a toolbar, it is the navigation pane which was new in (2007?)/2010 and is handled dfferently.

---

Kim

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

how to call this function?

Answer 19

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