Access97 - str vars not recognized but hardcoded string is... HELP

[Mr. Mort]

I am having a problem...
I am using Access97 and trying to use a database I downloaded to do
scheduled compacts from a list of databases.
The dbengine.compactdatabase method works when I hardcode a directory
but when I assign variables it returns 3055 invalid file name.
I have verified the string values are the same as what I have
hardcoded via the Debug window.
Any suggestions? Here is the code...

Thanks in advance!
‘I have ensured with the following via debug window that the strings I
inserted for testing are the same as the values of the variables during
execution. It works with the strings, but not the variables. (problem
encountered at the code marked between the ‘**************)



Function ng_Step2(pstrFileName As String) As Integer
'* Compact database file
Dim strTempFileName As String
Dim strFileNamePart As String
Dim strFolderPart As String
Dim strFileName As String
Dim intRtn As Integer

On Error Resume Next

intRtn = False

If IsNull(pstrFileName) Or pstrFileName = "" Then
GoTo exit_section
Else
strFileName = pstrFileName
End If

'--------------------
Dim rx As Integer
Dim rxa As String
Dim rxw As Integer
rx = 1
rxw = 0
Do While rx <> 0 ' find last \
rx = InStr(rxw + 1, strFileName, "\")
If rx <> 0 Then
rxw = rx
End If
Loop
strFolderPart = Left(strFileName, rxw)
strFileNamePart = Right(strFileName, Len(strFileName) - rxw)
'MsgBox (strFolderPart & ":" & strFileNamePart)

'--------------------



If Len(strFileNamePart) > 4 Then
strTempFileName = "tmpCAL" & Left(strFileNamePart, Len(strFileNamePart)
- 4) _
& Format(Now, "yyyymmddhhnnss") & ".mdb"
Else
strTempFileName = "tmpCAL" & Format(Now, "yyyymmddhhnnss") & ".mdb"
End If

If strFileName = "C:\MyFolder\MyFileName.mdb" Then
DBEngine.CompactDatabase strFileName, strFolderPart & strTempFileName, ,
";pwd=MyPassword"
Else
‘**************
DBEngine.CompactDatabase strFileName, strFolderPart & strTempFileName
‘ This line works DBEngine.CompactDatabase
"F:\personal\stuff\Test\junk.mdb",
"F:\personal\stuff\Test\tmpCALjunk20041202091533.mdb"
‘**************
End If
If Err <> 0 Then
ng_UpdateHistoryRec "message = message & 'Error during database compact
= " _
& Err & "; ' "
Err.Clear
GoTo exit_section
End If

Kill strFileName
If Err <> 0 Then
ng_UpdateHistoryRec "message = message & 'Error deleting original
database = " _
& Err & "; ' "
Err.Clear
GoTo exit_section
End If

Name strFolderPart & strTempFileName As strFileName
If Err <> 0 Then
ng_UpdateHistoryRec "message = message & 'Error renaming temp compacted
database to original database name = " _
& Err _
& " temp database name is " & strFolderPart & strTempFileName & "; ' "
Err.Clear
GoTo exit_section
End If

intRtn = True

exit_section:
On Error Resume Next
ng_Step2 = intRtn
End Function

 

[Brendan Reynolds]

Just a guess, but perhaps you need to put quotes into the variables ...

strFileNname = Chr$(34) & strFileName & Chr$(34)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.