Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-10-24
14
Medium Priority
?
256 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 1000 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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

782 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