ACCESS VBA referenaces problem 1

[jaabaar]

Hi

I am trying to check if there is any broken references when access starts as the reference changes in different version of Access.

The code works through ok but it does not remove the missing/broken references. When I debug nothing is shown it’s just blank in the debug.print.

Any help and advice would be much appreciated'

Sub AddReference()

' To add a reference to the project using the GUID for the
'reference library
Dim strGUID As String, theRef As Reference, i As Long
Dim test As Boolean
    
Update the GUID you need below.
strGUID = "{00020813-0000-0000-C000-000000000046}" 'Excel Object library
    
'Set to continue in case of error
On Error Resume Next

    'Remove any missing references
    For i = Access.References.Count To 1 Step -1
        Set theRef = Access.References.Item(i)
        If theRef.IsBroken = True Then
            Access.References.Remove theRef
            Debug.Print theRef.Name
            Debug.Print "GUID: " & theRef
        End If
    Next i


'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear
     
'Add the reference
'unfortunately the add ref below produces error if ran in debug mode in first run:
'ERROR: can't enter break mode at this time. 
'is this error normal

 'The 0,0 should pick the latest version installed on that machine
 Application.References.AddFromGuid Guid:=strGUID, Major:=0, Minor:=0

    
    
     
'If an error was encountered, inform the user
Select Case Err.Number
    Case Is = 32813
         'Reference already in use.  No action necessary
    Case Is = vbNullString
         'Reference added without issue
    Case Else
         'An unknown error was encountered, so alert the user
        MsgBox "A problem was encountered trying to" & vbNewLine _
        & "add or remove a reference in this file" & vbNewLine & "Please check the " _
        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
    End Select
    On Error GoTo 0
End Sub

Thank you in advance

 

[1DMF]

If you are trying to enable your app to work with various versions of MS Office, have you considered late bindings?

That way you don't have a reference to a specific library to get broken.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[jaabaar]

Hi

How would I do that do I do that after the access starts. don't I need to remove broken links.

Thanks

 

[1DMF]

No, you firstly remove all referencing in the code editor GUI to Office xxx or any specific component version (Except for the Access reference of course!).

Then you use late binding syntax when creating office objects.

1. Always define your office variables as simply Object
2. Instantiate them (set) them as need to the Office object required.

' Use current installed version of Word
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")

' Use current installed and open version of Outlook
Dim OutlookApp As Object
Set OutlookApp = GetObject(, "Outlook.Application")

' Use current installed version of Excel
Dim ExcelApp As Object
Set ExcelApp = CreateObject("Excel.Application")

You can see how the late binding works, instead of declaring your variable as a specific object type, based on a version referenced in references, you are binding a generic Object at the point of use to what ever version is installed on the users PC.

OK, you lose some intellisense but you gain drastically in a multi-versioned office environment. This way as long as a version of Office is installed on the users PC, the app doesn't care what version it is and just uses it.

Obviously you need to ensure you perform generic operations not version specific ones, or you need to consider handling potential errors in doing so and having graceful fall-back code, the same principle used for developing websites in a multi-OS / multi-browser environment.

Though I have only used this in an environment where the versions ranged between 2003 / 2007 / 2010 / 2013 and never needed to use any functionality that was specific to a particular version of Office.







"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[jaabaar]

Hi

are you telling me if I remove the linking with GUI and add the below in global decoration. don't I need to do more? just reference ExcelApp when ever I need when doing excel automation.


' Use current installed version of Excel
Dim ExcelApp As Object
Set ExcelApp = CreateObject("Excel.Application")

Access will set the needed reference for excel.

Sorry if I sound too green. hope above is clear

Thanks in advance

 

[1DMF]

are you telling me if I remove the linking with GUI and add the below in global decoration. .... just reference ExcelApp when ever I need when doing excel automation.

That's exactly what I'm telling you, though I wouldn't recommend using a global declaration, just local var as required at point of use.

Once your process has finished, ensure you null it (dereference it) (which is best handled via the MS Access error handling paradigm)

Public Sub MySub()

On Error GoTo Error_MySub

Dim ExcelApp As Object
Set ExcelApp = CreateObject("Excel.Application") 
[highlight #FCE94F]
... do what ever with Excel object[/highlight]

Exit_MySub:

    ' clear object references and exit
    Set ExcelApp = nothing
    Exit Sub

Error_MySub:

    MsgBox("There was the following error : " & Err.Description)
    Resume Exit_MySub

End Sub

This is just an example, but you should get the gist.

Just remember if you have different users on different versions of MS Office and you are developing using a specific version of Access in your references, the way to resolve this conflict is to roll out your application as an ACCDR and have everyone run it with the relevant runtime. In my case I devel in MS Access 2010 and rollout MS Access 2010 Runtime along with my application ACCDR.

This way my users don't need the full version of Office with Access to run my application, there is no Access version conflict and my apps work with any version of Office when interacting with Outlook, Word or Excel.

I'm also assuming you already have front end application / back end data separation implemented.






"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[strongm]

>Set ExcelApp = nothing

If you are not running any explicit object clear up code there really isn't a necessity to do this. Exiting the sub will decrement the object reference counter for you as soon as it goes out of scope.

 

[1DMF]

Hi Mike,

I thought is was considered good practice to nullify object references?

I except your point as to scope, is this really a total wasted line of code?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

Well having done a little research, there is a school of thought that garbage collection in MS Access especially isn't perfect, and assuming objects will be released once out of scope is not ideal and so it is considered good practice to explicitly set them for garbage collection.

One assumes it's the old addage

It's better to have it and not need it, than to need it and not have it.

I guess it can't hurt?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[jaabaar]

Thank you so much I am back on track much appreciated.

 

[strongm]

Nope. Nothing to do with Access cleaning up. The 'school of thought' is working on bad information and on misunderstanding how COM works originating way back in the dawn of COM history (e.g. totally incorrect comments such as '[Access] historically had problems with not correctly updating the reference counts', and the idea that COM does garbage collection; it doesn't)

X=Nothing and X going out of scope have EXACTLY the same affect and COM treats them EXACTLY the same: the object pointer is deleted, and this in turn causes the the Release method on the object's iUnknown interface to be called. This decrements the objects reference count by 1. When the reference count (on all interfaces to the object from all clients) reaches 0 the object is deallocated immediately. This is COM behaviour, not Access, not VBA.

However, the Release method does not do any explicit cleanup (e.g to avoid orphaning an object through circular references). Often classes that need cleanup will have a cleanup method that should be called before giving COM the opportunity to deallocate the object. And it is this sort of scenario that urban legend incorrectly suggests that setting the object to Nothing will fix. It doesn't.

Here's a simple example that uses a self-reference to allow orphaning. Create a class called Widget:

[blue]Option Explicit

Private mvarMyself As Widget '
Public Property Set Myself(ByVal vData As Object)
    Set mvarMyself = vData
End Property

Public Property Get Myself() As Object
    Set Myself = mvarMyself
End Property

Private Sub Class_Initialize()
    MsgBox "Widget init!!"
    [green]'Set mvarMyself = Me 'if you really want to be silly ...[/green]
End Sub

Private Sub Class_Terminate()
    MsgBox "Widget terminate!!!"
End Sub

Public Sub ExplicitCleanUp()
    Set mvarMyself = Nothing
End Sub [/blue]

And then we can play with the object as follows:

[blue]Public Sub Master()
    Example1
    [green]' On exiting the sub we don't see wombat terminating, must mean simply letting the object go out of scope is broken. 
    ' And we are orphaned, since we no longer have any pointer to the object, but the object remains in existence ... (it'll terminate when the app is closed)[/green]
    Example2
    [green]' ... oh dear, no, no termination seen; setting wombat equal to Nothing doesn't fix it at all. So much for urban legend.[/green]
    Example3
    [green]' Ah, calling the wombat's explicit cleanup routine before setting wombat to nothing fixes things[/green]
    Example4
    [green]' Oh, but so does simply letting wombat go out of scope. No difference ...[/green]
End Sub

Public Sub Example1()
    Dim wombat As Widget

    Set wombat = New Widget
    Set wombat.Myself = wombat
End Sub

Public Sub Example2()
    Dim wombat As Widget

    Set wombat = New Widget
    Set wombat.Myself = wombat
    Set wombat = Nothing [green]' there, that'll do the trick ...[/green]
End Sub

Public Sub Example3()
    Dim wombat As Widget

    Set wombat = New Widget
    Set wombat.Myself = wombat
    wombat.ExplicitCleanUp [green]' Note you could manually cleanup by setting the Myself property to Nothing[/green]
    Set wombat = Nothing
End Sub

Public Sub Example4()
    Dim wombat As Widget

    Set wombat = New Widget
    Set wombat.Myself = wombat
    wombat.ExplicitCleanUp
End Sub[/blue]

 

[1DMF]

As always Mike, you are a scholar and a gentleman.

Thanks for explaining this and showing a working example of an object reference referencing itself internally.

So when you set an object reference variable to null, you aren't making the value of the object null, like you are a value type.

So if elsewhere another variable is also pointing to the reference, the object is still 'live' and stays in memory.

Is there no way to say in code 'null the memory being used', ie destroy the object, not simply deference it making my variable null?











"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[strongm]

Yep, that's pretty much it.

 

[PHV]

1DMF, I'm afraid you confuse Null and Nothing in your last post.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[strongm]

Nothing is pretty much just VB's special word for a null reference pointer, so I'd argue 1MDF is not as confused as you are suggesting.

 

[1DMF]

I'm in the middle of porting to C#, I see

VB Nothing = C# null

Is this incorrect?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[strongm]

No, it is not incorrect.

 

[1DMF]

OK, thanks for clarifying, had me questioning my sanity for a minute!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music