Solved

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

Posted on 2015-02-02
9
93 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 50

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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