Function for current drive & directory

[Bob Webster]

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.

 

[raskew]

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

 

[ghudson]

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

 

[Bob Webster]

I'm impressed.

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

Thanks very much for the help with this.

 

[Shadez]

you can also use currentproject.path

 

[Travis]

Access 2000 is CurrentProject.Path

Access 97 is Application.Path

 

[Cosmos75]

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)", [b]Application.Path[/b] & "\" & 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."

 

[ghudson]

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

 

[Travis]

As stated before:

Access 2000 use CurrentProject.Path

Access 97 use Application.Path

 

[Cosmos75]

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.

 

[Travis]

What version of Microsoft Access are you using?

 

[Cosmos75]

Access 97 - Sorry, forgot to mention that..

 

[ghudson]

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