Duplicate file input or a better idea

[Accessosaurusrex]

I have three devices which will upload data to a single CSV file each day. I have a database (Access 2003) to track and sort the data once it performs a transfer text macro from the CSV file to the database main table. I would like to purge the original data automatically from the CSV file after Access reads it if that is possible. The devices only allow me to append or overwrite the data in the CSV file. I am looking for any suggestions to overcome this. Is there a function in Access that allows me to purge the file when Access reads it or can I create a function elsewhere to do this?

Additionally, there is a small potential for duplicate input on the devices. I would like to prevent exact record duplication entry, but again, the data source is being read from a CSV file. Is there a way to have Access read the CSV file and either prevent the duplicate entries from being entered to the main table or placing the duplicate entries in an error table?

Thanks for your help in advance. I am just setting this system up so if you have a better idea how to accomplish this, I am all ears (or eyes).

 

[Jeanette Cunningham]

If you have the option to overwrite the data in the CSV's - this would be the easiest way to put new data into the same CSV each time without writing any code.

To prevent duplicates - easiest way is to look at the access table that stores the imported info from the csv. See if you can create a unique index using one of more fields in that table.

If you have a unique index, you can run append queries to that table and only non-duplicates will get appended to that table.

 

[Accessosaurusrex]

I should have clarified, my apologies. If allow the file to be overwritten, the three units will overwrite each others data in the CSV file. They all sync at different times throughout the day and do it multiple times. For example if unit 1 syncs at 9am and then unit 2 at 11am, unit 2's data will overwrite unit one's and so on. I have them on append now to prevent that issue. The software that syncs with them is the controller of sync or overwrite so I cannot change the function per each unit. The same function will repeat for all three.

With regard to the unique index item, I had only intended to use the single table to gather the data. This would allow me to develop trends and analysis from it very quickly. My intent was to purge the data annually to prevent the table from becoming overly cumbersome. What you are suggesting is that I create another table for us to use and append the data after importing it to another table that would be used for the reports etc.?

That might actually work better and I should have thought of that the first time around. I will go down that path and see how it works.

 

[Jeanette Cunningham]

My other suggestion is to use Access and vba to make a copy of the csv file after it has been updated and before it is overwritten.
You can use FileCopy - look in vba help for FileCopy.

 

[Accessosaurusrex]

I have everything pretty much in order less one final item. Hate to admit it but I am out of my experience zone here but this is what I have:

I have a macro called IIM that reads the .CSV file and imports it to the table. I have tried to use a VBS for this but have been unsuccessful. I think I am close but am not sure what is missing. This is my script:

Set objaccess = createobject("Access.application")
objaccess.opencurrentdatabase ("C:\Test\Test.mdb")
objaccess.run "YourMacroName"
objaccess.CloseCurrentDatabase
set objaccess=nothing

Unfortunately I get the following error message when I run it:

Line 3
Char 1
Error Microsoft Office Access can't find the procedure 'IIM.'
Code 800A09D5
Source (null)

What did I do incorrectly?

Thanks again!

 

[Accessosaurusrex]

I have resolved the problem. I used a batch file to open the database, run the internal macro and then close the database.

"C:\Program Files\Microsoft Office\OFFICE12\MSACCESS.EXE" c:\WR1.mdb /x IIM

Thanks for the help.

 

[marko46]

Duplicate files are harmful for PC. Examples of duplicate files are Viruses, hidden files, unnecessary files, exe files etc. Most of the duplicate files remain hidden in the folder which cannot be seen easily. This file stops PC to operate properly. DuplicateFilesDeleter has upgraded MD5 search engine. It searches the duplicate files both by the name and content. So, it is possible with Duplicate Files Deleter to identify the identical files that are of the same content saved with a different name.

 

[marko46]

DuplicateFileDeleter is such application that provides the facility to keep the PC in a top shape by dealing with the identical files, sorting out them and arranging the files. Following are some important features of this useful software
• It is a powerful application that works on permanent and secondary storage devices.
• It is user friendly and designed to suit the different types of operating platforms.
• It can be able to search through the hidden folders.
• Converting the scan reports to HTML and CSV styles.
• Retaining and wiping the desired duplicate and wastage file.
• It is multi-purpose software.