Forum Discussion
Dynamic daily data import to MS Access
Hi,
Hope that this is the right place to ask this question.
I'm trying to pull data reports from a local health source on a daily basis. The only option for exporting the report file is MS Access. Data in the source have new observations (rows) that should be appended to the destination. I then export the report from Access to excel to merge data from all reports in excel (different reports for different diseases) diseases. My goal is to automate the data appending in Access and the data export to excel. I tried to save the new pulled data to Access with the same name as the previous file. This worked for me in the beginning, however, the file lost the new data after opening the file and or/exporting it to excel. In fact, it returned back to the original data list and it did not update in excel as well. Also excel updated the data after refreshing the file in the first trial but did not work afterward. I'm really stuck and any advice will be really appreciated.
Than you.
3 Replies
I am not sure what this line means, it implies that you are actually attempting to save a new import as a table with the same name as an existing table. What that does is create a duplicate of that table with an incrementing suffix, i.e. "tablename", "tablename1", "tablename2" and so on. If that is, indeed, what is happening, that explains why you only see the original data in the original table; the rest of it is in one of those duplicates.
If that is NOT what is happening, we need more context.
Hi George,
I'm not that familiar with how Access work. The daily data that I pull from a secure website export it to MS Access. A total of 10 files (6 tables each) are to be exported daily then cleaned, deduplicated, and merged into one dataset (I'm checking if the cleaning, deduplication, and merging can be done in Access, instead of exporting into SAS or excel). In summary, I'm thinking of ways to streamline this whole daily process. What I tried to do is save the new incoming files with the same name as the first imported file thinking that the new file will save and overwrite the previous one. I'm not sure if this is a suitable way in Access.
Thank you- Operations of this kind are commonly referred to as "ETL", or "Extract, Transform and Load". The typical tool would often have been csv files. What kind of files are you getting? I'm having a hard time visualizing a file of 6 tables, by the way. Are they combined in the file somehow?
Start with the step "...export to MS Access." Tell us as much as you can about that, what the file type is, and so on.
Also, is this website under your organization's control? Can you connect directly to the database behind it?
