Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122

    Set Compact/Repair Schedule for a Password Protected Backend Database

    Access Experts,



    I have been struggling to find a good solution to that... but can't find an easy on the web. I would like at mid-night everyday, my backend database is being compacted. (i.e. it compacts itself automatically without an user intervention)

    If this can not be done, perhaps the front end database can be left running and it fires the VB Code to auto compact its linked password protected backend database? Any insight? Ty

    Sincerely,
    Perry

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there,

    Please feel free to use my back-end compacting utility, I have recently updated it to support password protected files. You use it by creating a scheduled task in Windows scheduler to run at your preset times. Should be self explanatory how to set it up but feel free to ask if you have any questions.

    http://forestbyte.com/ms-access-util...end-compacter/

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Can I suggest that as a precaution you add code to backup the BE database BEFORE it is compacted...
    Apologies to gicu if this is already done as part of his compacting utility.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Colin, no worries, yes it does do pre (and optional post-compacting backups) and the compacting is done in a local folder (that local post-compacting copy can also be preserved as an additional backup)

    Click image for larger version. 

Name:	PrePostBackup.png 
Views:	32 
Size:	19.0 KB 
ID:	41166 Click image for larger version. 

Name:	BAK.png 
Views:	32 
Size:	40.5 KB 
ID:	41167

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That sounds perfect!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    You are wonderful!! Just a few questions:

    1. What does AutoRun mean? (loaad when windows loads and hide in the background?)

    2. If the computer reboot, what would happened? (i.e. the FBA_BAK will auto run and start compacting until my preset time in the scheduler?)

    3. If users are still using the BE database, what would happened? Compact won't take place?

    Thank you so much!!

    Sincerely,
    Perry

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Perry,

    When you setup the Windows scheduler task make sure you place the utility in a local drive that is available even if no user is logged on, you want the scheduler to have access to it even after you're logged out (manual or by Windows itself).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    Gicu, that's a great tip as well. ty. Can you help answer my other questions?

    1. What does AutoRun mean? (loaad when windows loads and hide in the background?)

    2. If the computer reboot, what would happened? (i.e. the FBA_BAK will auto run and start compacting until my preset time in the scheduler?)

    3. If users are still using the BE database, what would happened? Compact won't take place?

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Perry,
    Here are my answers:

    1. When AutoRun is checked the utility does its thing with no human intervention, such as when started by a scheduler task. So once you setup all the required info (file names, paths, etc.) you check the autorun and after a couple of seconds you will see it do the compacting/backup then it will shut down.

    2. When you setup the scheduler task you are given the option to run the task with no one logged on, you should choose that if you want the task to run at times when nobody is logged on the workstation (this is what my previous post was about).

    3. If the users are still using the BE the utility will not run (it checks for the presence of the laccdb or ldb associated with the back-end file). The compacting takes place in a local folder (most problems associated with compacting Access files are caused by attempting to do it on network drives) then it copies the file back to its original location which it couldn't do if the file was in use.

    Note the the utility produces a txt file log in the same folder as itself that shows you the results of each compacting attempt.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    Ty sir. It is good to know the thoughts behind this wonderful utility. When I got this error, does it mean BE is being used? Click image for larger version. 

Name:	error.JPG 
Views:	25 
Size:	32.0 KB 
ID:	41197

    I ran this couple times already without a problem, then, I encountered this error message now every time I run it.

  11. #11
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    one other request... is there a way to mask the PW?

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    For the error can you please check the file name and paths. Clicking each text box and press Shift F2 to invoke the zoom window to check for extra lines. I need to recompile the accde for the pad mask and I"m on thg e road right now, twill be few hours,I'll let you know.
    Cheers,

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Perry,

    The password field now has a password input mask. Have you figured out the error?

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Perry,

    Just curious if you got it working, what was causing the error?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    VLAD,
    It apparently never worked.. I thought it was working nightly... ahhh... It worked while I was log-on and manually executed it. Not sure why.


    Click image for larger version. 

Name:	Capture.JPG 
Views:	7 
Size:	63.1 KB 
ID:	41394

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-03-2016, 03:58 AM
  2. Replies: 8
    Last Post: 05-08-2016, 09:41 AM
  3. Split Database | Compact and Repair
    By mrmmickle1 in forum Access
    Replies: 4
    Last Post: 07-27-2015, 08:53 AM
  4. Backend File Compact And Repair
    By data808 in forum Access
    Replies: 5
    Last Post: 04-03-2015, 03:04 AM
  5. Compact and Repair Encrpyted Database Problem
    By mrlddst in forum Security
    Replies: 16
    Last Post: 05-06-2014, 01:24 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