Question about a Path in code

J

Joe Cilinceon

I have a routine that backs up the back end from the front end. It uses a
line as follows to set the path and file name:

DestinationFile = "C:\Handi\Backup\Handi_be" & DateToStr(Date) & ".mdb"

Now if I remember (It has been a very long time since I messed with paths)
that there is a way to write a path to copy a file to a sub folder with out
hard coding the main folder name.

For example perhaps something like:

DestinationFile = "..\Backup\Handi_be" & DateToStr(Date) & ".mdb"

Where you would have D:\WhatEverFolder\Backup........

I hope that is clear as it has been a long day.
 
D

Douglas J. Steele

Assuming that you want to refer to a subfolder of the current folder, you
can't be sure what the current directory is.

Far better is to determine the "base folder" programmatically. For instance,
assuming C:\Handi\ is supposed to be the folder where the MDB file exists,
use

DestinationFile = Application.CurrentProject.Path & _
"\Backup\Handi_be" & DateToStr(Date) & ".mdb"

if you're using Access 2000 or newer, or

DestinationFile = Left(CurrentDb.Name, Len(CurrentDb.Name) -
Len(Dir(CurrentDb.Name))) & _
"Backup\Handi_be" & DateToStr(Date) & ".mdb"

for earlier versions.
 
J

Joe Cilinceon

Thanks again Douglas (send me a bill for the consulting job, vbg) that is
exactly what I wanted.
 
J

Joe Cilinceon

Oh yes I'm used Office XP Developers and 2002 file formats. Now this is
weird, using what you said before I changed the SourceFile line from:
SourceFile = "C:\Handi\Handi.mdb"
TO
SourceFile = Application.CurrentProject.path &"\Handi.mdb"

And now get a message and debug window that says I can't compact/repair with
a macro ......... Change it back and it works fine. I listed the whole piece
of code below.

The DestinationFile line works perfectly. go figure

********
Private Sub ShutDown_Click()
'Remove Reservations over 6 days old
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelReservations", acNormal, acEdit
DoCmd.SetWarnings True

'Compact Database
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

'Filecopy the Handi_be file to the backup folder and add the date to the
file name.
Dim SourceFile, DestinationFile

SourceFile = "C:\Handi\Handi_be.mdb" ' Define source file name.
'DestinationFile = "C:\Handi\Backup\Handi_be" & DateToStr(Date) & ".mdb"
' Define target file name.

'SourceFile = Application.CurrentProject.path & "\Handi.mdb"
DestinationFile = Application.CurrentProject.path & "\Backup\Handi_be" &
DateToStr(Date) & ".mdb"

FileCopy SourceFile, DestinationFile ' Copy source to target.

' Close Handi and go to Windows
DoCmd.Quit

End Sub
**********
 
D

Douglas J. Steele

Have you tried single-stepping through the code to see exactly where it's
failing?

You're not setting a value for SourceFile until after the compact has been
completed, so I can't see how changing that code will have any impact on the
code.

Couple of minor things, btw.

Dim SourceFile, DestinationFile

should be

Dim SourceFile As String, DestinationFile As String

And since you're trying to avoid hardcoding, why not use

SourceFile = Application.CurrentProject.Path & "\Handi_be.mdb"

instead of

SourceFile = "C:\Handi\Handi_be.mdb"
 
J

Joe Cilinceon

It must have been the Dim statement because as soon as I changed it the
SourceFile worked with no errors. The code snippet I posted had the
SourceFile = Application.CurrentProject.Path & "\Handi_be.mdb" line ' out
because I had changed it back and test then posted it. It is now working
perfectly again with the Application.CurrentProject.Path. Thanks again
Douglas you have really been a big help to me today, it is greatly
appreciated.
 
Top