Question Planning to Import/Export/Linking

[Skip Bisconer]

I am in the planning process on how to deal with this on going situation. We have a complete download from Amex covering all the current monthly cardholder credit card transactions.

I want to create a monthly statement for each cardholder to approve each line of charge, assign the business puprose from a predetermined list and if customer related add customers name in the appropriate field. The employee will then feed the spread sheet back to the administator who will import back to Access and add it to a history file.

I have no problems with setting up the tables and queries and even the code for this should be simple. As this will happen once every month I i'm having a "lack of ideas" problem in thinking of the best way to automate the back and forth with Excel files. Could someone share their experiences with me?

 

[georgedwilkinson]

Is there a reason the approvers can't just approve it in Access? Then you wouldn't need to send .xls around.

 

[Skip Bisconer]

George,

Yes. The problem is the field employees all have laptops with office installed but no Access.

 

[georgedwilkinson]

That makes sense.

I've been a heavy user of the TransferSpreadsheet method. I have an automatic process that tracks each file I import/export (in a table). When the file has been imported, I run insert queries on the various tables where the data goes and then a delete query on the import table. I tie the incoming data to the file it came from.

The process is similar for outgoing.

Also, I've made heavy use of Excel automation (q.v.) to make the outgoing spreadsheets "pretty".

HTH.

 

[Skip Bisconer]

I was thinking possibly I could use the same outgoing and incoming XL file and set up a linked table to the XL file then the admin people could just look in the linked file to see if the employee updated the file. Then just run an append query to the history file when each record is complete.
Thing I don't know is if I export a query to a specific file path does it overwrite the existing file? I think it would need happen that way to make what I am thinking work. I have mostly worked with XL using VBA so I can program the incoming worksheets back to the linked file.
When I worked a large company I did a similar thing in Access 95 but I only had to send out about 50 spreadsheets because the regional managers had to make the approvals and expense notations. But here I only have to send out 30 worksheets and it would work mostly in the background. It's just been too long and now I am in my late 60's so my memory isn't as long as it used to be. I really appreciate your input.