Can anybody help with a function I can use in Access 97 to return the drive and directory of the currently open and active database (as displayed in File/database properties/general/location).

curdir looks the right function, but depending on how one opens the database it either does return the required result, or can just give the location specified for the default database folder (as in tools/ options/general).

Hope someone can help.

Give this a try...
Function dbpath() As String

Dim db As DATABASE
Dim lngloop As Long
Dim strName As String
Dim NL

NL = Chr(13) & Chr(10) ' Define newline.
Set db = CurrentDb()
strName = db.Name

lngloop = Len(strName)

While Mid(strName, lngloop, 1) <> "\"

lngloop = lngloop - 1
Wend
db.Close
Set db = Nothing
dbpath = "Path: " & Left(strName, lngloop) & NL & "Name: " & Right(strName, Len(strName) - lngloop)
End Function

If you want the path and the name of the current database, then use:
CurrentDb.Name

If you want just the name of the current database, then use:
Dir(CurrentDb.Name)

Or if you want the path to the current database, then use:
Left(CurrentDb.Name,Len(CurrentDb.Name)-Len(Dir(CurrentDb.Name)))

HTH

I'm impressed.

CurrentDb.Name etc is very simple and does the trick nicely.

Thanks very much for the help with this.

you can also use currentproject.path

Access 2000 is CurrentProject.Path

Access 97 is Application.Path

Private Sub cmdSnapshot_Click()
On Error GoTo Err_cmdSnapshot_Click

Dim stDocName As String

stDocName = "rptNewCoatingLetter"

Dim ReportName As String
Dim ReportDate As Date
Dim FileName As String


ReportName = "Letter"
ReportDate = Format(Now(), "mm-dd-yyyy")
FileName = ReportName & " - " & ReportDate

Dim sngStart As Variant
Dim sngEnd As Variant
Dim sngElapsed As Variant

MsgBox "File will be saved as " & FileName
MsgBox Dir(CurrentDb.Name)

' Get start time.
sngStart = Timer

stDocName = "rptNewLetter"
DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)", Application.Path & "\" & FileName & ".snp", True

' Get end time.
sngEnd = Timer

' Elapsed time.
sngElapsed = Format(sngEnd - sngStart, "Fixed")
MsgBox ("The report took " & sngElapsed & " seconds create as a snapshot file.")

Exit_cmdSnapshot_Click:
Exit Sub

Err_cmdSnapshot_Click:
MsgBox Err.Description
Resume Exit_cmdSnapshot_Click

End Sub


I have this code in an Access 2000 database but with CurrentProject.Path instead of Application.Path. Application.Path ins't working though?? Neither does replacing it with Left(CurrentDb.Name,Len(CurrentDb.Name)-Len(Dir(CurrentDb.Name)))

I get an error message that says "Member or data member not found."
:confused:

Sounds like you need some help debugging. ;)

What value do you get if you add this line...
msgbox Application.Path & "\" & FileName & ".snp"

Does hard coding the file location work?
DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)", "C:\Test.snp", True

HTH

As stated before:

Access 2000 use CurrentProject.Path

Access 97 use Application.Path

Originally posted by Travis
As stated before:

Access 2000 use CurrentProject.Path

Access 97 use Application.Path

Travis,

When I try to use Application.Path I get an error message that says "Member or data member not found".

I tried this

MsgBox Application.Path

to see what it would give me.

ghudson,
Does hard coding the file location work?
DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)", "C:\Test.snp", True
- Yes, it does. But I want one of two things

1) File path to be save as a relative path and I specify the file name

OR

2) User chooses file path and I specify the Default File name and the user can change it is they want to.

What version of Microsoft Access are you using?

Access 97 - Sorry, forgot to mention that..

Cosmos75,

What happens when you try...

DoCmd.OutputTo acReport, stDocName, "SnapshotFormat(*.snp)", Left(CurrentDb.Name,Len(CurrentDb.Name)-Len(Dir(CurrentDb.Name))) & "\" & FileName & ".snp", True

This simple formula should work for all version of Access to return the location of the active db...
Left(CurrentDb.Name,Len(CurrentDb.Name)-Len(Dir(CurrentDb.Name)))

HTH