auto import and update on startup

[MrFurley]

Hello,



i have already planned my database out but i am not familiar with creating it. Basically, I am creating a database that tracks when and where my employees travel so they can get paid. Im having trouble with updating my database on startup. Everytime I open my database I would like it to automatically:

1. import data from tableA in database_BE (C:/TRAVEL_DATA/database_BE) into a temp_table. (my info is stored in a seperate database for safekeeping) This temp_table contains both the travelID and employeeID fields needed to update from different files.

2. search for and import multiple xls files from (C:/TRAVEL_DATA/INBOX/*.xls) into the the temp_table if the (travelID and SSN) match, if not then create new records in the table. **I have to match both fields because some travelIDs are blank, but everyone on the list has a SSN.

3. move each xls file to a history folder (C:/TRAVEL_DATA/HISTORY) until there are none left in the inbox.

4. add employeeID data to all records that do not already have one... right(LNAME,2)+left(LNAME,2)+right(SSN,4) ...since some employees travel more than once its ok to have multiple employeeIDs on the table.

5. import information from another xls file (C:/EMPLOYEES/list.xls) into the temp_table for all records that have a matching employeeID. (this is a list of employees by ID with additional database info that is not already on any of the travel_data files.) **If one employee travelled twice then a copy of this information should be reflected for each TravelID in the database.

6. delete any records from temp_table if the employeeID is not contained in the employee list (C:/EMPLOYEES/list.xls)

7. At this point i have no problems manipluating the data through switchboards, forms and reports. However,

8. when i quit i would like it to automatically update tableA in database_BE (C:/TRAVEL_DATA/database_BE) and then delete temp_table in the database.

does anyone have any suggestions, guidance or pointers they could share with me?? i appreciate any assistance to get me started!

[weekend00]

setup startup form to perform all these your planned work.
In Access 2003, the setup is in Tools -> startup, no idea about Access 2007

[RuralGuy]

To add to Weekend's suggestion, here's how you do that in ac2007: http://www.btabdevelopment.com/ts/de...aspx?PageId=96