General :: Scheduled Task To Import Daily CSV Exports?

May 18, 2015

We have small data dumps from a webservice delivered to us daily in csv format. I'd like to create a system where we can have the data automatically imported into either Access.

My idea is this:

1. CSV is downloaded to <x> folder.

2. Scheduled Task runs script to look in folder <x>

3. If CSV is found in folder <x>, import it's data into a fixed Access db->table.

4. Move CSV to an archive folder to avoid re-processing.

Is this feasible? How complicated would it be, and how to get it on track?

View Replies


ADVERTISEMENT

Program Windows Scheduled Task

Aug 23, 2007

Hi All,

Does anyone one if it is possible to program a schedule for "Windows Scheduled Task" via VBA?

Reason:- A multi user application is set to give a 5 Min warning at the same time every day. After the 5min the users are politely kicked off.

At that time the Windows Scheduled task opens up the BE (only on 1 PC the fastest). The BE start form has a time event that backs up the BE and compacts and repairs itself then closes the application.

Not pretty I know but it works really well.

This shutdown time can be set/changed on the FE however, when it is the windows Scheduled Task has to also be changed which I would like to do automatically via VBA

Any help would be appreciated.

View 1 Replies View Related

Modules & VBA :: Scheduled Task On Windows Server

Apr 20, 2015

I have a vbs-script that opens my access database db.mdb and runs the module "Export":

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("D:Datadb.mdb")
accessApp.Run "Export"

accessApp.Quit
set accessApp = nothing

This works fine as a scheduled task on a Windows 7 computer.

Is there a way to make this work on a Windows Server 2008 R2 ? When I double click the vbs-file, the file opens instead of runs. And when I create a scheduled task, nothings happens.

View 2 Replies View Related

Running A Query / Macro Through Scheduled Task?

Aug 25, 2012

I have a MS Access append query that updates a table from a linked Excel spreadsheet. Im trying to automate the running of the query during the night.

Ive tried a couple of ways to schedule the task with Windows Task Scheduler, although each time, it appears to start the job, but then just hang (task never completes, doesnt run the query or macro and when I log back into Windows, the msaccess.exe process is running).

Methods Ive used are:

- command line with /x macroname

- vbs script which runs the query / macro

Code:
'Read from a Microsoft ACCESS database
Option Explicit
dim oaccess
'execute another Access macro
set oaccess = createobject("access.application")
oaccess.opencurrentdatabase "H:Database.mdb"
oaccess.docmd.setwarnings false
oaccess.DoCmd.RunMacro "Macro1"
oaccess.docmd.setwarnings true
oaccess.closecurrentdatabase
oaccess.quit
set oaccess=nothing
WScript.Quit(0)

- .bat file which just has the command line in it.They all run fine when I am logged in, although it seems to hang when I am logged off the network. I have the correct permissions as Ive scheduled a Excel macro to run and its fine.

View 4 Replies View Related

Modules & VBA :: Scheduled Task - Run Macro To Send Report Out?

Feb 3, 2014

I have a report that I run manually from my computer every day.

This report is created by a macro in microsoft access, then it runs a VBA macro to send it out..

Is there a way that I can add this as a scheduled task since I usually forget to send it out?

View 3 Replies View Related

Tables :: Daily Import From Excel - Current Only

Jun 11, 2014

I'm working with an A/P department that is using "legacy technology" and has no capability for maintaining notes on invoices due, etc. I'd like to give them a database that would provide them with the features they need, but I'm running into a conceptual snag.

I am able to extract from their database system all open invoices each day. For each invoice, the A/P person will make notes from day to day on approvals to pay, any problems with the invoice or related inventory or service, discussions with the vendor, etc. So, let's say on Monday they make notes on a particular open invoice. On Tuesday, they'll pull all open invoices from their system and import that set of invoices into the Access database. Some will be new, but many will already be in the table. Using the Vendor Number and Invoice Number together as the table key, I can avoid duplication into the table. But, some already in the database will not appear in the new extract because they've been paid.

So, my question is... how do I get to a point where they can do this import each day and see only the invoices that are currently open AND maintain their previously-entered notes? I thought first that there would be a query to run each day, but I don't know how to keep consistency regarding table names in the query, etc, so the query doesn't have to be edited for each new import. Would this be an append to get the new ones? If so, how do I drop the ones that are not in the newest extracted data?

A complication, of course, is that I want them to be autonomous and able to do this without my input each day.

View 6 Replies View Related

Modules & VBA :: Automating Daily Import Of CSV File

Aug 10, 2013

We receive a daily csv file from another database and we are trying to automate this process as much as possible.

At the moment, the other system will extract any new records, save a file as importddmmyyyy.csv in a specified location e.g. c:importpendingimportddmmyyyy.csv and then make a call to open access giving access the file path as follows

masaccess.exe "C:mydatabase.mdb" /nostartup / Excl/ cmd Filepath="c:importpendingimport09082013.csv"

I have configured access to automatically open a form, "ImportForm" when it opens, and I have configured this form to automatically run an event procedure on loading "importdailyfile".

My problem lies in writing this procedure. Basically I need it to run the Transfer Text command (I have already create an import specification)

DoCmd.TransferText acImportDelim, "myimportspec", "table to append the data to", filepath, true

The problem I am facing is that as the filepath changes on a daily basis given the naming conventions (which i cannot change) and my knowledge of VBA s pretty basic, I don't know how to make the procedure pick up the filepath that the other system is providing in the call to open access.

View 3 Replies View Related

Automate Import Of Daily Report From Another Source

Oct 30, 2014

The multi-user application I'm making uses data from another system. I get reports in csv format e-mailed to me in zip format. Since it's a multi-user system, I have to create a table with the records from the csv I get an error message that someone is viewing the data if I try viewing it while someone is viewing it.

When I get the e-mail with the latest version of the csv, I unzip it and replace the older file in the folder location that the Access database is linked to. I have a sub routine that deletes everything from the table and inserts the records from the latest csv. I execute the sub by pressing a button.

Is there a way to automate unzipping the file, extracting it to the folder and running the sql code in VBA?

View 2 Replies View Related

General :: Fonts Not Embedding Properly In PDF Exports

Jun 21, 2014

I'm using Access 2010's DoCmd.OutputTo in VBA to export reports to PDF. The "look and feel" of the PDFs are very important, as they will be distributed to clients of my company. I'm using special corporate fonts that are legally licensed for embedding as a subset. Two are .ttf (TrueType) and two are .otf (OpenType) fonts. The ttf fonts embed fine; the otf fonts do not, and the PDF viewer substitutes something it thinks is close (but really isn't). In the properties of the fonts in Windows Control Panel, the embedding properties are exactly the same for both.

Any way to force the fonts to embed? or any other workaround? Also, is there any way to edit-protect PDFs with VBA code? Or apply any other type of PDF security such as requiring a password to open?

View 3 Replies View Related

Modules & VBA :: Daily File Import Timestamp Shift

Mar 17, 2014

I have a daily file to import. If I hard code the entire file name my code works. My problem is getting around the daily name change of the file.The owner has an automated process that creates the file so the name every day is slightly different due to the timestamp. Here's an example

Today's file is "mydailyfile_20140317_1035.xls"
Tomorrow will be "mydailyfile_20140318_1033.xls"

I can account for date using "sFileDate = Format(Now(), "yyyymmdd")".The hour/minutes is never the same.is there a wildcard solution??

View 5 Replies View Related

General :: User Defined Function Exports Julian Date

Jan 10, 2013

I have created user defined function for lastdayofweek, lastdayofmonth, and firstdayofmonth. When I apply this function to an expression in my query, it returns a julian date. How do I apply the format for the calendar date?

Maybe I am not calling my function correctly. Here's my functions below as well as my expression..

Function LastDayThisMonth()
LastDayThisMonth = DateSerial(Year(Date), Month(Date) + 1, 0)
End Function

Function FirstDayThisMonth()
FirstDayThisMonth = DateSerial(Year(Date), Month(Date), 1)
End Function

[Code] ....

View 3 Replies View Related

General :: Merging Databases - Importing Saved Imports And Exports

Aug 22, 2013

I'm merging a few databases together. They're fairly simple on their own but I'd like them as one big database. I read that I should import each access database into a new, blank one.

It all works fine, but none of the saved imports and exports come with them. Unfortunately, these imports and exports are relied upon quite heavily.

Is there a way to bring saved imports over from other databases, or will I have to rebuild each one?

View 3 Replies View Related

General :: Task Scheduler To Run Macro?

Mar 19, 2014

Just got a new Windows 7 computer and installed Office 2013. I have a report that saves itself as a pdf in a temp folder and then attaches the pdf to an email and sends it out. I use a macro to run the report and everything works fine from there. I have a VBS file that runs the macro and clicking the vbs file makes everything work fine. the code is below.

When I tried to run it though the task scheduler it show as completing fine, but it doesn't. So I added a reference the vbs in a batch file (below) and output the logs to a temp file. After I ran the batch and opened the log I seen the message

Wed 03/19/2014 14:11:17.74 Username
C:Users*****DesktopAuto Reportsaragingrecent.vbs(1, 1) Microsoft VBScript runtime error: Permission denied: 'CreateObject'
Wed 03/19/2014 14:11:17.77

I turned UAC all the way down. turn off the virus scanner, and have local and domain admin access. I'm at bit a lose here. I had this working before but can't seem to remember how I did it. This is just one example I have several other reports that were schedule to run overnight, but they basically all use the same code and processes.

Code:
'launch macro
set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDatabase "C:Users******DesktopAuto ReportsAgedTrialBalance.mdb"
accessApp.DoCmd.RunMacro "MCRecent"

[Code] ....

View 2 Replies View Related

General :: How To Execute A Task In Background

Feb 2, 2014

Is there a way to run a query, macro, and/or report in the "background". By this I mean in a way that frees up the current operator to do other things in the database while the query, macro, report keep on running?

View 1 Replies View Related

General :: Open Database With Task Scheduler

Sep 17, 2013

I am trying to get windows server 2012 task scheduler to open my access database. When the task runs all that I see happen is the locking file will appear on my desktop and I have to go to the task manager and kill it to make it stop. I tried a .bat file to open it as well and im still getting the same thing. If I just click on the database and open it manually it runs just fine.

View 3 Replies View Related

General :: Large Table Import / Way To Import Tables To Access

Sep 17, 2013

I want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference).
Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?

I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.

View 3 Replies View Related

General :: Database To Record Payments - Multi Task Command Button

Apr 7, 2014

I am creating a small database to record payments.

There are three tables :

Supplier - tblSupplier
Payments - tblPayments
Invoice - tblInvoice

There are two Forms :

frmPayments (Bound to tblPayments) - Main Form for payment entry.
frmInvoice Sub (Bound to tblInvoice) - Sub form to display not paid invoices.

What i need is a Command button , lets named it "Commit", to perform a few tasks :

1. When click, prompt message asking whether to Save current payment record when all relevant input is completed during data entry.

2. If Answer is "Yes", then it will compare the "Invoice No" on the Main Form with the "Invoice No" of the Sub

Form , and if found to match, then put a tick in the "Yes/No" field of Invoice Table (tblInvoice) against the matching "Invoice No" of Main Form. This is to record payments made to this particular Invoice in Invoice Table.

3. Proceed to save current record, Refresh Main Form to be ready for a new data entry.

4.If Answer is "No", discard all current entries in the Main Form, Refresh to be ready for a new data entry.

Sample DB is attached...

View 1 Replies View Related

General :: Automated Task - Send Email Address To All Emails In Query

Sep 30, 2013

I want to automate a task in access....

I have a query that may have 6 - 7 different records for example each record contains a different email address, i need to get the first email then send and email to that address then go to the next record get that email address send an email to that email address until its gone through all the records...

I use this code to send an email, dont know how to loop through records and get the email...

Code:
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim strPath As String
Dim strFileName As String
Set appOutLook = CreateObject("Outlook.Application")

[Code] .....

View 1 Replies View Related

General :: Query For Daily As Well As Cumulative Production For Month

Jan 28, 2013

I have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ). Now I want a daily production query as

date : xx-xx-xxxx
productname | qty | monthlysum(for thsi product) | daily average |

I want this on a single query so that i can make a report out of this .

View 12 Replies View Related

General :: Code For Bringing Daily Data Into Excel

Jun 22, 2015

I am trying to transfer daily data that I get from three different queries all into one Excel sheet. I take it that you have to make one over-arching query which I have made called Awaiting Base.

View 4 Replies View Related

General :: Append And Update Records Daily From One Table To Another

Jan 9, 2014

I need to know the best way to append,

table1=productid,ProductName,Qty to table2=Productid,productName,Qty,date daily

I want to be able to append Records daily from table1 into table2 OnClose. But i do not want duplication of record in the same day.

If changes occurs in table1 after append,table2 should be updated using If conditions

View 1 Replies View Related

General :: Maintain Stock From Daily Input-output Table

Feb 17, 2013

i have a production database in which i have different sections processing on the same product. i have a daily production entry form on which datewise entry is done for each section.

i want to maintain the total stock of each section (sectionB) with productName, input from sectionA , production from sectionB, balance (input-production) . presently i used query for this .. but i need a stock table in which entries should be inserted by an automatic query .. how should i achieve this :

1. by an append query who runs every time to append the quantity when a daily entry is made .??
2. by an update query to replace the quantity with a qty from totals query ?

i.e. what is the best method to maintain inventory from input & output tables ?

View 3 Replies View Related

General :: Append Data From Table1 To Table2 Daily With No Duplication

Jan 6, 2014

I have 2 tables,

table1=productid,ProductName,Qty &
table2=Productid,productName,Qty,currentDate.

I want to be able to append data programatically once daily OnClose.Although users can log-off & on as many times,but the Append should be once & after then,update subsequent records for that day automatically from table1-table2.

View 2 Replies View Related

General :: Calendar Template - Track And Update Orders Daily

Mar 24, 2013

Does access have any calendar templates to track and update orders from day to day?

View 3 Replies View Related

General :: Tracking Visitors - How To Enter And Retrieve Information On Daily Basis

Aug 12, 2012

I have visitors who come in from one to several days at a time through different times of the year. Usually someone visits each day. Currently I use Excel to track visitors but I often keep typing and retyping the same persons over and over.

I would like to create a database of these people but I'm stuck with how to enter and retrieve the information on a daily basis. Should I create a table with many date fields (up to several); or just two; one FROM and one TO and try to draw the information out through a query? If so, how would one ask ACCESS with a query to PRINT TODAYS LIST and another VIEW TODAYS LIST (of visitors) with a button on a form (I know how to create buttons).

View 1 Replies View Related

General :: Report That Show Daily Activities - Adding Time Lengths

Feb 25, 2014

I have a report that shows the daily activities. it shows how long it takes to do each task. however some tasks are 8 hours andsome are 10 hours. when all these times are added together it will give the time on a clock. not a total of hours spent.

i.e.
treatment1 : 8 hours
treatment2: 10 hours

this will give 06:00 in short time. in medium time it will give 18:00.

however if I add another 12 hours to that it will give 06:00.

I want it to show either 1 day and 4 hours /or 30 hours.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved