On Error for acCmdImport

[NshSteven78]

I searched the forum and didn't see this already asked somewhere...so I hope it isn't a duplicate.



I created a simple file comparison database. On the form there is a button to import a .txt file. The button works for users that have Access installed, but not for those that are using the runtime version of Access. I am thinking that there is something wrong with my code as I do not have an "On Error" line. I'm not sure how to write the on error tag. Below is what I have for the code. Any help would be much appreciated. The computer where I have the db is running XP with Microsoft Access 2002 if that means anything.

Private Sub Command1_Click ()
DoCmd.RunCommand acCmdImport
End Sub

[pbaldy]

You're looking for how to write the error trap?

http://www.baldyweb.com/ErrorTrap.htm

Or are we looking to fix the problem? I'd probably be using TransferText; I'm unfamiliar with that particular code.

[NshSteven78]

I feel stupid, but I have actually seen that document before and tried to write the error trap based on that - but I can't get it to work. Basically, I know just enough about Access to mess it up. It might help if I explain what I am trying to get the db to do.

I have one table called "Deleted Codes". Then I have a table called "QA Import" which is blank (just headers). The user obtains their file from an outside source and saves it as a .txt file. Inside the database, they go to the form, click on the "Import" button that I created. It allows them to import their .txt file and they import it to the existing "QA Import" table. Then they click another button on the form to run an unmatched query. After that, they click a button on the form to clear the data that they imported from the "QA Import" table.

I built the import button in because we are working with medical codes, and they aren't all numbers. So, the table they are importing to has been set up to allow both numbers and text to be imported without them getting the import errors message and some of the data not appending to the table.

As I continue to think about this - I am wondering if the true issue is because most of the users only have the Runtime version of Access. In that version they don't even have the File, Edit, View, Insert, etc toolbar. So, I am thinking that if they don't have access to go to File/Import.....then a button to do that wouldn't work, either.

[pbaldy]

So all that button does is pop up the import dialog? It could be that wouldn't work with the runtime version. I have processes where the user needs to import a spreadsheet or text file. I pop up a file dialog to let them point to the file, and then run TransferSpreadsheet or TransferText against the selected file. Of course, that presumes that the selected file is in the correct format, which in my case I can count on. Most of my users only have the runtime version, and that all works fine for them.

[NshSteven78]

I could probably go that route, myself. Is that a simple code to pop in? The files they import are very basic two column files. They get the fallout errors if they don't import correctly because of the data on the files.

The files have:
CODE RATE
10021 500.00
C1234 600.00

The coding that starts with an alpha falls out. So, I created a blank table for the user to import to and set the tables fields up to accept both numbers and text to keep the codes from falling out.

If you can think of any way to work around this - I would definitely be in your debt!

[pbaldy]

Here's something out of an app I happen to be working on right now:

  Dim db                 As DAO.Database
  On Error GoTo ErrorHandler

  Set db = CurrentDb
  db.Execute "DELETE * FROM tblImportedRecords"
  
  If Len(Dir(g_sExcelPath & "Vehicle Trip Charge Detail.csv")) > 0 Then
    DoCmd.TransferText acImportDelim, "AVI3", "tblImportedRecords", g_sExcelPath & "Vehicle Trip Charge Detail.csv", True
    DoEvents
    Kill g_sExcelPath & "Vehicle Trip Charge Detail.csv"
  Else
    MsgBox "Target file does not exist"
    GoTo ExitHandler
  End If

  'append data from imported table to production table
  db.Execute "qryAppendImportedData", dbFailOnError

  MsgBox "Import complete"

ExitHandler:
  Set db = Nothing
  Exit Sub
ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select

If memory serves, I created the import specification ("AVI3") while manually importing the file to a new local table the first time.

[NshSteven78]

Thank you SO much! I was able to get it going.

[pbaldy]

Excellent! Hopefully you figured out everything I was doing there.