bradyboyy88
Well-known Member
- Joined
- Feb 25, 2015
- Messages
- 562
Hi,
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.
Thanks
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.
Thanks
