Access to Excel and back...

[north19701]

I am designing a collection module for a staff of 8 employees. How it is set up is all open outstanding accounts are housed in an access database. What I need to do is have 8 separate excel files that would query the database for open accounts based on the employees initials. However, I need for any updates made in the excel file to be updated in the master access table as well. Any thoughts on how to begin this?? thanks.

 

[mdmilner]

It is possible to do as you request - your queries would pull data, and then you'd have to check the xls equivalent of 'dirty', I think, to determine if changes were made and then push the changes up to the Access database and then have it actually change the entries via code.

How large are these lists of open accounts?

Would it be a reasonable alternative, instead, to keep the active data in the Spreadsheets and then link the Access database to each Spreadsheet? Linking is found under the File-Import menu in Access. Changes in the spreadsheet are immediately reflected in Access.

Once they're linked, you can extract the data and append it to an Access only table as a 'backup' - lot of options here.

Mike

 

[north19701]

I'd much rather have one table in Access that divides out the accounts. This is because I add accounts all the time, so this would automatically distribute out to the appropriate staff member. Also, there are other criteria I could filter with, such as accounts that were paid. In addition, I want to do a lot of reporting of this, and having just one table where all updates are (real time) would be optimal.

 

[north19701]

also, there are approx 8,000 rows of data in the excel spreadsheets combined.