database directory

[raymond3090]

hi there, is there some way for me to return the path of the directory in which the database (.mdb file) is currently stored.

I need to open an excel file from within the database. I don't know where the database and excel files will be, but I do know that they will be in the same folder. Any suggestions?

-ray

 

[ColinEssex]

Hi
Make a table called "Databases" include one field called "DBName"
Copy this code into a button OnClick.

Private Sub lblFindFiles_Click()


On Error GoTo Errorhandler

Dim varFile As Variant, objFileSearch As Object, strLookin As String
Dim MyDb As DAO.Database, MyRs As DAO.Recordset


strLookin = BrowseFolder("Please Select a Folder to find Files")

'find all the .mdb files
Set objFileSearch = Application.FileSearch
With objFileSearch
.NewSearch
.FileName = "*.mdb"
.LookIn = strLookin
.SearchSubFolders = True
.Execute

If .foundfiles.Count = 0 Then
MsgBox "No files found, please select a different location", vbInformation, "No Files Found"
'HideAll
GoTo ExitSub
End If


'Create the recordset
Set MyDb = CurrentDb()
Set MyRs = MyDb.OpenRecordset("Databases")

'add all the files to the table
For Each varFile In .foundfiles
MyRs.AddNew
MyRs("DbName") = varFile
MyRs.Update
Next varFile
End With


Errorhandler:
Select Case Err.Number
Case 5
MsgBox "Search Cancelled.", , "Search Cancelled"
'HideAll
GoTo ExitSub

Case Else
MsgBox Err.Description
End Select

ExitSub:

End Sub

It should work ok, I had to modify it a bit for you but give it a try.

Col


ps - code courtesy of Fizzio - thanks Fiz

 

[ghudson]

I know there is a system property that will give you the location of the current db but I can not find it. I do have this to offer that will also give you the location of the current db...

=Left([CurrentDb].[Name],Len([CurrentDb].[Name])-Len(Dir([CurrentDb].[Name])))

HTH

 

[raymond3090]

thanks for the responses everybody. You guys solved my problem!

What would I do without this site!!?

-ray

 

[sambo]

Here is a simple method..

Dim loc As String

loc = Application.CurrentProject.Path