Access - Automatical retrieve files

[eric.kung]

Dear MS Access super users.



I have another question regarding the validation data project that I am currently working on.


I need to validate two CSV files everyday.
However I have a concern right now, since I receive 2 CSV files everyday and I will be using MS Access to compare the two files everyday.

Is it possible for MS Access to automatically retrieve these files and allow user to compare the files?


If it is possible how do I do it?
Please advise!

[NoellaG]

Hi Eric,

using VBA you can program about anything you want...

gr
NG

[rpeare]

let me add, that if you are having a user 'compare' the two files, what are you comparing? If it's a logical decision (if you have rules) you can program those comparisons as well and only show the results you want rather than having someone visually look at the data which can be inaccurate when compared to having the machine do it.

[eric.kung]

Thank you guys.

@rpeare

I would like to setup this rule that you are talking about.
It is to compare the field "File_Name".
The two tables are said to have the same records but sometime inaccuracy is found within the two records and by human mistake.

Do you have some examples or some tutorials which I can read to do this?

I have been searching on the web for sometime now (approx 2 weeks) and I am unable to find anything near to what I need.

I can only find some learning visual basic lesson 1, and so on...

[rpeare]

So you're trying to compare file names that have been manually data entered?

so let's say you had File_Name

aaaaaaab.txt

in one table and

aaaaaaaab.txt

in another table

these may actually be the same file but someone has mistyped the name?

That type of comparison is extremely complex, it's akin to trying to find duplicates in addresses (for instance str, street, st., st all being the same thing in an address but in text they are not)

What you can do, however, is use filesystemobject to see if a file exists and if it does not you can flag it in your database. You can also use filesystemobject to gather the names of all the files in a particular directory or directory structure and add those records to your database so that there is no human intervention required and the file names will always be correct unless someone changes the name of the file itself.

I don't know the scope of your project but flagging the files that are NOT found might be the quickest way to get what you're after, then you could look for a similar name on your secondary table which might speed things up a bit.

[eric.kung]

So you're trying to compare file names that have been manually data entered?

so let's say you had File_Name

aaaaaaab.txt

in one table and

aaaaaaaab.txt

in another table

these may actually be the same file but someone has mistyped the name?

That type of comparison is extremely complex, it's akin to trying to find duplicates in addresses (for instance str, street, st., st all being the same thing in an address but in text they are not)

What you can do, however, is use filesystemobject to see if a file exists and if it does not you can flag it in your database. You can also use filesystemobject to gather the names of all the files in a particular directory or directory structure and add those records to your database so that there is no human intervention required and the file names will always be correct unless someone changes the name of the file itself.

I don't know the scope of your project but flagging the files that are NOT found might be the quickest way to get what you're after, then you could look for a similar name on your secondary table which might speed things up a bit.

Thank you rpeare

Indeed.
Both tables:
Table A and B have the same field.

File_name and Transmission_Time.

My job is to develop a program to compare the two.

Table A is the master, it contains EVERY FILES.
It is sending files daily to the FTP to create files for Table B.

Table B however, is being created IF Table A's files have been transferred to a FTP server successfully.
The program in the FTP will then generate a CSV file, this CSV file should then AUTOMATICALLY import (*) the CSV file into Table B.


And the comparison will then begin and I will totally see if I can (my knowledge to access and VBA is VERY LITTLE) allow the file to give a "failed flag" and a "success flag".
Success flag is available then this file_name SHOULD NOT BE CHECKED ANYMORE.
If failed flag is available, redo the checking one day after the transmission date (is this possible?).
If failed twice, it will then give 2nd failed flag and when 2nd failed flag appears, when I generate report I can see it (or send me an alert?)

My requirements from above is possible?
Am I thinking magic here?

[rpeare]

I run a very similar process where I work, but most of the process is run through a powershell script, but our information is kept on a single table. So we have incoming files and a field for upload date, the upload date is only updated when the file is successfully uploaded. Is there a reason you are basically maintaining the exact same information on two tables instead of just having an indicator field on the 'received' items table?

You could write a vb script that will look at your CSV that's generated when you upload files and have it update the table A data as long as the file name and file date make up a unique record.

[eric.kung]

Ok... thank you rpeare I will try to look into that and see what I can find.



There are two tables are stalled in a folder.
And I would like to either click a button or for MS access to automatically import these CSV files into my tables.
Is this possible?

I am about to develop a form for my database.
This form will display the file_name and the transmission_time of each of the tables.
Beneath them all are two validation modules, one for valid and the other for not valid.

Is it possible to develop a command button and have the command button to help me to import the CSV files from these two folders? If yes, how do I define it using visual basic?

Afterwards another button for the validation comparison.
Does this sound possible? Is it extremely complex? If so how difficult it is?
How long do you think a fresher (Very little VBA experience) can handle?
Please help, thank you!

[rpeare]

So you're starting with two text files (.csv) files. Are the .csv files cumulative (do they contain the entire history of your uploads) or do they contain periodic (files through a certain time frame) information. In either case yes, you can import them automatically. Assuming you never have duplicate file names you can write a script that will import all data from all files in a certain directory or with a certain naming convention etc. And further, you can have your process look at the files in your File B (the one that contains upload information) and update the appropriate record in a single table as opposed to 2.

You will likely want to look at filesystemobject code. filesystemobject programming will allow you to retreive file names, test paths, test file names, look at contents of a file, etc.

The other thing you'll probably want to look at is transferspreadsheet or transfertext procedures to import the csv's. Assuming, again, that you only have a file name/file date combination 1 time.