Can I use MS Excel to read & Edit MS Access in Realtime. Just like using Excel / Access but should update the Access table while Excel cell is modified?
You can do this but it is going to take custom coding and a considerable amount of time and knowledge to make it play right using DAO or ADO connectivity. Then you still can not be assured this will solve the corruption problems your seeing with the Access interface.
If you only need to display database information in an Excel Spreadsheet that is not as difficult or complicated. Use the Data Menu 'Data->Get External Data->New Database Query...' This will open a wizard to configure the data query and allow you to view it in MS Query or go directly to an Excel Worksheet. You can set up the options to refresh on open and save the query with the workbook so it does not need to be reconfigured once it has been setup.
You should check your database configuration and try to optimize it to reduce the corruption problems. Consider splitting your database into a front-end Back-end to separate the data from the user interface.
Consider splitting your database into a front-end Back-end to separate the data from the user interface.
I'd recommend this method after I had a similar problem to yours (regular database corruption). Splitting it into one single backend with a generic frontenf deployed to each user solved the issues. It also allowed me to add an audit trail, allowing me to answer the "who did that" questions that frequently pop up!
Like savebill & mrbump advised, I tried splitting the table with one common field to link the two. However When I run the query, am not able to update the fields (displays Field not updatable) - How do I achieve this along with Audit trail (Could you let me know me the procedure and/or the sql sample statement (if sql involved)