Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Possible to export a csv file to a web site server address every 5 minutes?

Posted on 2015-02-02
9
Medium Priority
?
102 Views
Last Modified: 2016-02-11
Is it possible to export a query results as a csv file to a web site server address every 5 minutes?

Does the database have to be open to do so?  I would think so.

If it does need to be open to do so  then how would it be done while it is open?

How can this be done if it is possible?

--Steve
0
Comment
Question by:SteveL13
9 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40584645
You can export the csv file and transfer it via FTP simply overwriting the existing.

For this to work, the web server must only read the file when needed or be set to refresh its cache every five minutes or so. Your FTP export should be build to accept a fail in case the export happens in the few seconds while the web server refreshes its cache.

/gustav
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40584857
I would create a separate FE for just this task.  Put it on a computer that is always on.  Create a startup form that includes a Timer event.  The Timer event would run the code to create the .csv file and move it to the FTP folder.  With a 5-minute interval, you don't want the overhead of opening and closing the database each time.

This will still need to be monitored to ensure that the computer hasn't been turned off or rebooted which would stop the Access app.  You can.  Create a Windows task that the scheduler can run say at 1 AM every day.  It would close the Access app if it is open, and then reopen it.
0
 

Author Comment

by:SteveL13
ID: 40584907
Pat,

Interesting idea.  I'll try this and get back to you.

--Steve
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40586622
The kicker here is what happens if an "event" is missed?
(Power failure, invalid data, errors, loss of internet connection, ...ect)
How would you detect a failure?
Would you simply skip that export?
How would you treat each different failure?
Would you need roll backs?
How would the data be affected?
...etc

Does his need to be "precisely" every 5 minutes?
The Access Timer event is low on the processor's priority list, so it may not be accurate over long periods of time.
Is this export system temporary?
With all of Access' possible foibles, ...this may not be 100% reliable in the long term.

You may wish to, eventually, move this data to have more direct access to the web server...

JeffCoachman
0
 

Author Comment

by:SteveL13
ID: 40586902
I have created a separate front-end file that is linked to the back-end table.  Then I have created a query i n the new f/e file which gets all of the data from the table.  Then I have a form that opens when the database is opened.  The form has an ontimer event of...

Private Sub Form_Timer()
Dim strCSV As String
    strCSV = Me.txtExportLocation
    DoCmd.OutputTo acOutputQuery, "qryExportQuoteJobData", acFormatXLS, strCSV, True
End Sub

Open in new window


Me.txtExportLocation is a location on my hard drive.

And I have the timer set at 300000 (which I think is 5 minutes).

But nothing happens.  The database is open.  The form is open.  What am I doing wrong?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40587009
Not sure, but if you closed the form, you reset the Timer
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40587035
Dim strCSV As String
    strCSV = Me.txtExportLocation
    DoCmd.OutputTo acOutputQuery, "qryExportQuoteJobData", acFormatXLS, strCSV, True

The fourth argument in OutputTo is the Path of the output file...
AFAICT, You have set it to it a control... so does this control store a valid path?

You waited the entire 5 minutes?

This worked fine for me...:
    DoCmd.OutputTo acOutputQuery, "qry1Locations", acFormatXLS, "c:\YourFolder\xxx.xls", True

As I stated, the Access timer is low on the processors priority
So it may not fire reliably if your db, (or another application/process) is running something that is process intensive...

Lets see what the other Experts contribute...

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40587071
...something eles...
Did you compile the code...?

if you just pasted the entire sub into the the VBA editor, ...it may not run.
Go through the property box to the Timer event, then build the code, ...then compile...

In any event, try the code like this first (with a timer interval or 10000), to be sure it will run:
Private Sub Form_Timer()
msgbox "Hello"
'Dim strCSV As String
   'strCSV = Me.txtExportLocation
    'DoCmd.OutputTo acOutputQuery, "qryExportQuoteJobData", acFormatXLS, strCSV, True
End Sub
0
 

Author Closing Comment

by:SteveL13
ID: 40587079
This ended up working.  I had thought it would export when the form opened at first.  I guess it takes 5 minutes after the form has been opened.  We are not concerned about the exact timing.  As long as its fairly close is ok.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

579 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