File detect and execute (1 Viewer)

[mreference]

I don't know how to search for this to see if it already exists in the forums.

Basically, in a nutshell, is it possible that access can detect a .csv file in a folder on a pc, then import that file into a table and run a query at 6am every morning on a mon-fri only.

Then once it has been imported, move the csv file in another folder so that the same routine can be run the following day.

If it is possible, can you let me know search terms to scan the forum for that would be welcome.

 

[CJ_London]

try googling 'vba detect file exists'

plenty of examples come up which you can adapt

 

[Solo712]

I don't know how to search for this to see if it already exists in the forums.

Basically, in a nutshell, is it possible that access can detect a .csv file in a folder on a pc, then import that file into a table and run a query at 6am every morning on a mon-fri only.

Then once it has been imported, move the csv file in another folder so that the same routine can be run the following day.

If it is possible, can you let me know search terms to scan the forum for that would be welcome.

Yes, it is possible and the steps are as follows:

1) Use the VBA Dir() function to find out if file exists:

2) Import the csv file using e.g.

     DoCmd.TransferText acImportDelim, "", strTableName, txtPath & "" & StrFileName, True

(make sure you understand and select the arguments)

3) Use VBA FileCopy finction to move the imported function to another directory

best,
Jiri

 

[Mark_]

For having your process run at "6am every morning on a mon-fri only", you would want to read up a little on .timer for some assistance.

Check if Weekday(now()) > 0 (Sunday) and <7 (Saturday).
If so, then if Time(Now()) > 6am go ahead and do the import and copy.

Your logical test for this is if the file exists. If it does, it hasn't been imported yet. If it doesn't, it has.

For myself I'd set the timer to a minute, that way you don't spend much time checking. If it is at all slowing down your program up to 5 or 10 minutes.

 

[moke123]

An alternative to a timer event could also be used.
have a table "tblImports" with a date field.
Create a procedure that runs on startup of the front end.
have that procedure check tblImports for the current days date.
If found exit the procedure.
If not found then run the import procedure and then update tblImports with the current date.
With this method the import will only occur once daily for the first person to open the database that day.

 

[Mark_]

Moke, my first though was an "On startup", but OP is asking for it to run at a specific time on specific days.

 

[The_Doc_Man]

Here is your issue for the "daily at some specific time."

Access cannot do this unless it is open at the time because no Access timers are running if Access is not running.

Further, the machine that will run the function has to running at the time i.e. can't be turned off. If your machines have an automatic shutdown imposed on them in your environment, you need to get an exception or set it up on a server. Some shops would allow this; others might not (having an application on a server rather than on client systems).

Finally, that machine that will perform your operation has to have Access installed on it.

If you have a machine that will be running at the right time and could manually open Access, then you can do this with a Windows Task Scheduler operation.

You create an event in Task scheduler that has the required timing. Daily at a given time of day is one of the easier cases.

The event you run has to launch Access naming your database file. You can give the command directly OR you could create a batch file that contains the command.

This command has to be a command-line operation because you need one special part on the launched command line: /x macro-name (after the name of the DB file).

Inside the database you need a macro of the given name that will do the import and perform the rename for you.

The EASIEST way to do this is to write a function (NOT A SUB!!!) residing in a general module that will do every step for you in VBA with all of your normal error trapping if you have any. The function can return a garbage value, but I usually make them return TRUE as a Boolean Function. For reasons known only to obscure people in the depths of the Microsoft dungeon, you can't run a Sub this way, only a function.

Then the macro has to have a RunCode step that runs the special function that does all of the real work. It ALSO has to have a step that causes an Application .Quit operation when it is done.

Doing it this way means you can test the code from a form that calls the function (using the form strictly as a testing scaffold) when you click some button. Then, when it all works, you just never use the scaffold again.

 

[moke123]

I am not a big fan of form timer events especially during development and only use them in a very limited capacity.
I assume that what you are trying to do is append some data to your database that is delivered daily to a specific folder.
I was playing around with the idea and did this simple demo to demonstrate how to determine if a file exists, how to connect to a csv file without importing it, run an append query that appends the data to Another seperate database, unlinks the csv file, Re-names the csv file and copies it to a folder, deletes the original Csv file, and records the import into a table.
My thought with this was that you can leave this database running with the timer event turned on without it affecting your main database.
It should at least give you some insight on how to do some of the things you were interested in.