Results 1 to 9 of 9
  1. #1
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127

    Appending to other database no longer works in macro.


    Hi Guys,

    Strange issue that has popped up out of no-where. I am assuming it's an Access 2019 issue.

    I have a series of several Append queries that append from one database to identical tables in another 'master' database. Both are local and up until recently this always worked with no issues.

    Now when I try to run all the queries at once through a macro, I keep getting the error that the 'master' database is locked or protected. The file name also shows up with the .laccdb locked extension in the error message which is strange. I check the append directory and they are all correct (the normal .accdb file extension).

    When I run these queries individually everything works fine, but when executed by a macro I keep getting the error. And seemingly every time it's a different query that doesn't work. It's not consistent.

    I have my computer and all documents as trusted on both of the databases.

    Any ideas?

    Thank you.

  2. #2
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Anyone? I have just tested this on a colleagues computer and the result is the same. The Macro does not work however all the individual appends do (when I run them one by one).

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Whenenever a database is opened, a companion "locking" file is created while it is open (the "laccdb" file you are seeing).
    I suspect what is happening when you see this behavior is that it hasn't finished the previous query and closed that connection before it starts the next one. So, it sees that the database is already open, and it doesn't like that.
    It is like you need a "pause" between running each query, to allow time for the previous one to complete and close before going on to the next one.

    In order to do that, you may need to convert your Macro to VBA, as I don't know that you can do that in a Macro.
    If you do a Google Search on "place a pause or wait command in an access macro", you can see how others did it (and maybe borrow their code).

  4. #4
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Thank you, JoeM

    While I agree this could be an issue. Why is it only becoming apparent now when I've run this macro dozens of times?

    I will investigate if it's a server sync error.

    Justin

  5. #5
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    The issue was with Dropbox.

    Typical. Sorry I had to waste everyones time. Maybe someone will see this and it'll help them.

    Thanks

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by JRodko View Post
    The issue was with Dropbox.
    It sounds like you are either running a non-split dB IN Dropbox (not local as you stated) or you have a split dB with the FE on your local computer and the BE in Dropbox.
    Either way you are asking for trouble. I hope you are doing at least daily full backups - better would be after changes.

    Dropbox (and One Drive, Box, etc) are designed for file transfer of large files or for backup storage, NOT for executing programs. Yes, I know you can open/edit Excel spreadsheets, Word Docs, Text files, but an Access database is a completely different object model.


    I would rather use macros than open/edit files in Dropbox (et al.) ....... and I never learned how to use macros!


    Not a matter IF the dB goes corrupt, just a matter of WHEN!

    Best of luck....... you'll need it.

  7. #7
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Hi ssanfu,

    Thank you for your concern. My database is not split. I designed these queries/macros so that I can work totally offline and append records to a master database when I see fit.

    While yes I technically work 'in' dropbox, it's rare that we have an issue. The only two people that have access to the shared master database are myself and one other person that's 1000 miles away. We get around corruption by checking when it is in use.. as long as we do that we have not had significant issues.

    Once in a while dropbox will generate a conflicted copy, but that's generally a simple fix. We keep backups daily, even when nothing has been changed. It has come in handy once or twice.

    Do you recommend a better type of file sharing/folder syncing software for both local and non local computers sharing data? I would be very interested in hearing your thoughts!

    Thanks

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not having a split dB, working in Dropbox, having a person 1000 miles away making changes...... you live a risky lifestyle in the computing world.....

    Suggestions:
    - Split the dB into a BE and a FE. High priority!
    - Move to a cloud based system - connect using RDP. We had 4 VMs - 3 workstations and a Server. The BE was an Access BE with around 23 tables. One table had approximately 1.5 million records. No problems.
    - Use Citrix


    Good luck......

  9. #9
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    I neglected to mention that much of the work we do is totally offline. This is an 'office' as well as a field database. I would have loved to a moved to a true cloud based service long ago. I have considered setting up an SQL server but I don't know if the juice would be worth the squeeze at this point. Perhaps one day.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-04-2017, 07:59 PM
  2. Replies: 3
    Last Post: 03-08-2017, 05:42 PM
  3. Replies: 2
    Last Post: 03-01-2017, 06:36 PM
  4. Replies: 1
    Last Post: 12-14-2015, 05:32 PM
  5. Data entry form no longer works
    By Jamesiv1 in forum Access
    Replies: 1
    Last Post: 05-13-2014, 09:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums