Query/Import question

[theKruser]

I have a tbl (tblDATA_TRNG) with the following fields:

TRNGDATAID (PK)
TRNGID (FK)
DATE
SCORE
CODE
REMARKS

My situation: the data entry team will receive reports (Excel format) from supervisors which they will have to enter. The easiest way I can see is to have a tbl (tblIMPORT_TRNG) for the team to import directly into. They would then notify an administrator who would run an append query to append to tblDATA_TRNG.

The problem (as I see it): admins (currently just me) will be receiving numerous notifications per day with updates. I would like to find a way to automatically clear all data from tblIMPORT_TRNG upon completion of the append query to remove the possibility of appending duplicated data.

End state (as I would like it, anyway): trying to make this idiot-proof (yeah, I know...dream on dreamer!!) to where a member of the data entry team has only to hit a button on the swithboard, select the file to import and click the "do-it-to-it" button. The "do-it-to-it" button should import the data from the excel file to the appropriate tbl while simultaneuosly launching an e-mail to the admins with notification of data awating action. Once the data has been verified accurate, the admins should have to click a button that will run the append query then erase the contents of tblDATA_IMPORT.

I realize I might be asking for more than what is feasible. I have this sneaky feeling that the answer lies in VBA coding which I know nothing about. Any help would be greatly appreciated.

 

[DCrake]

There are two ways of tackling this problem. The first solution is to have your code in Excel using the Personal.xls file and assign a macro to Export the data into Access. You could get it to check Access to see if it has already exported the said spreadsheet, if not it exports and you could also get it to send an email from excel to the user that a file has been exported.

The alternative way is to get the user to select the file to import and again check to see if it has been imported.

My only concern is that you are suggesting a temp table for the imports whwn inturn will be appended to the main table. I suggest the query that runs the append from the temp table to the main table should be followed by a delete query that deletes from the temp table the same records it has just appended to the main table.

When checking for existance of previous imports/exports you will need to check first in the holding table for sheets that have been imported/exported but not posted to the live table, and check the live table for imports/exports that have been posted to the live table.

This is just a started for you.


CodeMaster:

 

[theKruser]

I think the delete query idea is best. My next question would then be, is there a way to create a command button that will initiate the file import. It should open a file selection window (standard open file box) where the proper file can be chosen, then automatically import data into a predetermined table. Thanks for the help!