Import csv, export queries, delete queries then repeat.

[cochise]

Any help on the following will be greatly appreciated:

1) I wish to import the first CSV in "C:\Documents" and create a table called "Data" with import specs I have already created.

2) Next I wish to Export all fields in a query (Sport=Football, Event=Match Odds) to an excel binary file called "Match Odds 001"
I then wish to delete the data in this query from the Table "Data".

I then wish to repeat 2) a number of times exporting each query to an excel binary file with the name "(Event) 001" then deleting that query data from the table.

After all queries have run and been deleted from the table any remaining data will be exported to an excel binary file named "Misc 001" and then this data deleted from the Table "Data" (or maybe even delete the Table "Data" completely.)

I may want to compact & repair the database at this point to reduce the file size if that is necessary.

I then wish to repeat the whole process again but with the next csv file in "C:\Documents".
Exported files would be named "(Event) 002" so as not to replace the previous files.

I have just started using VBA in Access and so far have code only to import data for a single named csv file.
I continually receive errors when trying to use a SELECT command.

Any help on the vba coding required or providing links to sites where I might find the information to solve this problem is hugely appreciated.

Thanks,

Cochise

 

[CJ_London]

Look at using the transfertext function to import each .csv file and transfer spreadsheet to export your query to excel so your vba would look something like (change names and parameters to suit):

docmd.transfertext acimportdelim, specname, "Data","C:\documents\" & "[COLOR=red]firstfilename[/COLOR].csv", True
docmd.transferspreadsheet acexport, acSpreadsheetTypeExcel12, "[COLOR=red]Query1[/COLOR]", "[COLOR=red]C:\documents\[/COLOR]" & "Match Odds 001" & ".xlsb", True
currentdb.execute "DELETE * FROM [COLOR=red]Query1[/COLOR]"
'note the execute command assumes that your query is a select query and updateable

You haven't provided the information so the bit in red will need to be changed

 

[namliam]

If you are processing large enough files for you to consider compacting, you will want to make a seperate backend database, so you can tell it to compact on demand using:
Application.CompactRepair
Or use the MSAccess command line to compact the backend database

To loop the files in your folder, look into the "Do While" and "Dir" functions