Solved

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

Posted on 2015-02-02
9
88 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 49

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 36

Accepted Solution

by:
PatHartman earned 500 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

828 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