[RESOLVED] Need help with RepairDatabase and CompactDatabase

[brianemcnamee]

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.

[dilettante]

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

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

[brianemcnamee]

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!

[brianemcnamee]

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.

[SamOscarBrown]

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

[brianemcnamee]

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

[Argus19]

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

[dilettante]

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.

[Argus19]

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

I tried late binding:

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.

[brianemcnamee]

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.

[Argus19]

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.

[brianemcnamee]

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.

[Argus19]

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

[brianemcnamee]

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...

[brianemcnamee]

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!

[brianemcnamee]

Good grief! How to you set the "Resolved" flag

[SamOscarBrown]

Under THREAD TOOLS on the menu. Selected Mark as Resolved

[Elroy]

Whenever this issue comes up for me, I still tell my users to open the database with MS-Access, and to manually click the buttons therein.

However, if I were to ever try to programmatically do this, I'd probably do it through MS-Access automation, rather than using the ADO (or RDO or DAO). Here's my thinking ... I suspect that Microsoft as improved these compact/repair procedures through the years, with hopefully the latest versions of them in their latest versions of MS-Access. Also, I sure like the way the latest versions of MS-Access just do it all "in-place" now, not asking you for new file names.

I have automated MS-Access from VB6 before, but I haven't ever programmed up this specific (compact/repair) task before. But, I can't believe that it'd be terribly difficult. Also, just to say it, I'd probably do it all in a late-bound fashion, so that I wouldn't have a permanent link/requirement for MS-Access on all machines running the VB6 software.

But, obviously, that approach does require MS-Access on the machine to work.

Good Luck,
Elroy

[brianemcnamee]

Elroy, the entire problem was that the REPAIR capability doesn't exist anymore! And I tried every possibility I could imagine and those suggested by the helpful folks who responded to my plea. When I gave up on the REPAIR attempt and moved on to the COMPACT capability it worked, even with my simplistic first configuration of code.

[dilettante]

I haven't found anything that tells why the ACE engine lost the repair functionality, but newer versions of Access can't even get to it through their private interfaces. They never released a newer JetComp.exe utility for accdb files either.

I have found a few 3rd party utilities that make bold claims, but none that are free so I haven't tested them. One has a free trial download, but I got bored pretty fast since nothing said what the trial could or couldn't do.

[brianemcnamee]

dilettante, I think, which means it's a guess with no supporting evidence whatsoever, that the ACE engine never had the capability. I started on PC around 1980 with Radio Shack boxes then began using Windows with Windows 1, and I think, with the first version of Office ( stand alone MSWord on 3.5 inch floppies ). I didn't get involved in doing much programming on PCs until Windows 3 and VB 1 when my site offered our customer VB 1 demonstrations of the screen displays for their developing product. I was a Systems guy on old Univac 1100 Series from the 1107 days ( 1965 ) on. Sysgens, occasional mods to the various Execs ( I, II, 8, OS1100 ) and sometimes,programming on IBM 360s ( and their predecessors, 1400s and 7000 Series ) and then into the MiniComputers, where I mostly did data conversions from one system to the other. Sometimes, after installing software I had to learn about it to overcome the complaints from the Users that it didn't work. I got good the Univac DMS1100 ( Database package ), Mapper 1100 ( a really clever file system "analyzer" that I wound up creating a monster with, but that's a different story), and CMS 1100 ( Communications interface and manager ) that way. So I've had a very wide range of experience. After discovering how much fun VB was I built a VB 1 computer simulator game for my girl friend's son as a "project" to demonstrate how computers worked it: displayed arithmetic and data registers and had a "program input" function ( an assembly level language of about 8 instructions ) and a "memory function" that stored the input and fed instructions to the registers and received the results of the register activity and so on. I contemplated expanding it into a generalized "system to system" converter back in the early Minidays when the machines were basically "lab devices" and data wasn't interchangable between them. But I kept getting embroiled in very large projects ( e.g.; > Gigabuck+ ) which were generally badly managed and it was me against the world! But I won "moral victories". So I tended to have a cavalier attitude about programming on my PC. Anyhow, the REPAIR capability appears to have it's origin in the original Jet ( not ACE ) engine ( perhaps necessary because early versions misplaced data? ) and and went the way of 7 track tapes and 300 Baud communications when the ACE product appeared.

[Elroy]

I've got Access 2019 installed on my machine, and here's what the button says:




And, just playing around with some automation, this code seems to work:

Private Sub Form_Load()
    Const acQuitSaveNone As Long = 2&
    Const MyDbSpec As String = "c:\users\Elroy\Desktop\test.mdb"
    Const MyDbTemp As String = "c:\users\Elroy\Desktop\temp.mdb"
    '
    Dim AccessApp As Object
    Set AccessApp = CreateObject("Access.Application")
    AccessApp.Visible = False
    '
    AccessApp.CompactRepair MyDbSpec, MyDbTemp
    Kill MyDbSpec
    Name MyDbTemp As MyDbSpec
    '
    AccessApp.Quit acQuitSaveNone
    Set AccessApp = Nothing
End Sub

Now, I don't know for certain that the CompactRepair method is calling the same code as clicking the button, but I'd assume it is.

Also, you'd sure want to put some error trapping into that code, to make sure the user has MS-Access, and to make sure the CompactRepair method runs ok, as it requires exclusive access to the database. Also, you might want to make sure your temp database doesn't initially exist.

Anyway, my two-cents.

Good Luck,
Elroy

p.s. I also tried with an accdb type database, and it seems to work just fine.

[brianemcnamee]

Elroy, AccessApp.CompactRepair isn't available in my Access 2007/12. There are separate Compact and Repair functions, which I assume would normally be performed in Repair and then Compact order. The problem is that both functions appear in the various VBA functionality descriptions. However, the Repair command ( appears to ) fails to do anything and the "On Error Resume Next" and "If ERR > 0 Then Msgbox ' Error! Err: "+Format(Err)+vblf+vblf+ERROR' " fails to indicate any error occurred! Using exactly the same code with the Compact command instead of the Repair command produced a compacted Db... There is the possibility my analysis is wrong and the Repair actually occurred without any discernible changes to the Db! E.G.; it didn't need to be Repaired!

[SamOscarBrown]

A side note, brian....

In your reply to Elroy, you gave an 'example' of your Error routine. In it, you used the plus sign (+) to combine strings/characters.
Use the plus signs for math, use the ampersand (&) for concatenating Strings. Ex:

Err : Err & vbCrLf & vbCrLf & Error

or another example:

Dim myString as String, sString1 as String, sString2 as String, sString3 as String
sString1 = "Brian"
sString2 = "M"
sString3 = "McNamee"
myString = sString1 & " " & sString2 & ". " & sString3

myString would equal "Brian M. McNamee"

[brianemcnamee]

It works...

[SamOscarBrown]

I know....but it won't sometimes, trust me...it is not the proper character to use when putting together strings.

[brianemcnamee]

Good to know! Thanks.