Avatar of bejhan
bejhan asked on

Checking Existance of Reference/Get Database Location

How can I check if a certain reference included in my database is broken. I know there is an Application.BrokenReference property but that is for any broken reference not a specific one. The reason I want to do this is is so I can install the dll if the system does not have it.

Also, how can I get the path of the database (the folderpath not including filename).
Microsoft Access

Avatar of undefined
Last Comment
bejhan

8/22/2022 - Mon
Emil_Gray

Here is some code for references in your current db.
Option Compare Database
Option Explicit
 
Function CurrentRelations(DbName As String) As Integer
'------------------------------------------------------------------
' PURPOSE: Create English language current database relationships
'          table.
' ACCEPTS: The name of the current database as a string.
' RETURNS: The number of relationships in current database
'          as an integer.
' DbName in Function must be full path of this database.
' REQUIRES: A Reference to Microsoft DAO 3.6 Object Library
'           in the database. To make sure you have it installed
'           click on Tools/References at the top of this page.
'           If you do not see a check mark next to
'           Microsoft DAO 3.6 Object Library then check the box
'           and click OK before running the program.
'------------------------------------------------------------------
 
Dim ThisDb As DAO.Database
Dim ThisRel As DAO.Relation
Dim ThisField As DAO.Field
Dim I As Integer, cnt As Integer, RCount As Integer
Dim j As Integer
Dim ErrBadField As Integer
Dim Path As String
Dim DoesItExist As Boolean
Dim strNameObject As String
Dim objTablesQueries As AccessObject, dbsTablesQueries As Object
Set dbsTablesQueries = Application.CurrentData
Path = GetFullPath
RCount = 0
 
DoEvents
DoesItExist = False
    For Each objTablesQueries In dbsTablesQueries.AllTables
        strNameObject = objTablesQueries.Name
    If strNameObject = "tblCurrentDBRelationships" Then
    DoesItExist = True
    End If
    Next objTablesQueries
If DoesItExist = True Then
DoCmd.DeleteObject acTable, "tblCurrentDBRelationships"
End If
DoesItExist = False
DoCmd.RunSQL "CREATE TABLE tblCurrentDBRelationships (ID AUTOINCREMENT UNIQUE PRIMARY KEY, ThisRelName TEXT(255), " _
& "ThisRelTable TEXT(255), ThisRelForeignTable TEXT(255), ThisRelAttributes LONG, " _
& "ThisFieldName TEXT(255), ThisFieldForeignName TEXT(255)" & ");"
 
Set ThisDb = CurrentDb()
 
' Loop through all existing relationships in the current database.
For I = 0 To ThisDb.Relations.Count - 1
   Set ThisRel = ThisDb.Relations(I)
 
   ' Set bad field flag to false.
   ErrBadField = False
 
   ' Loop through all fields in that relation.
   For j = 0 To ThisRel.Fields.Count - 1
      Set ThisField = ThisRel.Fields(j)
 
      ' Check for bad fields.
      On Error Resume Next
      If Err <> False Then ErrBadField = True
      On Error GoTo 0
   Next j
 
   ' If any field of this relationship caused an error,
   ' do not add this relationship.
   If ErrBadField = True Then
      ' Something went wrong with the fields.
      ' Do not do anything.
   Else
      ' Try to append the relation.
      On Error Resume Next
      If Err <> False Then
         ' Something went wrong with the relationship.
         ' Skip it.
      Else
         ' Keep count of successful imports.
         RCount = RCount + 1
      End If
      On Error GoTo 0
   End If
 
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblCurrentDBRelationships ( ThisRelName, ThisRelTable, " _
           & "ThisRelForeignTable, ThisRelAttributes, " _
           & "ThisFieldName, ThisFieldForeignName ) " _
           & "VALUES " _
           & "( " & Chr$(34) & ThisRel.Name & Chr$(34) & ", " _
           & Chr$(34) & ThisRel.Table & Chr$(34) & ", " _
           & Chr$(34) & ThisRel.ForeignTable & Chr$(34) & ", " _
           & ThisRel.Attributes & ", " _
           & Chr$(34) & ThisField.Name & Chr$(34) & ", " & Chr$(34) & ThisField.ForeignName & Chr$(34) & " )"
DoCmd.SetWarnings True
 
Next I
 
' Close databases.
ThisDb.Close
 
' Return number of successful current relations.
CurrentRelations = RCount
 
 
End Function

Open in new window

SOLUTION
Emil_Gray

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rey Obrero (Capricorn1)


currentproject.Path  will give you folder location of the db.

what do you mean by specific one?
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rey Obrero (Capricorn1)

sorry Emil, did not refresh
Your help has saved me hundreds of hours of internet surfing.
fblack61
Emil_Gray

Thats OK. I have the same problem sometimes. I think the two codes I provided can be modified  to accomplish what the questioner desires.
ASKER
bejhan

Just one more question emil:

ref.FullPath returns the network path

i.e. Domain\\Folder\Folder1\Folder2

While currentproject.path returns the mapped drive

i.e. M:\Folder1\Folder2

I wanted to use these two to compare against each other. (one of my libraries is in my app folder)

Is it possible to get both of them to come out in the same format or to retrieve the actual path of M:\ from ms access?

Emil_Gray

They do appear to have the same format as far as I can tell. They just are not located in the same paths.

ref.FullPath = where the reference dll is located.

CurrentProject.Path or if using the provided function GetPath = where the current database is located. They likely are not the same in most if not all cases. For instance I added a line to the code I provided using GetPath which is the Current Database Path. The function then returned the following. You will see the reference objects are not located where the database is located.

ReferenceProperties
Name:         VBA
FullPath:     I:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
CurrentDatabasePath:        L:\C Drive Old Files\My Documents
Version:      4.0
Name:         Access
FullPath:     K:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
CurrentDatabasePath:        L:\C Drive Old Files\My Documents
Version:      9.0
Name:         ADODB
FullPath:     I:\Program Files\Common Files\System\ado\msado21.tlb
CurrentDatabasePath:        L:\C Drive Old Files\My Documents
Version:      2.1
Name:         ADOX
FullPath:     I:\Program Files\Common Files\System\ado\msADOX.dll
CurrentDatabasePath:        L:\C Drive Old Files\My Documents
Version:      2.8
Name:         DAO
FullPath:     I:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
CurrentDatabasePath:        L:\C Drive Old Files\My Documents
Version:      5.0
Name:         VBIDE
FullPath:     I:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
CurrentDatabasePath:        L:\C Drive Old Files\My Documents
Version:      5.3
Name:         stdole
FullPath:     I:\WINDOWS\system32\stdole2.tlb
CurrentDatabasePath:        L:\C Drive Old Files\My Documents
Version:      2.0
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
bejhan

In this case they are to be located in the same place, only for this specific reference though.

And I know that M:\ is mapped to Domain\\Folder\ but I want to make it dynamic, so in the future if the mapped drive is G:\ and its mapped to somewhere else this will still work.
Emil_Gray

Please post the code you now have that is working so I can better answer your question although I believe you already have the answer to your problem. I already provided this function but here it is again.


Option Compare Database
Option Explicit
 
Function GetFullPath()
   'Returns full path including file to currently opened MDB or ADP
   GetFullPath = CurrentProject.FullName
End Function
 
Function GetPath()
   'Returns the path to currently opened MDB or ADP
   GetPath = CurrentProject.Path
End Function
 
Function GetName()
   'Returns the filename of the currently opened MDB or ADP
   GetName = CurrentProject.Name
End Function

Open in new window

ASKER
bejhan

I'm afraid you still don't understand what I mean. Anyways you have answered what I asked and I don't think its fair to keep you answering questions. I will award the points, please come to the new question I have opened which is much more detailed about the problem I was trying to explain to you. As well I may not understand how references work in Access and may be over shooting my checks.

https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23426080.html
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes