Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error Handler Not Working in Access VBA

Posted on 2007-11-29
5
Medium Priority
?
340 Views
Last Modified: 2008-02-01
I'm importing a set of CSV files into Access.

My procedure loops through a list of filepaths kept in a table. Some days, some of the files will not exist so I want the code to proceed to the next in the list. I've used On Error Resume Next, On Error GoTo Move_To_Next_File etc - all the usual error handling techniques - but my code still breaks.

I'm using the Scripting FileSystemObject library in this procedure. Some of the source files have "." in the filename (which Access cannot import) so I use FSO's file.copy method to copy the file to a temporary folder under a different name, then import. However, when I encounter a file that does not exist it breaks here:

Set file = FSO.GetFile(filepath)

It will not Resume Next or GoTo the label I specify.
0
Comment
Question by:TimHudspith
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20372824
Have you got your error handling setting switched to Break On All errors?
If so - set it to Break on Unhandled Errors.
(Tools>options, General tab)
0
 

Author Comment

by:TimHudspith
ID: 20372840
It's already set to 'Break on Unhandled Errors'.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 total points
ID: 20372922
Is the error coming about beause the file doesn't exist? If so, you could use the FileExists method of the FSO to check first (sorry, don't know much about that library, as I absolutely refuse to use it since it can be entirely disabled by network admins or antivirus software).
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 20373063
<My procedure loops through a list of filepaths kept in a table>
If there is a particular reason for this,
try using

dim xFile
xfile=dir(filePath &"\*.csv")
while xFile<>""
         if  not Isnull(dlookup(filepath &"\"& xfile,"TableName")) then
               'import the file
             else
               'whatever
          end if

         xFile=dir
wend
0
 

Author Comment

by:TimHudspith
ID: 20382048
LSMConsuulting: Yes, it breaks because the file does not exist. I completely missed the FileExists method - I'll use it for now. Good to know about the FSO shortcomings also.

Capricorn1 - I haven't come across dlookup(). Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

916 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