SHFileOperationA with Access 2K3?

[rubbernilly]

I had a database in Access 2K format that had an archive function that utilized the SHFileOperationA API call:

Private Declare Function apiSHFileOperation Lib "shell32.dll" _
            Alias "SHFileOperationA" _
            (lpFileOp As SHFILEOPSTRUCT) _
            As Long

It works great in the 2K format database (opened in Access 2K3).

This past weekend, I upgraded a copy of the database to Access 2K3 format, and in my testing of the application, I found that the 2K3 database generates a windows error (wanting to send Microsoft a report) when it hits this API call. Access stops responding for about 15 seconds, then the error pops up asking if I want to create a backup copy of the database and compact it again. If I hit Send or Don't Send, the database closes.

The 2K database (opened in Access 2K3) works like a champ. On the same computer, the 2K3 database (opened in Access 2K3) generates the error and closes.

Does this API call not work with 2K3? Is there another I should be using?

 

[rubbernilly]

OK, got it. I don't know why this works, but maybe it will help someone else in the same situation.

I not only converted the database to 2K3 format, but I also modified the custom function wherein this API call was made so that it was looking for an argument of a public enum. That was the problem, not the conversion to 2K3. But I still don't understand why. Without passing the argument (making the function check for the value itself) it worked fine. Passing the argument, it doesn't work.

Here are the specifics:

Clicking on the "Archive" button runs code that includes the line...

sArchive = LocalMakeBackup()

LocalMakeBackup utilizes a couple of API calls, the first to get the Windows Open/Save box to get the name of the archive as the user wants it saved.

Function LocalMakeBackup() As String
Dim strMSG As String
Dim tsiFileOp As SHFILEOPSTRUCT
Dim lngRet As Long
Dim strSaveFile As String
Dim lngFlags As Long
Const err_USER_CANCEL = vbObjectError + 1
Const err_DB_EXCLUSIVE = vbObjectError + 2
    On Local Error GoTo MakeBackup_Err

    If DBExclusive = True Then Err.Raise err_DB_EXCLUSIVE
                
    lngFlags = FOF_SIMPLEPROGRESS Or _
                            FOF_FILESONLY Or _
                            FOF_RENAMEONCOLLISION
[blue]    strSaveFile = GetSaveFile(DLookup("Value", "ProgramData", "Type = 'Archive Location'"), _
                              "Archive Database", [/blue][maroon]GetArchiveDefaultName(Me.cboNamePattern)[/maroon][blue], _
                              "Access Database", ".mdb")[/blue]
    If strSaveFile = "" Or IsNull(strSaveFile) Then Err.Raise err_USER_CANCEL
    If Dir(strSaveFile) <> "" Then Kill strSaveFile
    With tsiFileOp
        .wFunc = FO_COPY
        .hwnd = hWndAccessApp
        .pFrom = CurrentDb.Name & vbNullChar
        .pTo = strSaveFile & vbNullChar
        .fFlags = lngFlags
    End With
    [red]lngRet = apiSHFileOperation(tsiFileOp)[/red]
    LocalMakeBackup = strSaveFile
    
MakeBackup_End:
    Exit Function
MakeBackup_Err:
    'error handler removed
End Function

The GetSaveFile function above (in blue) wraps up the GetOpenFileNameA and GetSaveFileNameA API calls. The GetArchiveDefaultName function, in maroon, is a custom function that suggests a name for the database depending on a combo box on the form. The options correspond to a simple Public Enum:

Public Enum ArchDefs
    adArchDate = 1
    adNameDate = 2
    adArchIter = 3
    adNameIter = 4
End Enum

This is used in the GetArchiveDefaultName function to suggest a name, as I said. An adArchDate name would suggest a name like "Archive_02092006.mdb", while adNameIter would suggest "ThisDBName_0.mdb".

So here is the difference...

If I rewrote the declaration of the LocalMakeBackup function to include an argument:

Function LocalMakeBackup([blue]ArchType as ArchDefs[/blue]) As String

And then pass the value of the cboNamePattern combobox as the argument:

sArchive = LocalMakeBackup(Me.cboNamePattern)

So that in the LocalMakeBackup function my call to the GetArchiveDefaultName function can use that argument:

strSaveFile = GetSaveFile(DLookup("Value", "ProgramData", "Type = 'Archive Location'"), _
                              "Archive Database", [blue]GetArchiveDefaultName(ArchType)[/blue], _
                              "Access Database", ".mdb")

...then I get the error. Not passing the argument - making the GetArchiveDefaultName function reference the cboNamePattern combobox value directly, I get the archive just as I would expect, with no errors.

Any ideas why?