DBinPhilly
11-22-2008, 05:18 AM
I have a problem with a program that I wrote 5 years ago. It is an MS Access database that accepts time information from plant employees and does analysis of that time information in order to charge other divisions of the corporation for allocated time on various projects.
The last stage of the analysis is passing the current month's data to an Excel spreadsheet that then does its own analysis. (A management type who knows Excel but not Access wanted to come up with his own final reports).
No problem for 5 or 6 years, then:
They needed to change the reporting structure to eliminate one type of project and combine two others. This required a few hour's rewrite on both ends (MS Access and MS Excel) to make it work. It works on both ends.
The Problem: Access no longer speaks to Excel.
I saved the old spreadsheet and created a new one from it, then made my revisions. I gave the spreadsheet full permissioning for all users.
But: the workbook that I linked to the MS Access database is locked in Access --- it is totally open in Excel mode. I cannot even go into the table section of Access and change data on the linked spreadsheet. So it naturally will not allow my queries that actually update the data every month to work.
I have gone back to the original spreadsheet and that one is locked too!
Any ideas would be greatly appreciated.
Oh, the MS Access front end is on the user's C drive. The MS Access back end is on a network drive. The MS Excel spreadsheet is on the user's C Drive.
The last stage of the analysis is passing the current month's data to an Excel spreadsheet that then does its own analysis. (A management type who knows Excel but not Access wanted to come up with his own final reports).
No problem for 5 or 6 years, then:
They needed to change the reporting structure to eliminate one type of project and combine two others. This required a few hour's rewrite on both ends (MS Access and MS Excel) to make it work. It works on both ends.
The Problem: Access no longer speaks to Excel.
I saved the old spreadsheet and created a new one from it, then made my revisions. I gave the spreadsheet full permissioning for all users.
But: the workbook that I linked to the MS Access database is locked in Access --- it is totally open in Excel mode. I cannot even go into the table section of Access and change data on the linked spreadsheet. So it naturally will not allow my queries that actually update the data every month to work.
I have gone back to the original spreadsheet and that one is locked too!
Any ideas would be greatly appreciated.
Oh, the MS Access front end is on the user's C drive. The MS Access back end is on a network drive. The MS Excel spreadsheet is on the user's C Drive.