+ Reply to Thread
Results 1 to 7 of 7

Locking Issue using ADO with Excel as a Database

  1. 08-15-2016, 06:50 PM #1
    scott.j.minard
    scott.j.minard is offline
    Registered User
    Join Date
    08-15-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    3

    Locking Issue using ADO with Excel as a Database

    Hi,

    I currently have an ongoing issue using ADO with Excel as the BE database.
    My application uses an Excel (2010) workbook as a backend to store the required data, then uses another excel workbook to query (Using list objects with data connections) and also manipulate and edit the data in the back end using ADO recordsets. I have used this setup as MS Access is not currently an option, and I need multiple users to be able to manipulate and query data at the same time without read-only restrictions.

    The issue is that occasionally the excel BE database seems to go into a "Locked state". When in this "locked state", the recordset appears to update successfully. However, when I open the BE database or requery with a new ADO connection the changes have disappeared.
    The only way for me to fix this currently is to open the excel BE Database, hit Save and close the workbook.

    The most frustrating part is that most of the time it works without issue.

    Any help would be greatly appreciated. An example of my connection string is below...

    Please Login or Register  to view this content.
    Last edited by scott.j.minard; 08-15-2016 at 07:02 PM.
    Reply With QuoteRegister To Reply
  2. 08-15-2016, 06:59 PM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Locking Issue using ADO with Excel as a Database

    Just curious, but why are you using ADO and DAO?

    Can't you do everything in ADO?
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 08-15-2016, 07:01 PM #3
    scott.j.minard
    scott.j.minard is offline
    Registered User
    Join Date
    08-15-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    3

    Re: Locking Issue using ADO with Excel as a Database

    Sorry, that was a typo. I am trying to do it all with ADO.
    Reply With QuoteRegister To Reply
  4. 08-16-2016, 01:06 AM #4
    Kyle123
    Kyle123 is offline
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Locking Issue using ADO with Excel as a Database

    Excel isn't multi user, you're going to have these sorts of problems unless you use a database. Why isn't access an option?
    Reply With QuoteRegister To Reply
  5. 08-16-2016, 01:30 AM #5
    scott.j.minard
    scott.j.minard is offline
    Registered User
    Join Date
    08-15-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    3

    Re: Locking Issue using ADO with Excel as a Database

    MS Access is against company policy.
    This is small scale, I am not likely to get more than 2 users at any one time.
    The issues are arriving also arriving during testing when there are not multiple users using the system.


    For example when I run the following code. When the file is in a locked state output reverts back to the original value, even though it successfully updates the value at the start.
    When the database is not in a locked state the output changes to "TestUpdated".
    I hope this makes sense, I have also checked the file attributes to make sure that it is not read only etc.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 08-24-2016, 06:31 AM #6
    Daniele85
    Daniele85 is offline
    Registered User
    Join Date
    08-24-2016
    Location
    Camaiore, Italy
    MS-Off Ver
    2013
    Posts
    2

    Re: Locking Issue using ADO with Excel as a Database

    Hi scott,
    i have the same problem... everything seems to work fine (no errors) but after updating, the database (.xlsm file) is always the same.
    Please, let me know if you resolved this problem!
    Daniele
    Reply With QuoteRegister To Reply
  7. 08-24-2016, 06:38 AM #7
    Daniele85
    Daniele85 is offline
    Registered User
    Join Date
    08-24-2016
    Location
    Camaiore, Italy
    MS-Off Ver
    2013
    Posts
    2

    Re: Locking Issue using ADO with Excel as a Database

    Hi scott,
    i have the same problem... everything seems to work fine (no errors) but after updating, the database (.xlsm file) is always the same.
    Please, let me know if you resolved this problem!
    Daniele
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Access Database and Excel Pivtot Table Issue
    By jefflab1 in forum Excel General
    Replies: 0
    Last Post: 08-11-2015, 11:05 PM
  2. Screen locking issue
    By incommlyndon in forum Excel General
    Replies: 0
    Last Post: 11-08-2013, 11:23 AM
  3. Autofilter issue using macro when locking worksheets
    By rajiraji in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2013, 11:27 PM
  4. Help with macro, automatical locking cells when excel is closed but only locking 1 sh
    By snoopy1461 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2011, 11:29 AM
  5. Excel accessing external database issue
    By wacattack in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-08-2010, 11:44 AM
  6. Issue trying to retrieve records from excel database
    By marktheman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2007, 04:24 PM
  7. Font issue? Excel locking up.
    By plunk25 in forum Excel General
    Replies: 1
    Last Post: 05-16-2006, 02:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1