compact data base

[Guest]

developing an application for a client using access 97 because that is the level the client has.

i have a command button on the main menu that allows them to compact the mde data base. i have a date routine that gives a pop up window when a specific number of days has elapsed, reminding them that they must compact the data base.

when i use the following code without a data base password, the compact routine works fine.
Private Sub compact_data_base_Click()
On Error GoTo Err_compact_data_base_Click

Call Application.Run("compacter.docompact", True)

Exit_compact_data_base_Click:
Exit Sub

Err_compact_data_base_Click:
MsgBox Err.Description
Resume Exit_compact_data_base_Click

End Sub

when i add a data base password, i get the error message 'not a valid password - error code 3031'. this message appears at the final step of compacting. any thoughts as to how i can correct this?

 

[Dev Ashish]

Call Application.Run("compacter.docompact", True)
when i add a data base password, i get the error message 'not a valid
password - error code 3031'. this message appears at the final step
of compacting. any thoughts as to how i can correct this?

I'm assuming you are talking about the compacter addin?
[ http://www.mvps.org/access/modules/mdl0030.htm ]

If this is the case, then you'll need to tweak the mda file a bit. I
don't have Access 97 so I can't test this out. If you open the mda file
with the shift key down, you can modify it yourself. You might be able to
pass a password to DoCompact method, but Access doesn't accept teh
database password as part of the command line. You'll have to most likely
modify the DoCompact routine so that it instantiates Access with
CreateObject and then pass the same instance to the fCompactDatabase
routine to replace
Set objAccess = GetObject(strMDBNamePath)

Once you do that (note that the db hasn't been opened in this new
instance), close currentDatabase in active Access instance, compact the
database (DBEngine.CompactDatabase), openCurrentDatabase in the new
Access instance with the password passed in the 'OpenCurrentDatabase'
call.

-- Dev

 

[Guest]

thanks. that is what i was talking about. i copied my code into the question but apparently it did not actually transfer so you could see it.

 

[Guest]

i appreciate your help but i am still a rooky at vb coding, and as a result i am not astute enough to follow your suggestions.
the first thing i did was make a copy of the mda file and then tried to make changes, so i would not totally screw up the mda file. i need very specific instructions on how to fix.
thanks

 

[Dev Ashish]

i appreciate your help but i am still a rooky at vb coding, and as a
result i am not astute enough to follow your suggestions. the first
thing i did was make a copy of the mda file and then tried to make
changes, so i would not totally screw up the mda file. i need very
specific instructions on how to fix. thanks

Replace these 2 routines and call the compact routine as

Call Application.Run("Compacter.docompact", True, "password")

' *** Code Start ***
Function DoCompact(ReStart As Boolean, Optional pwd As String)
Dim strCompacter As String
Dim strExePath As String
strExePath = SysCmd(acSysCmdAccessDir) & "msAccess.Exe"
strCompacter = Chr$(34) & CodeDb.Name & Chr$(34) _
& " /cmd" & " " & CurrentDb.Name
If ReStart = True Then
strCompacter = strCompacter & ";True"
Else
strCompacter = strCompacter & ";False"
End If

If (Len(pwd)) Then
strCompacter = strCompacter & ";" & pwd
End If
Call Shell(strExePath & " " & strCompacter, vbNormalFocus)
End Function

Function fCompactDatabase(strMDBNamePath As String) As Boolean
'*******************************************
'Name: fCompactDatabase (Function)
'Purpose: Close (if Open) and compact an external Jet Database
'Author: Dev Ashish
'Date: 04 November 1998
'Called by:
'Calls: API, TempFile
'Inputs: strMDBNamePath - full path to MDB file
'Output: True on success, False on failure
'*******************************************
On Error GoTo Err_Handler
Dim strMsg As String
Dim strFileOnly As String
Dim strNewFile As String
Dim intI As Integer
Dim strTmpName As String
Dim intPos As Integer, pos2 As Integer
Dim pwd As String
Dim objAccess As Access.Application

'Determine whether we need to reopen the source database or not
'See if the semi colon separater is there
intPos = InStr(1, strMDBNamePath, ";")
' Determine password, if present
pos2 = InStr(intPos + 1, strMDBNamePath, ";")

If intPos > 0 Then
'If string to the right is False, just close
If (pos2 > 0) Then
strTmpName = Mid$(strMDBNamePath, intPos + 1, pos2 - intPos)
Else
strTmpName = Right$(strMDBNamePath, Len(strMDBNamePath) -
intPos)
End If

If (pos2 > 0) Then
pwd = Mid$(strMDBNamePath, pos2 + 1)
End If

'get a clean name for strMDBNamePath
strMDBNamePath = Left$(strMDBNamePath, intPos - 1)

Select Case UCase(strTmpName)
Case "FALSE":
mblnReopen = False
Case "TRUE":
mblnReopen = True
Case Else:
'Assume the we have to reopen
mblnReopen = True
End Select

Else 'Assume we have to reopen if the option isn't there
mblnReopen = True
End If

'Invalid argument value
If Len(strMDBNamePath) = 0 Then Err.Raise mconERR_NO_COMMAND_LINE

'File doesn't exist
If Len(Dir(strMDBNamePath)) = 0 Then Err.Raise mconERR_FILE_NOT_EXIST

'strip out the filename from db path
strFileOnly = Dir$(strMDBNamePath)

'Get a unique filename to compact to
strNewFile = TempFile(False)

'Try to get a handle to the other Access instance
Do While objAccess Is Nothing
intI = intI + 1
If intI = 50 Then Err.Raise mconERR_INSTANCE_NOT_FOUND
Set objAccess = GetObject(strMDBNamePath)
DoEvents
Loop

'Close the database and compact it to a new file

'close all open objects
Call sCloseAllObjects(objAccess)
With objAccess
.CloseCurrentDatabase
Call sUpdateStatusForm("Compacting " & vbCrLf & strMDBNamePath &
vbCrLf _
& " to " & vbCrLf & strNewFile)
If (Len(pwd)) Then
'DBEngine.CompactDatabase strMDBNamePath, strNewFile,
";pwd=" & pwd
DBEngine.CompactDatabase strMDBNamePath, strNewFile, , ,
";pwd=" & pwd
Else
DBEngine.CompactDatabase strMDBNamePath, strNewFile
End If

DoEvents
Kill strMDBNamePath
DoEvents
Call sUpdateStatusForm("Copying " & vbCrLf & strNewFile & vbCrLf
_
& " to " & vbCrLf &
strMDBNamePath)
FileCopy strNewFile, strMDBNamePath
Do While Len(strMDBNamePath) = 0: DoEvents: Loop

'Only open if specified
If mblnReopen Then
If (Len(pwd)) Then
.OpenCurrentDatabase strMDBNamePath, , pwd
Else
.OpenCurrentDatabase strMDBNamePath
End If
Else
.DoCmd.Quit
End If
Kill strNewFile
End With

fCompactDatabase = True
Exit_Here:
On Error Resume Next
DoCmd.Close acForm, Forms(mconSTATUS_FORM)
Exit Function
Err_Handler:
fCompactDatabase = False
Select Case Err.Number
Case mconERR_INSTANCE_NOT_FOUND:
strMsg = "The Access instance containing the database " &
vbCrLf
strMsg = strMsg & pconQ & strMDBNamePath & pconQ & vbCrLf
strMsg = strMsg & "couldn't be located."
MsgBox strMsg, vbCritical + vbOKOnly, "Access instance not
found!"
Case mconERR_NO_COMMAND_LINE:
strMsg = "Invalid database name."
strMsg = strMsg & vbCrLf & vbCrLf & pconQ & pconQ & vbCrLf &
vbCrLf
strMsg = strMsg & "Compacter will terminate."
MsgBox strMsg, vbCritical + vbOKOnly, "Invalid database
name!"
Case mconERR_FILE_NOT_EXIST:
strMsg = "The database you specified" & vbCrLf
strMsg = strMsg & pconQ & strMDBNamePath & pconQ & vbCrLf
strMsg = strMsg & "doesn't exist." & vbCrLf & " Please check
the filename and try again!"
MsgBox strMsg, vbCritical + vbOKOnly, "File not found"
Case 429:
strMsg = "The Compacter utility couldn't locate Access
instance!"
MsgBox strMsg, vbExclamation + vbOKOnly, "Access instance not
found"
Case Else:
MsgBox "Error: " & Err.Number & ". " & Err.Description,
vbExclamation + vbOKOnly, _
"Unknown Error"
End Select
Resume Exit_Here
End Function

' *** Code End ***

-- Dev

 

[Guest]

thank you for all your help. i replaced the routines as suggested. checked 2x to make sure had keyed correctly
changed the call routine.
when i hit the compact button on the main menu screen, i immediately get the message "wrong number of arguments or invalid property assignment". I checked the documentation for run and I do not see any error in the construct

i searched microsoft.com for the error message "invalid propery assignment" and for the one mentioned for an explanation as to what causes the error. got everything but what i needed
so i need your advice

thanks

 

[Guest]

checked more microsoft sites for the error message "wrong number of arguments or invalid property assignment". got information that explained the error. reviewing that information and then checking the code, do not see why should get the message.

appreciate your help in this matter

thanks

 

[Dev Ashish]

when i hit the compact button on the main menu screen, i immediately
get the message "wrong number of arguments or invalid property
assignment". I checked the documentation for run and I do not see any
error in the construct.

Make sure you backup the addin from Document and Settings\profile
\Application Data\Microsoft\AddIns (if you are using XP) and replace it
with the new version as this is the version that gets used.

-- Dev

 

[Guest]

i am working with access 97 because that is what my client has.

when you sent the code, the first thing i did was make a copy of compacter.mda. then inserted the new code into the copy. obviously, commented out the old code.
then renamed compacter.mda to compacterorig.mda and renamed the copy to compacter.mda.

then fixed the run statement in the access app. as indicated earlier, get the wrong argument message the second i click the compact button on the menu.

 

[Dev Ashish]

then fixed the run statement in the access app. as indicated earlier,
get the wrong argument message the second i click the compact button
on the menu.

Please keep in mind that when you install an Addin and if the addin
specifies (as in this case) a 'Library' path in usysrefInfo table as
|ACCDIR\filename.mda, then Access *copies* the file to an Addin folder
under your profile before using it. The original copy that you tried to add
will not be touched or used in this case.

-- Dev