CODEWITHSUNDEEP
import-from-csv
Till Ducroquet
Till Ducroquet

Reputation: 1

vba importexportspecification not working twice

Seems CurrentProject.ImportExportSpecifications("import-CP_Commandes").Execute has sometimes a strange behaviour. I need to import daily one or several csv files that will be inserted into an access database. Each file is previously copied in a file named CP_Commandes.csv. I have written a VBA function within Access to do the job. I have created the necessary importexport specification (named import-CP_Commandes). The relevant xml is stored in a txt file. It works fine with the first file, i.e. the CP_commandes.csv is imported into the relevant Access table (x_temp_commandes). Problem is, after copying the next file into CP_commandes.csv, Executing the import does just nothing, without any error. It looks like Access considers it has already done the job ! Even after stopping the program and re-launching, same result : x_temp_commandes is empty ! After some time, maybe an hour or more, I cant' be more precise, the second file is treated OK ...

I tried different solutions for file #2 to cope with this problem.

Solution 1 :

CurrentProject.ImportExportSpecifications("import-CP_Commandes").delete
DoEvents
CurrentProject.ImportExportSpecifications.add "import-CP_Commandes",ch  '(ch previously loaded from the relevant xml file)
DoEvents
CurrentProject.ImportExportSpecifications("import-CP_Commandes").Execute
=> no error, x_temp_commandes empty

Solution 2 : Same as solution 1, but with ch modified to specify the actual file to be imported instead of CP_Commandes.csv => no error, x_temp_commandes empty.

But sometimes, the program without ANY modification is able to import 2 or 3 files without problem.

Hence my question : Is there something I can do to make sure the work will be done ?

Upvotes: 0

Views: 115

Answers (1)

Till Ducroquet
Till Ducroquet

Reputation: 1

Sorry I finally figured it out - I was not closing the recordset which I oped with

set rs=CurrentDb.OpenRecordset("x_Temp_CP_Commandes")

For this reason, Import did not work - but it did not mention an error. By applying an rs.close after this, the following files import correctly.

Upvotes: 0

Related Questions