Results 1 to 15 of 15

Thread: Need help with RepairDatabase and CompactDatabase

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Need help with RepairDatabase and CompactDatabase

    I've been using Office 2007 since 2007, and earlier versions since around '95. I've been trying to write a program to Repair and Compact an .mdb ( actually an .accdb ) that I had to delete many K records from. When I get to the DBEngine.RepairDatabase command I either get "Compile Error Sub or Function not defined" - I guess the wrong References - or ERR "3343 Unrecognized database format" - again I thing a Reference problem. My DBEngine is version 12. When I Open my database with Access is says it's 2007 and the Current Database Properties Version is 12. Can some please provide me with an example of a working Repair/Compact Database example with the References and Dims/Globals used to define the objects used? I've juggled every Reference possibility that appears to have anything to do with Access, Office, DAO, Jet, or Visual Basic to no avail.

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,545

    Re: Need help with RepairDatabase and CompactDatabase

    I don't have the ACE 12.0 stuff installed but it should be pretty similar to this:

    Code:
    Option Explicit
    
    Private Sub CompactDb(ByVal DbOrig As String, ByVal DbCompacted As String)
        'Requires a reference to:
        '
        '   Microsoft Office 14.0 Access database engine Object Library
        
        On Error Resume Next
        GetAttr DbOrig
        If Err Then
            On Error GoTo 0
            Err.Raise vbObjectError Or &H4102&, "CompactDb", "No database to compact"
        End If
        On Error GoTo 0
        
        With New DAO.DBEngine
            .CompactDatabase DbOrig, DbCompacted
        End With
    End Sub
    
    Private Sub Main()
        CompactDb App.Path & "\sample.accdb", _
                  App.Path & "\samplecompacted.accdb"
        MsgBox "Done"
    End Sub

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Need help with RepairDatabase and CompactDatabase

    Ok, The code looks pretty similar. I have a "Microsoft Office 12.0 Access database engine Object" and some "Microsoft DAO I.JJ Object Library" routines. They conflict with each other and the "Microsoft DAO I.JJ Object Library" routines don't get me by the "Name conflicts with existing module, project, or object library" - meaning "Microsoft Jet and Replication Objects 2.6 Library", which got me by the "ERR 424 Object required" problem. When I used both of those I got a Compile error: Method or data member not found. Most of my DB programs define the Database by "Dim DbView as Object" and Recortdset as "DimDbRS as Object". After looking at the Northwind examples I changed that to "Dim DbView as Database" and Recordset as "DimDbRS as Recordset" - didn't seem to affect anything. The DB was created as an .accdb but I changed the extension to .mdb to make the FileListBox patterns I use happy and Access seems happy with it. That has never bothered the many AccessDBs I use. The program also has references to "Visual Basic For Applications", "Visual Basic runtime objects and procedures" and "Visual Basic objects and procedures" - a carry over from my program template which I don't think affects anything. In the program I use the FSO system to display the Filesize before and after Repairs and Compacts ( if I can get the Repairs and Compacts to work! ) so I need a few References! I also display the number of records in the Tables, so I have to OpenDatabases and RecordSets. But, I've never seen your "With New DAO.DBEngine CompactDatabase..." usage. I'll try that instead of the "DBEngine.CompactDatabase" I've been using.

    Thanks for the example!

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Need help with RepairDatabase and CompactDatabase

    PowerPoster: This is more complicated than I could've imagined! I just read an article that describes the changes which occurred with Office 2007. Mainly the ACE Jet was introduced. Apparently I've been running all my Dbs - which are mostly .accdb with the mdb extension applied - with the older Jet and never ran into a problem before - maybe! I had a working version of a Repair/Compact routine back around 2007 but it quit working and I haven't had a good reason to use one since then. I did notice that my DBCreater - I wanted a Schema-like listing of my Dbs - does Reference the Microsoft Office 14.0 Access database engine Object Library you mentioned and my VB ( 6.0SP6 ) doesn't complain ( and it works!). I'm going to isolate the Repair/Compact calls in their own routines - I had other Object References being called in the Routines that don't work - and make another attempt to whittle down the References I need. And change the extension back to .accdb!

    Thanks again for providing me with a direction.

  5. #5
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,934

    Re: Need help with RepairDatabase and CompactDatabase

    I hope you DO know that just changing an extension from .mdb to .accdb is not "A" solution....it would be like changing .ppt to .doc. Apples.oranges.

    I am sure dile will help if you run into other issues.

    Sammy
    Sam I am (as well as Confused at times).

  6. #6

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Need help with RepairDatabase and CompactDatabase

    PowerPoster/Sammy Yes, I know. But the file was created as an .accdb and I changed the extension to .mdb. I've been doing that for over 20 years and never had a problem before! It's hard to get info on the dbVersion ( DatabaseTypeEnum Enumeration (DAO) ) names to use when creating a Db, so I've been just using 30 and then converting the result to .accdb on its first Open. Maybe it's time to update the dbVersion name I use. dbVersion120 should result in a .accdb. The relationship to dbVersion names to functionality also appears to be flaky. Values 1 - 40 appear to be based on processor word length, and then 120 pops out of nowhere but seems to be the Jet to ACE crossover. I'm also, strangely, having problems with my EXCEL Object naming...


    BTW, the

  7. #7
    Addicted Member
    Join Date
    Jul 2020
    Posts
    240

    Re: Need help with RepairDatabase and CompactDatabase

    Isn't it easier to set database properties ?
    https://support.microsoft.com/en-us/...9f4005b2#bm3_1

  8. #8
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,545

    Re: Need help with RepairDatabase and CompactDatabase

    That's only useful if your database has been "beheaded" i.e. opened in MS Access and used that way often. It does nothing about programmatically compacting a clean database that Access has never touched and goobered up.

  9. #9
    Addicted Member
    Join Date
    Jul 2020
    Posts
    240

    Re: Need help with RepairDatabase and CompactDatabase

    Quote Originally Posted by dilettante View Post
    I don't have the ACE 12.0 stuff installed but it should be pretty similar to this:
    I tried late binding:
    Code:
    Option Explicit
        Dim myDB As Object
        
    Public Function GetDBEngine() As Object
        On Error Resume Next
        Set GetDBEngine = CreateObject("DAO.DBEngine.150")
        If Err.Number <> 0 Then
            Err.Clear
            Set GetDBEngine = CreateObject("DAO.DBEngine.140")
        If Err.Number <> 0 Then
            Err.Clear
            Set GetDBEngine = CreateObject("DAO.DBEngine.120")
        End If
    End If
    End Function
    
    Private Sub Form_Load()
        Dim myEngine As Object
        
     On Error Resume Next
            Set myEngine = GetDBEngine
                 If Err.Number <> 0 Then
                 MsgBox "DBEngine not installed", vbCritical + vbOKOnly, "Error!"
                 Exit Sub
                 End If
            Set myDB = myEngine.OpenDatabase(App.Path & "\2_base.accdb")
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
    If IsObject(myDB) Then
        Set myDB = Nothing
            End If
    End Sub
    On Win10X64 there is Microsoft Office 15.0 Access database engine Object, but the program "does not see" it.
    Everything works on Win7.

  10. #10

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Need help with RepairDatabase and CompactDatabase

    Sammy, I think I've figured out the problem (maybe!). It looks like "Repair" went away with the original Jet Engine. But, it still appears in some of the documentation:

    DBEngine.CompDBactDatabase method (DAO) ; Microsoft Docs ; Docs.Office client development.Access.Access desktop database reference.Microsoft Data Access Objects reference.DBEngine.Methods

    Note: Docs.Office client development.Access.Access desktop database reference.Microsoft Data Access Objects reference.DBEngine.DBEngine object contains: "Use the CompactDatabase and RepairDatabase methods to maintain database files" But RepairDatabase is not listed in the methods section.

    So, maybe if I quit attempting to use the RepairDatabase method that problem may go away... I'll try just Compacting and see if this is true.

    Argus19. My attempt at a Repair/Compacter program queries the Database and and the Database tells me I'm using DBEngine 12.0. Which is what I thought I was creating in my DBCreater program. Here's the statement I've been using forever ( pzDBVersion is my "schema" parameter for DBVersion, Global gdbDBCrtr As Object ) and it's been working since 2002 (at least; I think dbVersion30 was the 3rd one I've used, 20 is still an option in the program - when I was experimenting I tried 120 and 40, they both worked when I was using Office16. ):

    If pzDBVersion = "dbVersion30" Then Set gdbDBCrtr = CreateDatabase(gzDBCrtrDatabaseFile, dbLangGeneral, dbVersion30)

    A couple of years ago when I was "experimenting" with Win10 and Office2016 I was using 14.0, but I backed out of both of those because they slowed my machine down more that I felt necessary. I'm only connected to me don't need the overhead of being connected to everything or anything. Besides I'm uneasy about being connected to the Cloud for no reason except to expedite MS's movement toward computing as a service... Since abandoning Win7 in January MS has been updating my box up to 3 times a day ( not counting the uncontrolled updates to EDGE, which is certainly filling in the apparent boundaries it started as ). Lately I've been losing control of my cursor to mysterious activity - I think the Win10 Virtual Network I detested in Win10 has now been installed here! I long for the olden days when I was a "Systems Manager" for Univac 1100 Systems that only had a couple of hundred, maybe a few thousand users doing mostly classified stuff from dreary old DP to gaming! And forays into DMS1100 and the clever Mapper application.

    I do have Win10 on my laptop and an Office16 I will reinstall on that when I get around to it.

    This has covered a lot of ground! Thank you both.
    Last edited by brianemcnamee; Jul 21st, 2021 at 06:29 PM.

  11. #11
    Addicted Member
    Join Date
    Jul 2020
    Posts
    240

    Re: Need help with RepairDatabase and CompactDatabase

    If this is your personal database and you work with it on the same computer, then determining the version of "Microsoft Office XX Access database engine Object" is not a problem. I suggested searching for this library "by trial and error" if the operating system or driver updater, etc., decide to update Microsoft Office for you.
    Or if the program will run on another computer.
    Last edited by Argus19; Jul 22nd, 2021 at 03:48 AM.

  12. #12

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Need help with RepairDatabase and CompactDatabase

    Argus19, I really do know (e.g.; have determined ) the version of the DB I'm using. Since I use the DBs and programs on my laptop when I travel ( Win10, but still using Office 2007, although I have installed Office2016 and run, effectively, all my VB6 (SP6) and Office 2016 programs/Macros in that environment ). Now that Microsoft has "updated" my Win7 to be as slow as Win10 ( increased overhead for functionality I don't use ) I'll probably be returning to the Win10 / Office 2016 environment ( maybe - I only have 1 copy of Office2016 but 3 computers... ) in the near future.

  13. #13
    Addicted Member
    Join Date
    Jul 2020
    Posts
    240

    Re: Need help with RepairDatabase and CompactDatabase

    brianemcnamee, if the version of the library is known, then it is easier to set the link:
    Project-> References->

  14. #14

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Need help with RepairDatabase and CompactDatabase

    Argus19, I think the "solution" to my problem is to give up on the attempt to "Repair" the DB! Apparently the "Repair" method disappeared in the transition from the original Jet to the ACE. I went through all the Projects-> in the Object Browser and there was no "Repair". But there is a Compact and I'll settle for that. After all, if the DB is usable what is there to Repair? I recently recognized the problem of "Information Obsolescence" when I glanced at a chart of the classification of fish. No teolosts! Back in the day that was one of the "higher" classifications of the fishy world ( roughly "bony fishes" ) but the hierarchy has been redrawn and that noun has been relegated to the dustbin. So, it seems, has my dear old DbRepair! Since that recognition I've seen manifestations of the "problem" in several fields. I'll report the results of dropping the DbRepair code and ( probable ) success of the DbCompact code when I switch computers and make the changes...
    Last edited by brianemcnamee; Jul 22nd, 2021 at 01:58 PM.

  15. #15

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Need help with RepairDatabase and CompactDatabase

    Time to end the Thread! My problem was, in fact, due to the DbRepairDatabase method having gone away. Except for that, the same code, and References used in my attempt to conjure up the DbEngine.RepairDatabase method worked for the DbEngine.CompactDatabase method. My Compacted DB was 3,104,768 bytes thinner than the unCompacted version.

    Thanks again for your assistance!

Posting Permissions

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



Click Here to Expand Forum to Full Width