compact access2000 mdb using vb6 on windows 7 platform 64 bit

I am having a problem with VB6 program running on a Windows 7 Home Premium 64 bit operating system

I am attempting to compact / repair an Access 2000 MDB using the Microsoft Jet and Replication Objects 2.6 Library.

I compile the VB6 program on an XP computer, then run the exe on the XP and on the Windows 7 notebook.

In design mode:

First I dim JRO as JRO.JetEngine
Then Set JRO = New JRO.JetEngine

Then execute the JRO.CompactDatabase method to compact the data base

For testing I exit the sub routine where the compact is executed immediately after the compact instruction.

The source mdb and destination mdb are in the same folder for testing purposes.

Executing the code Within the Windows 7 notebook the source file (MDB) is there unchanged and the destination file (MDB)
does not get created.

Running the exact same exe on the XP computer works correctly. I can see both the source and destination files and
can see that the destination file size has decreased from the source version.

I checked the notebook computer for the presence of the msjro.dll.  It is located at path :
c:\program files (x86)\common files\system\ado\msjro.dll

Any assistance with this problem is greatly appreciated.

Or, if there is another procedure for compacting an Access 2000 MDB on the Windows 7 64 bit platform that would work also on other platforms (XP, Vista)
that would be fine.  This is one of the last few problems that I need to solve in order to release the software to
the public.  We need for it to run on Windows 7 64 bit systems.  

This is a fairly sophisticated program that has many features and after extensive testing on the 64 bit system this is one of the last few issues we have run in to.

I have included a code snippet

Thanks for any assistance here.

morrisbo

Private Sub MnuFCompact_Click()

   On Error GoTo mnuCompactErr
   Dim TransOK As Boolean
   Dim Response As Integer
   Dim lresponse As Long
   
   lresponse = my_taskDialog(frmAction, App.Title, "Compact & Repair Data Base ?", "", xtpTaskButtonYes + xtpTaskButtonNo, 3)
   
   If lresponse = xtpTaskButtonNo Then Exit Sub
   
   lresponse = my_taskDialog(frmAction, App.Title, "All Work stations must be completely out of the ARS system before proceeding!!", "", xtpTaskButtonOk, 3)
   
   If Len(Dir$(gPathData$ & "\ARSDB.LDB")) > 0 Then
       lresponse = my_taskDialog(frmAction, App.Title, "Database is in use.  Unable to complete the operation.  Please try this operation later.", "", xtpTaskButtonOk, 3)
       Exit Sub
   End If
   TransOK = MarkDB
   
   Screen.MousePointer = vbHourglass
   
   Dim JRO As JRO.JetEngine
   Set JRO = New JRO.JetEngine

   Dim DB_sour As String
   Dim DB_dest As String
   Dim sSource As String
   Dim sDestination As String
  
   sSource = ModGlobal.gQuoteDatabaseName
   
   sDestination = ModGlobal.gPathData & "\compactMDB"
    
   Dim fso As New FileSystemObject

   If fso.FileExists(sDestination) = True Then
       Kill sDestination
   End If
  
   DB_sour = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & sSource
  
   DB_dest = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & sDestination & " ;Jet OLEDB:Engine Type=5"

   JRO.CompactDatabase DB_sour, DB_dest

GoTo Finish
   
   If fso.FileExists(sDestination) = True Then

      Kill sSource

      FileCopy sDestination, sSource

   End If
   
   
   Set JRO = Nothing
   Screen.MousePointer = vbDefault
   lresponse = my_taskDialog(frmAction, App.Title, "Compact complete.", "", xtpTaskButtonOk, 3)
   
   
   Dim CompactFile As String
   CompactFile = gPathData$ & "\" & "compact.txt"
   Open CompactFile For Output As #1
   Print #1, Date, Time
   Close #1
   Dim TypeOp As String
Finish:
   TypeOp = "C"
   TransOK = UnMarkDB(TypeOp)

   
   Exit Sub
   
mnuCompactErr:
   MsgBox Err.Description & " in CompactMDB in Backup procedure"
End Sub

Open in new window

morrisboAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Does it have to be done inside your application?  If not, you can use the JetComp utility from Microsoft.  It supports 97-2003 MDB formats.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
<<Or, if there is another procedure for compacting an Access 2000 MDB on the Windows 7 64 bit platform that would work also on other platforms (XP, Vista)
that would be fine.  This is one of the last few problems that I need to solve in order to release the software to
the public.>>

I use the Jet Compact 4 (EXE) utility from my Access front ends and VB apps. It can be called passing it a command line.

See:
Jet Compact Utility Available in Download Center

For:

    * Microsoft Office XP
    * Microsoft Office 2000
    * Microsoft Office 97
    * Microsoft Access 2002
    * Microsoft Access 2000
    * Microsoft Access 97
    * Microsoft Visual Basic 6.0
    * Microsoft Visual Basic 5.0
    * A Microsoft Office XP Developer run-time application that includes the run-time version of Microsoft Access 2002
    * A Microsoft Office Developer 2000 run-time application that includes the run-time version of Microsoft Access 2000
    * A Microsoft Office Developer Edition 97 run-time application that includes the run-time version of Microsoft Access 97
    * A Microsoft Visual Basic 6.0 run-time application that includes Microsoft Jet database engine 3.5 or 4.0
    * A Microsoft Visual Basic 5.0 run-time application that includes Microsoft Jet database engine 3.5
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Here is the comnd line options:

---------------------------
Jetcomp 4.0
---------------------------
Usage: JetComp
-src:{database name}
-dest:{database name}
-y{system database location}
-u{user name} (default=admin)
-p{user password}
-w{database password}
-x Encrypt Destination Database
-t Use Database Locale when copying data for text columns
-v4 Destination db is 4.x format (default)
-v3 Destination db is 3.x format
-L0 Db locale - None Use current language (default)
-L1 Db locale - English, German, French, and Portuguese
-L2 Db locale - Italian, and Modern Spanish
-L3 Db locale - Arabic
-L4 Db locale - Simplified Chinese
-L5 Db locale - Traditional Chinese
-L6 Db locale - Russian
-L7 Db locale - Czech
-L8 Db locale - Dutch
-L9 Db locale - Greek
-L10 Db locale - Hebrew
-L11 Db locale - Hungarian
-L12 Db locale - Icelandic
-L13 Db locale - Japanese
-L14 Db locale - Korean
-L15 Db locale - Norwegian and Danish
-L16 Db locale - Polish
-L17 Db locale - Slovenian
-L18 Db locale - Traditional Spanish
-L19 Db locale - Swedish and Finnish
-L20 Db locale - Thai
-L21 Db locale - Turkish
If no options are passed, GUI front end will start.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

morrisboAuthor Commented:
TheHighTechCoach

Thanks,

I think this might work.

I am not familiar with using the command line from within a VB6 program.

Would I constrict a bat format file and just shell to it from within the VB6 program.

I am just a novice here.

A sample code snippet would really help.

morrisbo
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can generally do this with a Shell command:

Shell "full path to jetcomp.exe" -src: "full path to your source database" -dest: "Full path to the destination database"

----------------------------------------------

However, the issue may be UAC on the Windows 7 machine. Where is the .mdb file located on the machine? If you're trying to wite to one of the protected areas, you'll find that you cannot do this with UAC enabled.
0
morrisboAuthor Commented:
LSMConsulting

Thanks,  using shell will be my next step.

I am assuming that jetcomp can recognize that the MDB is Access2000.  

I plan to do some studying on Windows 7.

I also believe the problem has something to do with UAC.

The files are in c:\program files (x86)\ars\data

morrisbo
0
aikimarkCommented:
you should probably check with the JetComp help information to see what the default version is.  You can set the version with the -v parameter.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<The files are in c:\program files (x86)\ars\data>

This most likely your issue. On Windows 7 machines with UAC in place, Program Files is a protected directory. Your data should be in one of the "Data" locations on Win7, like ProgramDAta, Users\<SomeUser> or something of that nature. Note that a folder directly built on the root (i.e. "C:\MyData") can also be used, but that may change in the future.

As a side note: Program Files should NEVER have been used to house data, and MS has made a good move IMO in restricting that directory. I too was caught by this change, and had to rework nearly all of my apps to respond to this change. In the end, I had a more robust application, since my users could pick/choose where to house their data, and my apps now work on any supported environment.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
<<The files are in c:\program files (x86)\ars\data >>

That location is an is an issue.  I would never put data in the program files folders.

I totally agree with LSMConsulting's side note.

0
morrisboAuthor Commented:
Changing the Install location for the Access 2000 MDB  away from \PROGRAM FILES did the trick.

The compact procedure using the the JRO.CompactDatabase method works now on the Windows 7 Professional platform.

I also looked at the JetComp utility documentation which indicated that it was possibly more powerful in repairing damaged Access MDB's so I also tested using Shell from within the VB6 program, using a Windows API code snippet I found on the Internet to pause execution until the compact either completed successfully or returned a flag if it failed.

The lesson I learned is that Windows 7 requires more fine tuning to accomodate "Legacy" 32 bit  apps like VB6 to run correctly on 7 64 bit..

Another problem was that not including all required dlls for the 3rd party controls imbedded in our application and running on Windows 7 just stops with the message "Program is stopping" without giving any indication of what the problem is.

Also, I have read up on the UAC feature of Windows 7.

I plan to look into what is involved in the option on Windows 7 running in XP mode and the "virtual machine" procedure just as insurance for our app. when it goes out the door.  

Anyways, thanks Guys for your help.  Doubt if I could ever have solved the problems without outside assistance.

250 points each to LSMConsulting & TheHiTechCoach.

Morrisbo
0
morrisboAuthor Commented:
Expert Exchange came through here.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.