Solved

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

Posted on 2015-02-02
9
79 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
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
Pat,

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

--Steve
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:SteveL13
Comment Utility
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
Comment Utility
Not sure, but if you closed the form, you reset the Timer
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
...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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now