How to Get the Path to Either the Frontend or the Backend

[Steve R.]

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.

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.

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]

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.]

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]

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.

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]

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]

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

DLookup("Database","MSysObjects","Type=6 And Name='YourTableName'")

a) Linked ODBC

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

PathWithoutFilename=Left(filepath, InstrRev(filepath,"\")-1)