Results 1 to 10 of 10

Thread: JRO Database File Size Limitations??

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Question JRO Database File Size Limitations??

    I am working with a particular client database (my design) that will "works with all features of my program but will not compact. My program stalls, no VB6 Error thrown, even though there is an error handler, and I have to Win10 "task manager" close it manually. Other client's databases (same structure) work fine, but not this one. (All the other client databases are the same except for the data contained in them.)


    I note that this DB file size is about 57MB.
    I also note that that in Access it will execute the Compact/Repair (not hang), but that the file size does not seem to change ( so I don't know if even Access is actually compacting/repairing the database).

    On a hunch, I opened the database, and deleted about half the records, then I compacted it in Access, and now my program does not hang when it runs the database thru its compaction function.

    Here is the Function:
    Code:
    Public Function MyCompactDB(strSRCPath As String, strTGTPath As String) As Boolean
        'IMPORTANT
        'in the strDBTarget connection string set the Engine Type number
        '  according to the JET version you are using
        '
        'Jet x.x Format MDB Files  Jet OLEDB:Engine Type
        '************************  ************************
        '         JET10                     1
        '         JET11                     2
        '         JET2X                     3
        '         JET3X                     4
        '         JET4X                     5
            '<EhHeader>
            On Error GoTo CompactDB_Err
            '</EhHeader>
    
        'Debug.Print "strSRCPath = "; strSRCPath
        'Debug.Print "strTGTPath = "; strTGTPath
    
           'On Error GoTo Err_compact
           Dim JRO As New JRO.JetEngine
    
           'Source and Destination connection path
           Dim strDBSource As String
           Dim strDBTarget As String
    
           Dim DataBase_MDW_SecurityFilePath As String
    
    100    DataBase_MDW_SecurityFilePath = App.Path & "\Databases\MyDatabaseSecured.mdw"
    
    102    DoEvents
    
    104    strDBSource = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & strSRCPath & ";" & _
                            "Jet OLEDB:System database =" & DataBase_MDW_SecurityFilePath & ";" & _
                            "Jet OLEDB:Engine Type=5;" & _
                            "Jet OLEDB:Database Password= " & g_strJet_DB_Password & ";" & _
                            "User ID= " & g_strDBUserID & ";" & _
                            "Password = " & g_strDBPassWord & ";" & _
                            "Jet OLEDB:Encrypt Database=True"
    
    
    106    strDBTarget = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & strTGTPath & ";" & _
                            "Jet OLEDB:System Database =" & DataBase_MDW_SecurityFilePath & ";" & _
                            "Jet OLEDB:Engine Type=5;" & _
                            "Jet OLEDB: Database Password= " & g_strJet_DB_Password & ";" & _
                            "User ID= " & g_strDBUserID & ";" & _
                            "Password = " & g_strDBPassWord & ";" & _
                            "Jet OLEDB:Encrypt Database=True"
    
           'JRO has a CompactDatabase method that takes two parameters:
           '  the ADO connection string for the source, and the destination database.
           '  NO NEED TO CREATE A COPY OF THE DB.
    108    JRO.CompactDatabase strDBSource, strDBTarget
    
    110    CompactDB = True
           Exit Function
    
    
    'Err_compact:
    '
    '112    CompactDB = False
    '114    Err.Description = "fso.CopyFile Error!"
    '116    MsgBox Err.Description, vbExclamation
    
            '<EhFooter>
            Exit Function
    
    CompactDB_Err:
            MsgBox Err.Description & vbCrLf & _
                   "in xGELv2.modMain.CompactDB " & _
                   "at line " & Erl
            Resume Next
            '</EhFooter>
    End Function

    Is there a file size limit on the JRO CompactDatabase??
    Something else happening?

    Ideas?

  2. #2
    Frenzied Member
    Join Date
    Jan 2020
    Posts
    1,620

    Re: JRO Database File Size Limitations??

    you can copy all table to new mdb file

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: JRO Database File Size Limitations??

    Quote Originally Posted by xiaoyao View Post
    you can copy all table to new mdb file
    Interesting that you mention this. I sometimes have to do that with various Word/WordPerfect documents when they get corrupted. I do a Save As and then Open the new file and all workd.
    Was thinking that something similar like you suggest ( basically copying the entire database into a new one might be the trick.

    Have not copied (import??) one database into another before, though.

  4. #4
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    4,699

    Re: JRO Database File Size Limitations??

    I think xiaoyao is correct.
    A compact is nothing more then copying the complete DB content to a fresh DB.
    Then all flagged as deleted records are of course skipped and you end up with (sometimes) a smaller DB.

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,825

    Re: JRO Database File Size Limitations??

    Data pages are vacuumed of deleted data, indexes get rebuilt, identity values typically get compacted, lots of small things involved.

  6. #6
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    470

    Re: JRO Database File Size Limitations??

    I do everyday compact on access DB larger than 1.7 Gb
    takes time but working

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,825

    Re: JRO Database File Size Limitations??

    Try JetComp.exe, a utility that can sometimes compact and repair MDBs too broken for MS Access or JRO.

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: JRO Database File Size Limitations??

    Quote Originally Posted by dilettante View Post
    Try JetComp.exe, a utility that can sometimes compact and repair MDBs too broken for MS Access or JRO.

    Ill check it out.. Do you think/know if it can be "Shell and Wait" to?

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: JRO Database File Size Limitations??

    Quote Originally Posted by Thierry69 View Post
    I do everyday compact on access DB larger than 1.7 Gb
    takes time but working
    Would you like to take my code above and give it a try form a VB6 project on your large database, and let me (us) know?

  10. #10
    Frenzied Member
    Join Date
    Jan 2020
    Posts
    1,620

    Re: JRO Database File Size Limitations??

    you can split to 3 database,main.mdb,2.mdb

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