Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    4

    Cool Unanswered: Use MS Excel to Realtime Edit MS Access DBTable

    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?

    I need to use this on LAN, for multiple users to use the same Access database/table. How do I do this? Please help!!!

    ---: The Reason to do this is, at present, the queries/working is very slow for multiple users using the same database/table & keeps getting corrupted every 15 to 20 minutes...and need to repair :----

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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.
    ~

    Bill

  3. #3
    Join Date
    May 2006
    Posts
    4

    MS Access Optimisation

    Thanks Bill ,

    You almost saved me with your reply. I had been thinking there was a easier way, but thanks for letting me know there isn't one.

    However, what are the parameters I need to check or uncheck to optimize the ACCESS database to reduce corruptions?

  4. #4
    Join Date
    Dec 2005
    Posts
    19
    Quote Originally Posted by savbill
    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!

  5. #5
    Join Date
    May 2006
    Posts
    4
    Dear mrbump

    Audit Trail sounds very interesting. Precisely what we want. How do I start with Audit Trails. Do I need to take care of anything specific?

    Thanks

  6. #6
    Join Date
    May 2006
    Posts
    4
    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)

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •