ADO Events and Data Integrity


Well-known Member
Feb 25, 2015

I have built a userform in excel which stores all of its data in a access database. There are multiple users at once but only one connection to the database is allowed to ensure one table is not updated while another user is using it for something else. I am wondering if ADO and access can handle the following scenarios:

- Are there ADO events that could be triggered if a ADO connection was lost similar to events that excel has built in like workbook_change and things of that nature?
- Why is it considered bad practice to keep the connection open at all times and if so is there a connection property to allow all users to have an open connection but only one user be allowed to edit the db at once?
- Say I had multiple procedures in excel which do different updates to the database. This includes say update one table then update another table (all of which need to be updated to ensure integrity), etc. If the connection were to be lost in the middle of the procedures, how does one keep data integrity to just roll back all the changes made because all the updates were not executed. Since this is a client side application this seems a bit more complicated then a server side like a website which ensures all changes are made regardless of if the user timed out their connection right?

I have been wondering these things for quite a while so any insight would be appreciated.


Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...