Solved

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

Posted on 2016-10-24
14
161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
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…

617 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