Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Compact database automatically

  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565

    Compact database automatically

    Hi,

    i have read that compacting automatically is wrong.
    Why ?

    My code looks like below:



    Code:
    Function BackUps()
    
    
    Dim sFile As String
        Dim Db As dao.Database
        Dim oDB As dao.Database
        Dim oTD As TableDef
        Dim OtherDB As dao.Database
        Dim sPath As String
        Dim newFile As String
        Dim sPath2 As String
        
        
        DoCmd.SetWarnings False
    
    
    On Error Resume Next
    
    
    'paths for my databases and their copies
    
        sPath = "\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Back-End\Aplikacja_Braki_BE.accdb" 
        sPath2 = "\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Back-End\Aplikacja_Braki_BE_V2.accdb"
        sFile = "\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Archiwum_kopie\" & Format(Now(), "DD-MM-YYYY_HHMM") & ".accdb"
           
    'here i am creating copy of my back-end
    
            DBEngine.CompactDatabase sPath, sFile, , , ";pwd=1234"
    
    
    ' here i am checking if database is open, if yes close the database
    
        If CheckLock("\\nt-wro4-01\dane\ihrm\dokument\worek\KADRY\DOKUMENTY _BRAKI\Back-End\Aplikacja_Braki_BE.accdb") = True Then
            Access.Quit
            Exit Function
        Else
    'if back-end is closed- i am creating copy of my database (and in the same time i am compacting it), i am killing the sPath file and changing name from Path2's name to Path1's name.
            DBEngine.CompactDatabase sPath, sPath2, , , ";pwd=1234"
            Kill sPath
            Name sPath2 As sPath
        End If
    
    
        Set Db = Nothing
        
    On Error GoTo 0
    
    
    Access.Quit
    
    
    End Function
    
    
    'function to check if database is opened
    Private Function CheckLock(strFileName As String) As Boolean
        On Error Resume Next
        Dim intFF As Integer
        intFF = FreeFile
        Open strFileName For Binary Access Read Lock Read As #intFF
        Close #intFF
        If Err.Number Then
            Err.Clear
            CheckLock = True
        Else
            CheckLock = False
        End If
    End Function
    What about automatically compacting database? what is wrong with that?

    Best Wishes,
    Jacek Antek

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    Check out this article here: http://rogersaccessblog.blogspot.com...e-feature.html

    Note, that I used to have my database set to "Compact on Close", and I would occasionally experience database corruption, where I would then have to copy over all my database objects to a new database.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    Thank you JoeM,

    i have read your post and unfortunately i did not understand it at all.

    You are giving also an example database to download as this should work: ImportToTempDatabase.mdb but macros there are not working. And there is a lack of txt file.

    The data import problem can be addressed by linking a table or file for processing rather than importing it. If it can't be linked, it can be imported into a second, temporary database and then linked. This temporary database can be deleted later. I have a sample on my website called ImportToTempDatabase.mdb, which illustrates how to do this.
    So as i understand it is better to link table and update main table then to import data to temporary table (in my case from Excel).

    In my current model i have temporary tables where i am importing data from Excel and run update and append queries. After all i am deleting imported data from temporary table in Access (there are in back-end all time), so it is bad approach?
    Do i understand it correctly?

    So better idea is to create link table Access-Excel and run update and append queries in order to keep always the newest data. After all link between access-excel table shoud be deleted?
    Or create temporary database, there temporary tables should be created, data imported to them and linked with front-end. Next append and update queries should run and finally temporary database should be deleted?

    ----------------->

    Additional questions:

    1. If i have front-end i should not do compacting on close so i should do nothing with this? Only create back-ups of Back-end yes ?

    2. Your sample CompactBackEndDB.mdb is not working (
    Code:
    Set tbl = db.TableDefs("DetailRemote")
    - "there is no object within this collection" ).
    How can i properly do this? Create a temporary database, copy all tables from my back-end, rename temporary database and delete my back-end table ?

    Thank you for helping,
    Best Wishes,
    Jacek Antek

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,454
    My $0.02.......

    As Joe stated "Compact on Close" is a known cause of corruption.

    If you have a split dB, using "Compact on Close" only compacts the FE.

    Unless there are temporary tables in the FE, using the dB (FE) won't /shouldn't cause bloat in the FE.
    The bloat will be in the BE because of adding/deleting records in tables.

    IF you set the BE to "Compact on Close", what would happen if you were the last one out of the dB, the BE starts the "Compact on Close" and at the same moment, someone else opens their copy of the FE which tries to connect to the BE?? I shudder to think about it.


    Before I manually initiate a compact the BE, I always make a backup or two. So no auto compacts for me....


    All in all, I think "Compact on Close" is a very bad idea.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    Thank you ssanfu for your help!

    Okey, compcat on close is bad, i understand this now !
    I should compacting BE only.

    Okey you can do manually compacting your database but i would like to autimatically do it as JoeM wrote.

    So again question to JoeM regarding back-uping back end:

    How can i properly do this? Create a temporary database, copy all tables from my back-end, rename temporary database and delete my back-end table ?


    thank you for help,
    Jacek

  6. #6
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    And one more thing.

    I added option for Administrators to create daile back-ups of back-end:

    Code:
    Private Sub ButtonArch_Click()    
        Dim sFile As String
        Dim Db As dao.Database
        Dim oDB As dao.Database
        Dim oTD As TableDef
        Dim OtherDB As dao.Database
        Dim sPath As String
        Dim newFile As String
        
        DoCmd.SetWarnings False
        
    On Error Resume Next
    
    
        sPath = CurrentProject.Path & "\Aplikacja_Braki_BE.accdb"
        sFile = "C:\Users\ljar01\Desktop\Makro Braki Access\Nowy\Archiwum\" & Format(Now(), "DD-MM-YYYY_HHMM") & ".accdb"
        
        Set Db = CurrentDb
        
        Application.CompactRepair sPath, sFile
    
    
     DoCmd.SetWarnings True
     
     MsgBox "Archiwizacja przebiegła pomyślnie"
    I am using method Application.CompactRepair. Can it cause also the interrupting the database?

    Or maybe i should copy database only and compact only the copy ?
    Or create copies only and do not compact them.

    warm regards,
    Jacek

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    As Joe stated "Compact on Close" is a known cause of corruption.
    That is really the main point I was trying to make with that link. The link goes into details why, if you are interested in a detailed explanation.
    I have never tried compacting by code.

    So again question to JoeM regarding back-uping back end:
    I would not overcomplicate it, and just follow Steve's advice. Make a backup copy, then do the manual compacting.
    Actually, in my environment, it is even easier, as our system gets backed up every night, so we really don't need to worry about that step, we already have nightly backups.
    If you do your compacting shortly after the backup happens, there probably isn't need to manually create another backup.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  8. #8
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    Quote Originally Posted by JoeM View Post
    That is really the main point I was trying to make with that link. The link goes into details why, if you are interested in a detailed explanation.
    I have never tried compacting by code.
    Ok thank you JoeM,

    So Application.CompactRepair when admin decides is not good idea...
    But what if users will accidentally delete data from table?

    the purpose of that was to do back-ups via button in order to avoid situations where users delete data...

    Process is like that: users have to have possibility to delete changes within different records and accidentally they can also delete all.


    Quote Originally Posted by JoeM View Post
    I would not overcomplicate it, and just follow Steve's advice. Make a backup copy, then do the manual compacting.
    Actually, in my environment, it is even easier, as our system gets backed up every night, so we really don't need to worry about that step, we already have nightly backups.
    If you do your compacting shortly after the backup happens, there probably isn't need to manually create another backup.
    Your code from your blog is :

    Code:
    Private Sub cmdCompact_Click()On Error GoTo Err_cmdCompact_Click
    
    
        Dim LinkPathFile As String
        Dim LinkPath As String
        Dim LinkFile As String
        Dim FileWithoutExtention As String
        
        'uses the findsource, getpath, and getfile functions
        'to determine the path and filename of the linked database
        LinkPathFile = Mid(FindSource(), 11)
        LinkPath = getpath(LinkPathFile)
        LinkFile = getfile(LinkPathFile)
        FileWithoutExtention = Left(LinkFile, InStr(LinkFile, ".") - 1)
        
        'Compact the Back-End database to a temp file.
        DBEngine.CompactDatabase LinkPath & LinkFile, LinkPath & FileWithoutExtention & "Temp.mdb"
    
    
        'Delete the previous backup file if it exists.
        If Dir(LinkPath & FileWithoutExtention & ".bak") <> "" Then
            Kill LinkPath & FileWithoutExtention & ".bak"
        End If
    
    
        'Rename the current database as backup and rename the temp file to
        'the original file name.
        Name LinkPath & LinkFile As LinkPath & FileWithoutExtention & ".bak"
        Name LinkPath & FileWithoutExtention & "Temp.mdb" As LinkPath & LinkFile
    
    
    Exit_cmdCompact_Click:
        Exit Sub
    
    
    Err_cmdCompact_Click:
        MsgBox Err.Description
        Resume Exit_cmdCompact_Click
        
    
    End Sub


    I do not understand why your code is indeed creating and compacting back-end.
    Sorry Guys i need to know it exactly and i have to ask about it until i will know everything.

    aah so first create a copy of database and after that i can do a backup using your macro ?
    I can in the night run macro which will be creating copy of back-up and in the morning run macro to compact this copy.

    Best wishes,
    Jacek

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    Your code from your blog is :
    It is not my blog. It is just an article that I came across that talks about why you wouldn't want to use "Compact on Close".

    I did not write the code you are referencing, nor have I ever used it. So I really cannot say much about it.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  10. #10
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    Ok thank you very much JoeM!

    I improved my code:

    Code:
    Sub test()
    
    'On Error GoTo Err_CreateTempDB
    Dim Directory As String
    
    
    DoCmd.SetWarnings False
    
    
    Dim dbNew As Database, db As Database
    Dim tbl As TableDef, length As Integer
    Set db = CurrentDb
    
    
    Directory = "C:\Users\ljar01\Desktop\Makro Braki Access\Nowy\Pesel_IBS_All.xlsm"
    
    
    DoCmd.TransferSpreadsheet acLink, , "tbl_temp_SAP_pr", Directory, True, "Arkusz1!"
    
    
    DoCmd.OpenQuery "QryUpdateSAP"
    DoCmd.OpenQuery "QryAppendSAP"
    
    
    db.TableDefs.Delete "tbl_temp_SAP_pr"
    DoCmd.RunSQL "DELETE * FROM tbl_Employees_SAP WHERE len(Pesel_nr)<5"
    
    
    DoCmd.SetWarnings True
    'Set tbl = db.CreateTableDef("tbl_temp_SAP_pr")
    'tbl.Connect = (";DATABASE=" & "C:\Users\ljar01\Desktop\Makro Braki Access\Nowy\Pesel_IBS_All.xlsm")
    'tbl.SourceTableName = "tbl_temp_SAP_pr"
    'db.TableDefs.Append tbl
    
    
    End Sub
    in order to avoid temporary tables i am only linking tables from Excel and after updating data - removing them.

    Ok now i understand.

    Summary of this topic:

    1. Try do not do compact and repair your back-end automatically - it can cause database corruption.
    2. Create only copies of your back-end (copy file) and compact it manually, perfectly it would be done on your local disc.

    Users should not have the possibility to compact database via button - it can be a cause of corruption of back-end.

    Everybody agrees with that?

    edit: it is not annoying fro you that you have to every day go to last copy of your database and compact it manually as developers?
    I have 2 tools on access right now, and will be more...

    Thank you for your help,
    Best Wishes,
    Jacek

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,454
    edit: it is not annoying fro you that you have to every day go to last copy of your database and compact it manually as developers?
    I usually compact the BE (and make a backup) before I start entering the months data so not too bad to do


    It has been a long, long time since I wrote a DOS batch file, but you can create a batch file that executes a command line that compacts an Access dB.

    The command line is: "the full path to Access" "the dB path" /compact
    For me, it would look like
    Code:
    "C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" "C:\Forum\Access Database 2015\Access BE\database3_BE.accdb" /compact
    You must ensure that no one is in the BE before executing the batch file.

    IIRC, there is a way using DOS to determine if a file (.laccdb) exists in the directory and not run the command line if it exists. But I would have to dig out my DOS books that are packed away.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,341
    FWIW, I have set db's to compact on close BUT only if the size exceeds a factor of about 2 or 3 times. That is, if I compacted it just before release and it was 6 Mb, I'd set the threshold to 12 Mb (or maybe 15). It depended on the nature of the db, like if temporary queries or temp tables were in play. I never stored objects in tables, so that was never an issue. And yes, there were daily backups. Once before all the ODBC data was imported (to ensure the end of day state was preserved) and again after the ODBC update finished. This captured both the last data entered and the new source data for the start of the day.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  13. #13
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    Steve,

    thank you, could you please fine the DOS code also?

    Micron,

    thank you!

    I'd set the threshold to 12 Mb (or maybe 15).
    So it is possibility to set the threshold automatically - if database size exceeds a factor 2 or 3 times - create back up? Did you do this automaticallly ?

    And yes, there were daily backups. Once before all the ODBC data was imported (to ensure the end of day state was preserved) and again after the ODBC update finished. This captured both the last data entered and the new source data for the start of the day.
    It is done automatically? And by backup you mean copying database into archive folder?

    Best Wishes,
    Jacek

  14. #14
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    565
    in VBA i have found code to check if database is used:

    Code:
    Private Function CheckLock(strFileName As String) As Boolean
        On Error Resume Next
        Dim intFF As Integer
        intFF = FreeFile
        Open strFileName For Binary Access Read Lock Read As #intFF
        Close #intFF
        If Err.Number Then
            Err.Clear
            CheckLock = True
        Else
            CheckLock = False
        End If
    End Function
    tested - it is working!

  15. #15
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,341
    Is your db split? I don't see where you've clarified this. Code to detect a split, single user front end will always show it's in use, won't it? That can include the back end as well. If a FE has a connection, the BE is in use, even in a split db. Don't see how that's useful. A count of logged in users (from a user table) would tell you if there's any more than 1. AFAIK, there will always be at least regardless if it's split or not. I would not rely on the locked db file either (ldb in 2007).
    if database size exceeds a factor 2 or 3 times - create back up?
    The backups were done nightly regardless. The compact was only done if the file size exceeded the threshold. The backup was stored in a folder I could access but the users could not. The backups were done by using Windows Task Scheduler. I suppose you could also backup the db before a compact.

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

Similar Threads

  1. Compact thesize of MS Access Database
    By kacocal in forum Import/Export Data
    Replies: 3
    Last Post: 03-06-2016, 04:25 AM
  2. Split Database | Compact and Repair
    By mrmmickle1 in forum Access
    Replies: 4
    Last Post: 07-27-2015, 08:53 AM
  3. Unable able to compact BE Database
    By crowegreg in forum Access
    Replies: 3
    Last Post: 01-30-2014, 09:02 AM
  4. Replies: 7
    Last Post: 11-11-2010, 11:16 AM
  5. Compact/Reapir doe not reduce database size
    By nosrepmodnar in forum Access
    Replies: 1
    Last Post: 10-23-2009, 02:38 AM

Posting Permissions

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