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 Replies
ADVERTISEMENT
Nov 8, 2005
I was working on project that involved writing the data into Access database using a C program. The insert query execution was taking very long, so I decided to write to a .csv file and then import it to one of the desired tables. This worked very fast as compared to directly writing to the DB. Now I want to automate this process. The user should specify the file name at the command prompt and I want to call a script so that the script automatically imports the contents of the .csv file into the access DB. I already have connection established to the DB. All I am looking for is the script that can automatically import the .csv file into access DB. Please help me out. :confused:
View 2 Replies
View Related
Nov 1, 2012
I have a database for when our people are out in the field. The people will fill out large questionnaires that I want to import into our main database. How can I automate this?
View 10 Replies
View Related
Sep 7, 2013
I've been asked importing about 50 - 60 .DBF files into an Access database daily - and processing the data in Access to specs I don't have yet.
I'm pretty comfortable doing whatever once I have the data in the Access Database.
View 8 Replies
View Related
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
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
Sep 18, 2013
I have requirement to automate the process to load .CSV files onto MS Access on daily bases. I am new to MS Access.
Note: No other technologies are used like JAva, C#, etc.
View 1 Replies
View Related
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
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 1 Replies
View Related
Jul 7, 2005
hello once again,
I need to import a text file into an existing table in Access. The text file has been imported once and is working well and everything. However, since I had to change some of the datatypes to be able to query the table correctly, I now cannot import the text file anymore unless I change the datatype of the table itself. Since someone other than I will be doing the imports from here on out, changing the datatypes everytime is out of the question. I was looking at the TransferText event, but I didn't seem to see anything about converting data types. I can think of two options, and neither are probably possible:
1. import using the wizard. Since I didn't see anything related to the types of data, I don't think this will work...
2.import using TransferText. This doesn't seem to give me any opportunity to change the datatypes either. Is there anyway to programmatically change datatypes, or is there possibly an easier way that I'm overlooking??
thanks in advance,
*j
View 4 Replies
View Related
Jun 27, 2007
I am trying to automate a report to run at night. I have created a batch file that will run a macro each night. But, when Access starts and loads my database I get a Security Warning [ This file may not be safe if it contains code that was intended to harm your computer]
I have tried to use a Sendkeys command to send an 'O' to go ahead and open the database but it doesnt work.
Also, my report goes to a pdf converter and it asks for a file name. I just want the macro to accept the default and continue.
How do I do this?
Thanks in advance
KD
View 1 Replies
View Related
Aug 24, 2006
Hi all,
Access 2000
Wondering if there is anyway to setup a report to automatically run and e-mail out at a certain time each day? I currently have a button on a form, based on a macro, that when clicked, will e-mail the report to a certain user. Just wondering if there is anyway to set it to send at a certain time, without having to open the database and manually run it.
Thanks in advance.
J
View 1 Replies
View Related
Sep 24, 2004
I need to generate a report with records generated on a daily basis and I am looking how to write the query expression. For example, my Access database generates record on a daily basis and I an using a query to generate the report. I only want to report the records created on that day, and then each subsequent day. So, in the query, I am looking for the correct expression to grab only those records generated that day. (Hope its not confusing, I'm new at this)
Thanks for any help in advance!
Keith
View 6 Replies
View Related
Aug 11, 2015
I have a kids club which do lots of functions for kids, to monitor the members and who is in and out I did a small access data base with details of Start date of the function and end date for each member.
I want to make a daily report showing only the active members in today date only for example:
Jack have swimming every Saturday, Monday and Wednesday Between 14 July 2015 till 14 August 2015
Jill have swimming every Sunday, Tuesday and Thursday Between 14 July 2015 till 14 August 2015
today is 11 August 2015 and I want to report to mention just who is active today only.
View 3 Replies
View Related
Aug 11, 2015
I have a kids club which do lots of functions for kids, to monitor the members and who is in and out I did a small access data base with details of Start date of the function and end date for each member.
I want to make a daily report showing only the active members in today date only for example:
Jack have swimming every Saturday, Monday and Wednesday Between 14 July 2015 till 14 August 2015
Jill have swimming every Sunday, Tuesday and Thursday Between 14 July 2015 till 14 August 2015
today is 11 August 2015 and I want to report to mention just who is active today only.
View 8 Replies
View Related
Oct 17, 2011
I am working on Access2007. I designed a report that I need to email every night automatically to my client. The report resides in the Access database. I currently use a macro to auto email this report for me but it still requires me to double click the macro to make it run. I actually want the report to be email at night without having me running the macro manually. Is there any way to make this happen?
View 7 Replies
View Related
Aug 19, 2013
I'm in the process of importing an excel spread sheet from an external source (SharePoint).
I have the link for the file and have edited it from the Https:// to projectspace. intranet.sharpoint etc etc.
The problem I'm having is before importing the data into a new table in my current db I get an error
"The File "projectspace.intranetetc etc" does not exist.
I am using IE8 and the version of Access is 2007.
View 3 Replies
View Related
Dec 6, 2012
Having experimented with Access for a few months now; I know you can link an Access Database to SharePoint, and you can export Excel files into Access for inclusion in tables.
My question is; If I have a daily report being produced, through script, with an excel output. Is there anyway to automatically publish this in Access, negating the need to manually go into Access to import?
View 5 Replies
View Related
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
Aug 11, 2006
Hi
I have o form based on a query that I can search in for last name etc.
And then I have a button for opening a report for the person that displays.
Here is the problem now:
If I have two persons with the same last name I get a report of several pages also showing the person that is not displayed in the form but have the same last name.
What can I do in the query just to get the person only on display showing on the form?
ID is the primary key
Mikael
View 1 Replies
View Related
Sep 9, 2004
hey
i really hope someone could help me with this, i have a query (Exp Query) which calls from 2 tables Expenditure and VAT.
My VAT table lists all the VAT Months and their relative Quarter Number, VATMonth is the primary key but because it contained other text, i had to make it a text value rather than format it as a date. so the problem is when i make a report, it sorts the VATMonth in alphabetical order, eg June 2004,July 2004, May 2004.
my VAT table has an ID field which is an autonumber, so i use that to order the list in things like my drop down box on a form. so how can i do the same thing in my report.
the report consists of 3 months which i need to SELECT VATMonth FROM Exp Query and ORDER BY VAT.ID (also from Exp Query) can i do this and if so could someone tell me how it would be written, i just keep getting syntax errors.
thanks for any help,
Baz
View 4 Replies
View Related
Sep 28, 2004
I am trying to get Access to print out a field that is city, state, zip except when no city is set.
My control source criteria is =IIf([City] Is Null," ",([City] & ", CA " & [Zip Code])) I've tried IsNull([City]) instead of [City] Is Null to no avail. All I get in the box is #error. I can get just [City] or just [Zip Code] to work but even if I just put in =[City] & ", CA " & [Zip Code] I still get #error.
Please help! I just got my hair cut so I don't have that much to pull out.
View 3 Replies
View Related
Jul 7, 2005
I have a form with three combo boxes and am looking to open a report based on the choices they make. After each combo box is selected a query is run that makes a table based on their choice. I want to be able to use the appropriate table, without having to set it each time. The data displayed is going to be the same on the report just different data. Any help would be greatly appreciated.
Thanks
Bobby
View 5 Replies
View Related
Sep 10, 2004
I have a report with subreports contained in it. Each subreport gets it's data from a different table.
I noticed subreport 1,2,3,4 are pulling the data from their corresponding tables. However, subreport 5,6, and 7 are pulling their data from the table relating to subreport 1.
I've checked the subreports themselves. They are referencing their own tables if I open them individually but not when opening the main report.
The report names all seem to be correct.
I've never seen anything like this before. Does anyone have some ideas??
Thanks.
View 2 Replies
View Related
Oct 5, 2004
Hi,
I am new to Access and am learning the ropes.
I have constructed a basic database for our Church Craft Fair.
It consists of two tables, one for suppliers the other for inventory.
The tables are linked with a one to many rule and each has a key field
of Supplier ID. I have a form for entering Supplier details and another
for entering Inventory which has a subform. It all seems OK.
I have used the wizards and then customised to suit.
The problem I have is that I need to have a report of items sold with their value.
I have done this with the wizard generating the report from the inventory table.
However I also need to include fields from the Customer table, such as supplier name
and tax details. I have tried to include these fields by entering the control source
from the Customer table using the expresion builder[customers]![SupplierName] but this
just generates an error on the form #Name?. I have also tried launching the report from
a form based on the customer table and using the customer data on the form as the control source.
This does not give errors, but the data is incorrect and only relates to the first customer record,
ie on the inventory list for each customer, every customer has the first customers name.
Can anyone help me with this?
I have spent hours trying to work out how to do this correctly.
Cheers
Cotech.
View 2 Replies
View Related
Jan 14, 2005
OK. I have a report that I want to use as a master and use with about 4 different querys. In the report properties I've bound it to a query. I've tried for ages to change the record sources with on click command bottons on another form. I think things have become complicated because there's a subreport on the report I AND a there's bunch of code to make things invisible in the on page event of the report.
anyway, here's what I'm using:
DoCmd.OpenReport "rpt_master", acViewPreview
Me.RecordSource = "qry_rptPrintRollClass"
Is this the right way to do it??
Any ideas on how I can clean it up??
Thanks
Damon
View 2 Replies
View Related
