Current Path Of BackEnd

[PC User]

I have a split database and I need to call up the current path of the
back end and I'm having trouble with the code. Can someone help?
================================
Public Function CurrentBEDir() As String
Dim strBEPath As String
Dim strBEFile As String
Dim Db As DAO.Database
Set Db = CurrentDb()
strBEPath = "SELECT DISTINCT MSysObjects.Database FROM MSysObjects
" & "WHERE MSysObjects.Type=6 ;"
CurrentDb.Execute strBEPath, dbFailOnError
strBEPath= Dir(CurrentDb)
CurrentBEDir = Left$(strBEPath, Len(strBEPath) - Len(strBEFile))
End Function
================================

Thanks,
PC

 

[Douglas J. Steele]

You can't run SELECT queries like that.

Try:

Public Function CurrentBEDir() As String
Dim varBEFile As Variant

varBEFile = DLookup("Database", "MSysObjects", "Type=6")
If IsNull(varBEFile) Then
CurrentBEDir = "***None***"
Else
CurrentBEDir = Left(varBEFile, Len(varBEFile) - Len(Dir(varBEFile)))
End If

End Function

Note, though, that it's possible for a database to be linked to more than
one BE. This code won't identify that: it'll simply return the first path it
comes to.

 

[PC User]

I got a little farther in debugging this function, but I'm now stuck at
finding the file name from strBEFile. This function now only finds the
full path, but I need to find the file name to delete it from the path
and get only the directory.
=========================
Public Function CurrentBEDir() As String
Dim Db As DAO.Database
Dim rst As DAO.Recordset
Dim strBEPath As String
Dim strBEFile As String
Dim strSQL As String
Set Db = CurrentDb()
strSQL = "SELECT DISTINCT MSysObjects.Database FROM MSysObjects " &
_
"WHERE MSysObjects.Type=6 ;"
Set rst = Db.OpenRecordset(strSQL)
strBEPath = rst!Database
CurrentBEDir = Left$(strBEPath, Len(strBEPath) - Len(strBEFile))
End Function
=========================
Any help would be appreciated.

Thanks,
PC

 

[PC User]

Thanks Doug,
You must have posted your reply the same time as I posted my
second post. Yes, you are right that a database can have more than one
link to its tables. In my design, though, I only use one link. I'll
save your thought for another database. Your idea of using DLookup(),
looks much simpler than mine. In my function I get the full path that
includes the file name. I need a generic way to deterimine the file
name and I can use a formula to delete it from the full path to get the
current directory.

Thanks,
PC

 

[Douglas J. Steele]

Did you try my function? All it returns is the path.

 

[PC User]

You function work great. I finally found the solution and here it is.
=====================
Public Function CurrentBEDir() As String
Dim Db As DAO.Database
Dim rst As DAO.Recordset
Dim fso As FileSystemObject
Dim strBEPath As String
Dim strBEFile As String
Dim strSQL As String
Set Db = CurrentDb()
strSQL = "SELECT DISTINCT MSysObjects.Database FROM MSysObjects " &
_
"WHERE MSysObjects.Type=6 ;"
Set rst = Db.OpenRecordset(strSQL)
strBEPath = rst!Database
Set fso = New FileSystemObject
strBEFile = fso.GetFileName(strBEPath)
CurrentBEDir = Left$(strBEPath, Len(strBEPath) - Len(strBEFile))
End Function
===================================
Now I can shorten the code by using your function. Thank for your
help.

PC

 

[Douglas J. Steele]

There's no need to introduce FSO into the fray: all it's going to do is
increase the size of your application.

To get the file name when you have a complete path, you can use
Dir(strBEPath). That assumes that you can actually access the file (but
then, so does your FSO approach). If you cannot access the file for some
reason, you can always use InStrRev to find the last \ in the path.

I don't for the life of me understand your reluctance to use the function I
posted, given how much smaller it is than yours!

 

[PC User]

Your right Doug. Your function is a lot shorter than mine and works
exactly the same; so I'm going to use yours to lessen the bulk of my
programming. My code can get a little bulky at times.

Thanks,
PC