none
Transferspreadsheet / Navigation Pane RRS feed

  • Question

  • Hello,

    I have an Access 2010 database which uses the transferspreadsheet method to import Excel data into a linked table. I have just found out the method fails when the Navigation pane is not visible. I want to distribute the database with the Navigation Pane hidden using the following command;

    db.Properties("StartupShowDBWindow") = False

    I have tried the following command to unhide the navigation pane prior to running Docmd.TransferSpreadsheet;

    DoCmd.SelectObject acTable, "TableName", True

    but this only opened the navigation pane, pushed the active form to the right and failed.

    Could someone please advise a suitable work around.

    Thank you.

    Wednesday, July 15, 2015 10:19 AM

Answers

All replies

  • Hi,

    Can you post the import code? I can't imagine why it would need the Nav Pane.

    Wednesday, July 15, 2015 1:53 PM
  • The import code is just a standard transferspreadsheet command;

    DoCmd.TransferSpreadsheet _
                TransferType:=acImport, _
                SpreadsheetType:=acSpreadsheetTypeExcel12, _
                tablename:="dbo_tbl_tablename", _
                FileName:=xclBook.FullName, _
                HasFieldNames:=True, _
                Range:=xclNewSheet.Name & "!A1:R100"

    The table is a linked table  (SQL Server backend).


    • Edited by SineIn Wednesday, July 15, 2015 11:40 PM
    Wednesday, July 15, 2015 10:47 PM
  • According to you Description, you can try like below:

    Private Sub cmdHide_Click()
       DoCmd.NavigateTo "acNavigationCategoryObjectType"
       RunCommand acCmdWindowHide
    End Sub
    Private Sub cmdUnHide_Click()
       DoCmd.TransferSpreadsheet _
                 TransferType:=acImport, _
                 SpreadsheetType:=acSpreadsheetTypeExcel12, _
                 tablename:="dbo_tbl_tablename", _
                 FileName:=xclBook.FullName, _
                 HasFieldNames:=True, _
                 Range:=xclNewSheet.Name & "!A1:R100"
       DoCmd.SelectObject acTable, "TableName", True   
    End Sub
    

    Thursday, July 16, 2015 9:38 AM
  • David,

    Your code will hide the Navigation Pane. I am already distributing the application with the Navigation Pane closed.

    Effectively I need to open the Nav Pane and minimize it. How can this be accomplished using VBA?

    Thursday, July 16, 2015 10:06 AM
  • Hi SineIn,

    Thanks for posting the code. I still can't see why it would need the Nav Pane to be visible for it to work. I don't have access to a SQL Server to try it out. Can you try using a local table just to see if you get the same problem? If not, then I will understand that it's a matter of having a linked SQL Server table, which I have never heard before to cause an issue like this. If you get the same issue with a local table, then the problem is probably caused somewhere else in your setup.

    Just a thought...

    Thursday, July 16, 2015 3:52 PM
  • Thanks DB Guy,

    Yes, it is a linked table issue as the import works for a local table.

    Think I will have to use the BULK INSERT method to load directly into the SQL Server table to work around this.

    Friday, July 17, 2015 1:17 AM
  • Hi,

    Thanks for verifying that. I will have to ask my SQL Server friends if they have a solution to this.

    Friday, July 17, 2015 1:52 AM
  • Hi Sineln,

    >> Think I will have to use the BULK INSERT method to load directly into the SQL Server table to work around this.

    I think this would work for you. After you import the data into SQL Server table, the data in Access table would keep sync.

    >> I have just found out the method fails when the Navigation pane is not visible.

    I made a test with import Excel data into a linked table under the Navigation pane is hide, but I failed to reproduce your issue. It worked correctly for me. To be honesty, I test the linked table in Access database instead of SQL server. When your method failed, did you get any error?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, July 17, 2015 5:35 AM
  • According to you Description, you can try like below :
    Private Sub Command0_Click()
    
       DoCmd.NavigateTo "acNavigationCategoryObjectType"
    
       'RunCommand acCmdWindowHide
    
       DoCmd.Minimize
    
    End Sub
    

    Friday, July 17, 2015 6:05 AM
  • Hi all,

    I have retested importing into a SQL Server linked table and now I cannot reproduce my original error.

    Everything is working as expected.

    Thanks everyone for your time and effort. 

    Wednesday, July 22, 2015 5:31 AM