Why is navigation pane displayed after code runs

I am using the following code (thanks to several experts last week to import an Excel file and do some magic with the end results in the table.

But for some reason after the code has finished the navigation pane displays even though I have it "turned off" in the settings... File > Options > Current Database.

Can someone tell me why this is happening?

Code:

Private Sub cmdImportAndClean_Click()
On Error GoTo Err_cmdImportAndClean_Click

    If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
        MsgBox "please select excel file"
        Me.cmdSelect.SetFocus
        Exit Sub
    End If

    If MsgBox("This function will import the Excel file you have selected and clean the data. Do you want to continue?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then

    DoCmd.Hourglass True

    'Import Excel file into tblTempImport
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempImport", Me.txtFileName, True
   
    'Code to delete any import error tables
    Dim tblDef As TableDef
    For Each tblDef In CurrentDb.TableDefs
    If InStr(1, tblDef.Name, "ImportError") > 0 Then
        DoCmd.SelectObject acTable, tblDef.Name, True
        DoCmd.PrintOut
        DoCmd.DeleteObject acTable, tblDef.Name
        Beep
        MsgBox "There was an error importing all of your records." & vbCrLf & vbLf & "An error report was sent to your default printer." & vbCrLf & vbLf & "The error report will detail the error reason for each field and row number for each record that was not successfully imported from your file." & vbCrLf & vbLf & "Please correct all errors and import your data again.", vbInformation, "Import Errors"
    End If
    Next tblDef

    'Use tblTempImport to add records to table named tblImportRecords
    Dim db As Database
    Set db = CurrentDb

    db.Execute "apndtblqryImportRecords", dbFailOnError
   
    'Run query to delete records from tblTempImport
    db.Execute "delqryDeleteRecordsFromtblTempImport", dbFailOnError
   
    'Data cleaning process
    db.Execute "qryCleanTable1", dbFailOnError
    db.Execute "qryCleanTable2", dbFailOnError
    db.Execute "qryCleanTable3", dbFailOnError
    MsgBox "Data Import and Cleaning Finished."
    'End of data cleaning process
   
    Else
   
    MsgBox "Import/Clean Function Cancelled"

    DoCmd.Close , "frmBrowse", acSaveNo
   
    End If

    DoCmd.Close , "frmBrowse", acSaveNo
    DoCmd.Hourglass False
 
Exit_cmdImportAndClean_Click:
    Exit Sub
   
Err_cmdImportAndClean_Click:
    MsgBox Err.Description
    Resume Exit_cmdImportAndClean_Click
 
End Sub
SteveL13Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Looks like you're also printing the table. That's going to be troublesome, since the only way to "print" a table is to select it (like you're doing), and in doing that (selecting it) you'll always show the Nav Pane.

You could output that table to a Textfile instead, something like this:

Dim fld As DAO.Field
Dim rst As DAO.Recordset
Set rst = "SELECT * FROM " & tblDef.Name

Dim sPath As String
sPath = "C:\YourFileSavePathHere\

Dim iFile As Integer
iFile = FreeFile

Open sPath & "\ImportErrors.txt" For Output AS iFile

Dim s As String

Do Until rst.EOF
  s = ""
  For each fld In rst.Fields
    s = fld.Value & "," & s
  Next fld
  '/ print the data
  Print iFile, s

  rs.MoveNext
Loop

Close iFile
rst.Close
Set rst = Nothing

AFTER doing that, you can then delete the table:
With this:

Currentproject.Connection.Execute "DROP TABLE " & tblDef.Name

You'd do away with the SelectObject, DeleteObject lines (and the Printout line).
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Commands like this one will show the Nav Pane:

        DoCmd.SelectObject acTable, tblDef.Name, True

Is there some reason you need to Select that object? I'm not sure what your goal is with that block of code ... but that's what is causing the Nav Pane to show.
0
 
SteveL13Author Commented:
The import Excel file part of this is leaving behind a import error table which I don't need because we'll never need, nor are we importing, the field that has the issue in the import.  So I'm just trying to automatically delete the import error table.

The code for the import is:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempImport", Me.txtFileName, True

Out of all the fields in the Excel file I am only bringing about 1/2 of them into the table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.