Solved

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

Posted on 2016-10-24
14
36 Views
Last Modified: 2016-10-24
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

0
Comment
Question by:Dustin Stanley
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 250 total points
ID: 41857779
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 250 total points
ID: 41857781
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
 
LVL 75
ID: 41857785
CurrentProject.FullName


Public Function GetDBPath() As String
    GetDBPath = CurrentProject.FullName  & "\"
End Function
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41857788
author wants path for BE
0
 
LVL 75
ID: 41857789
Ignore my post
0
 

Author Comment

by:Dustin Stanley
ID: 41857797
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41857800
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Dustin Stanley
ID: 41857804
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
 

Author Comment

by:Dustin Stanley
ID: 41857815
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
 

Author Closing Comment

by:Dustin Stanley
ID: 41857817
Thank you!
0
 
LVL 75
ID: 41857818
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
 

Author Comment

by:Dustin Stanley
ID: 41857819
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
 
LVL 75
ID: 41857823
No need for a loop :-)
0
 

Author Comment

by:Dustin Stanley
ID: 41857827
No need for a loop :-)

I see Thank you!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now