How to Get the Path to Either the Frontend or the Backend (1 Viewer)

Status
Not open for further replies.

Steve R.

Retired
Local time
Today, 08:09
Joined
Jul 5, 2006
Messages
4,617
Databases get moved around. Consequently they have to be designed to (automatically) adapt to the new location. For the Access frontend obtaining the new path is relatively straitforward.

Code:
strDataBasePath = CurrentProject.Path

Recently, I had to relocate the backend so I had to find out how to obtain the path to the backend. After some searching, I found the code below posted by JANR.

Code:
strBackEndPath = [URL="http://www.access-programmers.co.uk/forums/showpost.php?p=1063592&postcount=4"]Mid(Currentdb.TableDefs("NameOfOneLinkedTable").Connect, 11)[/URL]

Additional information is available here: Get the Path to the Database (.mdb) File
 

boblarson

Smeghead
Local time
Today, 05:09
Joined
Jan 12, 2001
Messages
32,059
What if you have more than one backend (I have databases at work which use an ODBC SQL Backend as well as 5 separate Access database backends). Not so simple then. It would need to be a little more robust. I've been working on something to do that for us here but I keep getting corrupt files when doing it, so I'll post when I have something but it has yet to be completely successful.

So, just thought I should point out that the code referenced is not necessarily going to work for everyone. It will with a single source though.
 

Steve R.

Retired
Local time
Today, 08:09
Joined
Jul 5, 2006
Messages
4,617
What if you have more than one backend (I have databases at work which use an ODBC SQL Backend as well as 5 separate Access database backends). Not so simple then. It would need to be a little more robust. I've been working on something to do that for us here but I keep getting corrupt files when doing it, so I'll post when I have something but it has yet to be completely successful.

So, just thought I should point out that the code referenced is not necessarily going to work for everyone. It will with a single source though.
Understood. Thanks for pointing out those short-comings. Our IT department is re-configuring our network, so I took the back-end off the file-server and placed it on my "C" drive - hence this post. I have been negotiating, for while, with our IT department to get the back-end onto SQL Server. Since it is a very small database and uses Access as the front-end they seem to have "issues". Hopefully, I can eventually overcome this hurdle.
 

irish634

Registered User.
Local time
Today, 08:09
Joined
Sep 22, 2008
Messages
230
What if you have more than one backend ....

I've used this bit of code (below) successfully over the years. I can't seem to find it, but I also adapted it to loop and return the path to all tables, insert into a temp table, and extract the paths that way.. I'll keep looking for it... but it was for a db that had 2 access backends. I don't remember any corruption issues (not to say there weren't any)

Anyhow here's what I use for the back end path.
Code:
Public Function GetBackEndPath() As String

    Const Attached = dbAttachedTable Or dbAttachedODBC
    Set DBS = DBEngine(0)(0)              
    For Each TBL In DBS.TableDefs
        ' Gets the back end full path
        If (TBL.Attributes And Attached) <> 0 Then
            GetBackEndPath = Mid(TBL.Connect, 11, Len(TBL.Connect) - 10)
        End If
    Next
    Set DBS = Nothing                      
    
'Error Handler removed
    
End Function
 

Cowboy_BeBa

Registered User.
Local time
Today, 20:09
Joined
Nov 30, 2010
Messages
188
Hi

Sorry to resurrect an old thread, i just found this and it helped me out immensely so i firstly just wanted to say thanks to the OP

Also a bit curious about something, i have a table that stores the folderpath of the back end on it (as well as a few other important variables)

This code gets me the folder path and the filename of the back end, but i was wondering if there was something similar to just get the path?
 

isladogs

MVP / VIP
Local time
Today, 12:09
Joined
Jan 14, 2017
Messages
18,186
Just found this old thread via a link suggested by Gina Whipp in another forum

The function in post #4 gives the correct path for a linked Access table but cuts off the first 5 letters (DRIVE) for a linked SQL table

Alternatively you could use the MSysObjects table

a) Linked Access
Code:
DLookup("Database","MSysObjects","Type=6 And Name='YourTableName'")

a) Linked ODBC
Code:
DLookup("Connect","MSysObjects","Type=4 And Name='YourTableName'")

If that looks useful I'll happily create a function for this approach

Hopefully Cowboy beba has an answer to his question from post #5 now but in case its useful to others

Code:
PathWithoutFilename=Left(filepath, InstrRev(filepath,"\")-1)
 
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom