We help IT Professionals succeed at work.

Retrieve Back-End Database path

shambalad
shambalad asked
on
398 Views
Last Modified: 2012-06-27
I am looking for an easy way to retrieve the path to the Back-End database.
Thanks,
Todd
Comment
Watch Question

Author

Commented:
Probably should add that I will be using a default linked table named 'tblOpenLink'.
CERTIFIED EXPERT
Top Expert 2016

Commented:


      Dim strTest As String, db As DAO.Database
      Dim td As DAO.TableDef
      Set db = CurrentDb
      For Each td In db.TableDefs
         If Len(td.Connect) > 0 Then   ' Is a linked table.
         'get Backend path

            sBEpath = Mid(td.Connect, InStr(td.Connect, "=") + 1)
            msgbox  sBEpath
            exit for
         end if
     next

Commented:
You can query the MSysObjects table for your tablename in the ForeigenName Field and retrieve the Database field which will have the full path name of the back end mdb

Author

Commented:
Capicorn1 -
I was trying a variation on your suggestion, but getting an error (see snippet).
Public Sub GetBackEndPath()
   Dim strPath As String
   Dim tdf As TableDef
   Set tdf = CurrentDb.TableDefs("tblOpenLink")
   
   ' Runtime error 3420 'Object invalid or
   ' no long set on following instruction
   strPath = Mid(tdf.Connect, InStr(tdf.Connect, "=") + 1)
   
   Debug.Print strPath
End Sub

Open in new window

CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That works.
Just out of curiousity. Why do I have to go through the additional step of defining and assigning a database object?
CERTIFIED EXPERT
Top Expert 2016

Commented:
from the VBA window > Help
type currentdb
open currentdb method for full explanation

Author

Commented:
Thanks -
In thinking about this last question, I figured it was time to pull out my 'Developer's Handbook' by Ken Getz et al.
At the risk of being too simplistic:
For most objects referenced using 'Currentdb', Access essentially creates an explicit reference to the current database that is only valid for that particular line of code. Once that line has executed, Access 'closes' the reference. Hence subsequent instructions that refer to the object will fail. A notable exeception to this rule is the 'Recordset' object. Here Access maintains the reference to the current database.

Author

Commented:
RgGray3 - I just wanted to acknowledge that your suggestion works perfectly fine, too. Capricorn1 was there first, though, and I will be using his method.
Thanks,
Todd
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.