Ms Access CurrentProject.Path Code Alter For a Split Front End Backend Database

How can I alter this to use on a split access database?
It just returns the Front End path not the Backend.

Public Function GetDBPath() As String
    GetDBPath = CurrentProject.Path & "\"
End Function

Open in new window

LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
[ fanpages ]IT Services ConsultantCommented:
By "Backend (database)" do you mean you have two MS-Access databases, a "front-end" (with Forms), & a "back-end" with tables, with linked tables in the "front-end" to the "back-end" database?

If so &,say, for example, one of your linked tables is called [Table1], you may use something like this:

Dim strBackend_Path As String

strBackend_Path = CurrentDB.TableDefs("Table1").Connect


This will give you a string that looks something like this:

";DATABASE=C:\Database\backend.accdb"

If so, you can change the line above to read:

strBackend_Path = Mid$(CurrentDB.TableDefs("Table1").Connect, 11)

The end result will be the value C:\Database\backend.accdb" within the strBackend_Path variable.
0
 
COACHMAN99Commented:
try
Function ConnectStringOfLinkedTable(sTblName As String)
    Dim rs              As Recordset
    Dim sSQL           As String
    sSQL = "SELECT Database FROM MSysObjects WHERE Name='" & sTblName & "';"
    Set rst= CurrentDb.OpenRecordset(sSQL)
    If Not rs.EOF Then
        ConnectStringOfLinkedTable = rs!Database
    End If
    rs.Close
End Function
this should return the Full path and file name of the Source database
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
CurrentProject.FullName


Public Function GetDBPath() As String
    GetDBPath = CurrentProject.FullName  & "\"
End Function
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
COACHMAN99Commented:
author wants path for BE
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Ignore my post
0
 
Dustin StanleyEntrepreneurAuthor Commented:
THANK YOU ALL!

Public Function GetDBPath(sTblName As String)
    Dim rs              As Recordset
    Dim sSQL           As String
    sSQL = "SELECT Database"
 FROM MSysObjects WHERE Name='" & sTblName & "';"
    Set rst = CurrentDb.OpenRecordset(sSQL)
    If Not rs.EOF Then
        ConnectStringOfLinkedTable = rs!Database
    End If
    rs.Close
End Function

WHERE = Expected End of statement.

By "Backend (database)" do you mean you have two MS-Access databases, a "front-end" (with Forms), & a "back-end" with tables, with linked tables in the "front-end" to the "back-end" database?

Yes exactly!

strBackend_Path = Mid$(CurrentDB.TableDefs("Table1").Connect, 11)

Was getting it in the ball park but not exactly.


Let me post all the previous code and maybe this will help more.

Image126.Picture = GetProductImageFileNm & Forms!frmSKUsEntry!sbfrmProductImages.Form!ProductImageFileNm
  
End Sub

Public Function GetProductImageFileNm() As String
    GetProductImageFileNm = GetDBPath & "images\"
End Function

Public Function GetDBPath() As String
    GetDBPath = CurrentProject.Path & "\"
End Function

Open in new window

0
 
[ fanpages ]IT Services ConsultantCommented:
Was getting it in the ball park but not exactly.

Go on, please give me a clue what you did see in the strBackend_Path variable.

Thanks.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Go on, please give me a clue
Can't open the file'\\Sur1-PC\Sur Database\Sur Database_BEaccdbimages\'


Which it can't open a file that doesn't exist.

I need it to return:\\Sur1-PC\Sur Database\Images\
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Works Great Thanks!


Public Function GetDBPath() As String
    Dim GetDBPathh As String
    Dim I As Integer
    
    GetDBPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("Assemblies").Connect, 11)

    For I = Len(GetDBPath) To 1 Step -1
        If Mid(GetDBPath, I, 1) = "\" Then
            GetDBPath = Left(GetDBPath, I)
            Exit For
        End If
    Next

End Function

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This will include the name of the back end db.
Public Function GetDBPath() As String
    GetDBPath = Replace(CurrentDb.TableDefs("SomeLinkedTableNameHere").Connect, ";DATABASE=", "")
End Function

This is without the db name
Public Function GetDBPath() As String
    GetDBPath = Replace(CurrentDb.TableDefs("SomeLinkedTableNameHere").Connect, ";DATABASE=", "")
'Remove db name
GetDBPath = left(GetDBPath ,InStrRev (GetDBPath,"\" ))

End Function
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you DatabaseMX for the additional info. I had figured it out and closed the question just a second ago or I would have included this in the assisted answers. But anyone in the future viewing this will defiantly benefit from that info! Thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
No need for a loop :-)
0
 
Dustin StanleyEntrepreneurAuthor Commented:
No need for a loop :-)

I see Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.