Solved

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

Posted on 2016-10-24
14
64 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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