list references using VBS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to programmically list references for a 97-2002 Access
database? Goal is not to fix or alter them, merely list them. Thanks
 
Using VBS (as your Subject says), or VBA (which is what Access uses)?

Using VBA, it's:

Dim refCurr As Reference

For Each refCurr In Application.References
If refCurr.IsBroken = False Then
Debug.Print "Name: " & refCurr.Name & " is found at " &
refCurr.FullPath
Else
Debug.Print "You have a broken reference to GUID " & refCurr.GUID
End If
Next refCurr

(Note that if the IsBroken property is True, Microsoft Access generates an
error when you try to read the Name or FullPath properties.)

In VBS, it would be something like:

Dim objAccess
Dim refCurr
Dim strOutput

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\MyFolder\MyDatabase.mdb"
For Each refCurr In objAccess.References
strOutput = strOutput & refCurr.Name & vbCrLf
Next

MsgBox strOutput

objAccess.CloseCurrentDatabase
Set objAccess = Nothing
 
I'm not an mVP but this is what I wrote for myself not very good documentation
on how to do this. I created a table called "System References"
in my database with the fields:

Ref_ID Autonumber (primary Key)
Ref_Name Text(50)
Ref_Path text(240)
Ref_Kind Long
Ref_IsBroken Text(50)

Here is the code to fill the empty table:

Public Sub ListSystemReferences()
Dim ref As Reference
Dim lngCount As Long
Dim rsRefs As ADODB.Recordset

Set rsRefs = New ADODB.Recordset
With rsRefs
.ActiveConnection = CurrentProject.AccessConnection
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.SOURCE = "SELECT * From [System References];"
.Open Options:=adCmdText

For Each ref In Application.References
lngCount = lngCount + 1
.AddNew
!ref_Name = ref.Name
!ref_Path = ref.FullPath
!ref_Kind = ref.Kind
!ref_IsBroken = ref.IsBroken
.Update
Next ref
.Close

End With
Set rsRefs = Nothing

End Sub
Good coding...
kjh
 
You mean you don't write code in VBS? Anyway, two great responses, each of
which solves my question. I looked in the Sys tables, thinking that is where
the application settings should be stored, but did not find anything that
made sense. Where is this information persisted? Thanks again
 
Problem with that, Ken, is that it's going to error out if the reference is
broken.

As I said elsewhere in this thread, Access generates an error if you try to
refer to either the Name or FullPath properties of the Reference object when
the IsBroken property is true.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ken Higgins said:
I'm not an mVP but this is what I wrote for myself not very good documentation
on how to do this. I created a table called "System References"
in my database with the fields:

Ref_ID Autonumber (primary Key)
Ref_Name Text(50)
Ref_Path text(240)
Ref_Kind Long
Ref_IsBroken Text(50)

Here is the code to fill the empty table:

Public Sub ListSystemReferences()
Dim ref As Reference
Dim lngCount As Long
Dim rsRefs As ADODB.Recordset

Set rsRefs = New ADODB.Recordset
With rsRefs
.ActiveConnection = CurrentProject.AccessConnection
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.SOURCE = "SELECT * From [System References];"
.Open Options:=adCmdText

For Each ref In Application.References
lngCount = lngCount + 1
.AddNew
!ref_Name = ref.Name
!ref_Path = ref.FullPath
!ref_Kind = ref.Kind
!ref_IsBroken = ref.IsBroken
.Update
Next ref
.Close

End With
Set rsRefs = Nothing

End Sub
Good coding...
kjh

stevemets said:
Is there a way to programmically list references for a 97-2002 Access
database? Goal is not to fix or alter them, merely list them. Thanks
Click to expand...
Click to expand...
 
Back
Top