?
Solved

MS Access - How do I trap an error for a bad import

Posted on 2011-09-14
2
Medium Priority
?
518 Views
Last Modified: 2012-05-12
In the below code I want to:
1-display a message box that the import failed if there was a problem with this specific line of code ... DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImportExcel", sOpenFile,
2-Exit the module and go back to my home form

NOTE, I am calling this module from a form.  Thus, if an error occurs I need to make sure I exit this module and I am placed back at my form object where I called Import()



Public Sub Import ()
'Get sheet name
Dim sOpenFile As String
'
'display the open file dialog box
sOpenFile = OpenFileDialog("Import Data File")
If sOpenFile <> "" Then
'the user has selected a file to import, the filename and path
'are contained in the variable sOpenFile
sOpenFile = Trim$(sOpenFile) 'remove all trailing spaces
'make sure it's an Excel file
If Right(sOpenFile, 4) <> ".xls" Then
MsgBox "You must select an Excel (.xls) file.", vbOKOnly
Else
KRunQuery ("qImportDeleteTableExcel")' just a call to the same query running code that Access Wizard generates, which I saved as a subroutine. This query deletes the data in the temporary import table. You would need to create your own delete query, or substitute code to do so.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImportExcel", sOpenFile, True
MsgBox "Initial transfer from spreadsheet to Import Table is COMPLETE! Proceed to next step.", vbOKOnly
End If
End If
End Sub
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 36540213
Give this a shot (see the comments in the code):

Public Sub Import ()
    'Get sheet name
     Dim sOpenFile As String

    On Error goto EH   '<-----***** Add this
'
    'display the open file dialog box
    sOpenFile = OpenFileDialog("Import Data File")
    If sOpenFile <> "" Then
         'the user has selected a file to import, the filename and path
         'are contained in the variable sOpenFile
          sOpenFile = Trim$(sOpenFile) 'remove all trailing spaces
         'make sure it's an Excel file
         If Right(sOpenFile, 4) <> ".xls" Then
                MsgBox "You must select an Excel (.xls) file.", vbOKOnly
        Else
                KRunQuery ("qImportDeleteTableExcel")' just a call to the same query running code that Access Wizard generates, which I saved as a subroutine. This query deletes the data in the temporary import table. You would need to create your own delete query, or substitute code to do so.
                On Error Goto EH_Transfer   '<------- ***** Add this
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImportExcel", sOpenFile, True
                MsgBox "Initial transfer from spreadsheet to Import Table is COMPLETE! Proceed to next step.", vbOKOnly
       End If
   End If
Exit Sub   '<----- **** add this

EH:
    msgbox "Error " & err.number & ": " & err.description
    Exit Sub
EH_Transfer:
   msgbox "Error " & err.number & ": " & err.description & vbcrlf & "Transfer spreadsheet statement failed!"



End Sub 

Open in new window

0
 

Author Closing Comment

by:upobDaPlaya
ID: 36540438
Very nice...thank you...this is good as I can trap any non import errors and then trap an error specific to the import..thanks for shring your knowledge..
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question