?
Solved

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

Posted on 2015-02-02
9
Medium Priority
?
99 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 51

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 38

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

762 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