ImportExportText when csv file may be empty

[Juddy]

Hi,

I want to have a macro which runs daily and appends an existing table with data from a csv file. I have managed to successfully do this using the 'ImportExportText' macro. However, on some rare occasions the csv file will be blank if no transactions have occurred on the previous day. This obviously causes an error when the macro is run which states 'Query must have at least one destination field'. Is there anyway to stop this message and get access to skip this 'ImportExportText' macro if the csv file is blank?

Thanks in advance for any help

Kind Regards
Kim

 

[Ranman256]

you could attach the csv file as a table. (one with data)
instead of IMPORTEXPORT, you would run an append query.
if the table count = 0 then do not import.

1. get new csv file
2. overwrite the same CSV file everytime, c:\folder\File2Import.csv
3. run import macro....if Dcount("*","tCsvTbl")> 0 then docmd.openquery "qaImportCsV"

 

[Isskint]

Hi Juddy

Simplest way i can think of would be to use error trapping and ignore the "Query must have at least one destination field" error.

To do this;
Add error trapping and use a message box to display the err.number
Run the macro when you know the CSV file is empty, so you get the error number
Amend your code to only show errors where the err.number <> the noted number.

 

[arnelgp]

add error handler on your macro. insert a line at the start of your macro.

OnError
GoTo: Macro Name
Macro Name: SubErr

now, on the buttom of your macro, add a submacro, name it SubErr.

SubMacro: SubErr
StopAllMacros

 

[Juddy]

Hi,

Thank you very much for all of the quick responses. I went with linking the csv file and then running an append query if the number of records was greater than 0 as suggested first and it worked perfectly.

Thanks again
Kim