My requirement is there are multiple copies of same excel sheet with multiple users who update a table in the excel sheet.Then there is an access database where one of the tables get updated from the excel table updated by multiple users and also the change done by any one of the users in their excel sheet should also reflect in all the other users excel sheet. The users don't have access to the access database. I am working in 2007 version.
What I need to do is
1.connect all the excel copies to the access database.So if there's any change done by any of the user it gets updated in the access database.
2.Now the change that has been done by one of the user in the excel sheet should also reflect in the excel sheet of all the other users.
I tried solving this by linking the excel files to the access database which created many linked tables in access. So, this way the change in one of the excel file was reflecting in its respective linked table created in access.Then I joined all the linked tables to one table(say joined table) by using union query. Then I connected this joined table to the multiple excel sheet.When the user opens or refreshes the excel sheet, the table in the excel sheet gets updated.
But the problem that I am facing now is when there's some change done in one of the records in excel file instead of overwriting that record in the joined table it creating another record.So, now there are two records one record without the updated data and another with the updated data in the joined table and these two records are reflecting back in the table excel sheet as well. So say if I have 50 records in each of the linked file then there are 51 records getting created in the joined table in access database as well as in the excel table.
Can you please help me solve this as I have tried lot but I am unable to solve this.
