none
Accessing a DB table from different process RRS feed

  • Question

  • I have two different background process.

    The one is read/write record from/to the table A and another process read/write from/to the same table.

    The DB is MS Access 2012.

    Is it safe to access the DB table from two different process?

    Should I do extra work to do this job from code?

    Tuesday, February 19, 2019 9:48 PM

Answers

  • Your options are best determined by how many people will potentially access the same record for updating at the same time which if this is the case can get ugly and the same goes for when not accessing the same record but when something changes in a related table. If this is the only option you should do load test which is generally best done with a scripting tool that is throttled to do slightly more work then expected.

    Depending on how important the database is to your organization the best option is to use a enterprise level database such as Microsoft SQL-Server which out of the box is better at handling transactions in regards to writing to the database tables. 

    No matter which direction taken wrap your write operations in a transaction which is fairly easy using a managed data provider such as OleDb on the connection. A transaction is connected to the connection object via cn.BeginTransaction (where cn is the connection), perform the write operations in a try/catch with a Commit after the operation and in the catch a RollBack on failure which can be against one or more write operations.

    Bottom line, you are risking failure with MS-Access, especially with an older version of MS-Access.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Jeff0803 Wednesday, February 20, 2019 2:21 PM
    Wednesday, February 20, 2019 11:22 AM
    Moderator

All replies

  • I was using MS Access for an Office application and had about 15 people accessing the database simultaneously from Excel and Word.  I also had an in-house website that could read and write to it.  Multiple tables are accessed simultaneously.  In 8 years I've  had 2 corruptions that were easily fixed.  I haven't been able to identify the exact problem but I think one was lost power and one was an intranet glitch.  I wanted to move SQL Server but it would be a months long project.  I did some load testing and performance testing and was surprised to find similar performance.  The database is under a gig.  I also have the database running on a dedicated machine.  That really helped with stability.
    Tuesday, February 19, 2019 11:10 PM
  • I was using MS Access for an Office application and had about 15 people accessing the database simultaneously from Excel and Word.  I also had an in-house website that could read and write to it.  Multiple tables are accessed simultaneously.  In 8 years I've  had 2 corruptions that were easily fixed.  I haven't been able to identify the exact problem but I think one was lost power and one was an intranet glitch.  I wanted to move SQL Server but it would be a months long project.  I did some load testing and performance testing and was surprised to find similar performance.  The database is under a gig.  I also have the database running on a dedicated machine.  That really helped with stability.

    MS Access was a pure nightmare with corruption in a Web application. I wouldn't touch Access with a 10 foot pole. 
    Tuesday, February 19, 2019 11:33 PM
  • Since it was small business internal website with limited usage it seemed to work ok.  I would never use it for a serious business website. 
    Wednesday, February 20, 2019 2:03 AM
  • Hi Jeff0803,

    Thank you for posting here.

    Based on your description, you want to know if it is safe to access the DB table from two different processes.

    I suggest that it is best for you not to use one database from different processes, because it could cause performance and timing issues.

    If you want to know more about it, the following link could be helpful for you.

    https://stackoverflow.com/questions/3479297/multiple-application-using-one-database

    Hope my advice could be helpful.

    Best regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 20, 2019 6:21 AM
    Moderator
  • Your options are best determined by how many people will potentially access the same record for updating at the same time which if this is the case can get ugly and the same goes for when not accessing the same record but when something changes in a related table. If this is the only option you should do load test which is generally best done with a scripting tool that is throttled to do slightly more work then expected.

    Depending on how important the database is to your organization the best option is to use a enterprise level database such as Microsoft SQL-Server which out of the box is better at handling transactions in regards to writing to the database tables. 

    No matter which direction taken wrap your write operations in a transaction which is fairly easy using a managed data provider such as OleDb on the connection. A transaction is connected to the connection object via cn.BeginTransaction (where cn is the connection), perform the write operations in a try/catch with a Commit after the operation and in the catch a RollBack on failure which can be against one or more write operations.

    Bottom line, you are risking failure with MS-Access, especially with an older version of MS-Access.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Jeff0803 Wednesday, February 20, 2019 2:21 PM
    Wednesday, February 20, 2019 11:22 AM
    Moderator
  • Actually my question was about some flag to use between different process.

    For example, one process make the flag is ON when access DB. If this flag is ON, another process wait until this flag is OFF.

    Is it reasonable to use flag between different proceess?

    Which way is the correct way to use flag between different process?

    Wednesday, February 20, 2019 2:26 PM
  • Since it was small business internal website with limited usage it seemed to work ok.  I would never use it for a serious business website. 

    As I recall now,  it was a client/server application using a Windows desktop program as the client  with a backend COM+ server using Access, which was used company wide at corporate headquarters. I converted the backend over to using MS SQL Server and my problems went away.    
    Thursday, February 21, 2019 5:10 AM