Access.References.AddFromFile "Destroys" Public Parameters ????

[Giorkos]

Hi All,



I am new to this forum and I have registered to research a very specific issue.

First of all, I would like to mention that I am not an Access-VBA Developer. It just happens that at work place we have a legacy Access system that we still need to support. I am reading into access-vba, but not competent enough to tackle this particular problem, so I am here to ask for your help.

I have found snippets on the Internet for the "Late Binding" Solution to different Office-Excel Versions on users PCs.

I have managed to get excel object referencing to work with "late binding" . My scenario is as follows

1. user logs in
2. user is transferred to a "switchboard" form-menu.
3. in the OnLoad event of the switchboard form I check for any existing excel references and remove them (I have found that if i dont, it was not working properly)

If refExists("excel") Then
      Access.References.Remove Access.References("excel")
End If

which uses the following

Private Function refExists(naam As String)Dim ref As Reference
refExists = False
For Each ref In References
    If ref.Name = naam Then
        refExists = True
    End If
Next
End Function

4. It then checks for office installations using the following code (i only declare it once here, but it covers all possibilities for office 2007,2010,2013,2016 , both 32 or 64 bit and 32 or 64 bit Windows OS)

If Dir("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe") <> "" Then    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe")
End If

5. I then disable the X Close button of Access Main Application window, so that I force users to click on a "close" button on the switchboard form which in turn again checks for any excel references and removes it.

It all work fine, and we have tested the same excel file on excel 2010, 2013 and 2016 and works as expected.

At least that is what we thought.
the problem is that some other functionality has been lost, and I have managed to pinpoint the piece of code which "causes" the problem.

it seems that when a path for office installation and excel.exe is found (in my case the following)

If Dir("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe") <> "" Then    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe")
End If

it somehow "destroys" the values of some public parameters, that hold the user role.
some buttons on another form are hidden or shown according to some roles, and as the public parameter holding the role value is NULL then the buttons are not shown as expected.

If i just leave all code untouched and just comment out the above line of code where it adds a reference, it all works as expected.
I have traced (with msgbox messages) the values for the public parameter, and it exists, right before the Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe") line and right after it its NULL.

i have also created a temp parameter, assigned it the value of the public parameter before it gets NULLed and reassign it back to the public parameter after the add reference code, BUT ITS STILL NULL !!!!

one more thing to mention, is that the public parameter i am talking about that holds the role name, is placed in a MODULE if that makes a difference.

As I said, my background is not Access-VBA so you have to be lineant with me !

Thank you in advance and I hope I get some info on this matter,

George

[davegri]

I assume by Public Parameter you mean Global Variable.
If, in fact, Access does a 'restart' when references are changed, you will lose the contents of variables.
You may have to add a local table to the FE to preserve the variables.

[HiTechCoach]

To avoid all the reference issues I would recommend switching from Early Binding to Late Binding before deployment. That way you do NOT have to set a reference.

See:Late Binding in Access

[Giorkos]

Hi HitechCoach.

I have use late binding technique to add the excel reference. (or at least I guess that is what the code i used from the internet did ).

Davergi,

I originally had my excel reference dealt on the FormLoad of the login form, but then moved it to the "switchboard" form...(for some weird reason ).

The software seems to behave as expected now, as the parameter is assigned a value when the user clicks on LOG IN button. so if Indeed access "restarts" as you said when you assign an object reference, then Im in the clear, at least regarding this certain "role" parameter. However, I cannot test all the application to see if any other parameters might have been assigned before even the FormLoad of the login form.

In Access. Is there any other event that I can place my code, for assigning the excel object reference that will be run even before the login form . I mean the earliest possible event that gets triggered, so that i place my code there, to make sure no other parameters have been NULLed due to this "restart" behavior of Access... ?

Also, does anyone have any information about this "restart" behavior of Access ???

Thanks everyone !

George

[Giorkos]

Ok..i can see that the form OPEN event gets fired before the FormLoad. SO i will put my code in there...for now...

but is there somewhere else (another event) that I Can put code to run, maybe an event of the Access Application being opened and before any code for the forms for "our application" is run ?

Thanks,

George

[Giorkos]

Ok..i can see that the form OPEN event gets fired before the FormLoad. SO i will put my code in there...for now...

but is there somewhere else (another event) that I Can put code to run, maybe an event of the Access Application being opened and before any code for the forms for "our application" is run ?

Thanks,

George

Hi...

ME again,

I have searched for a bit and finally concluded to use the Autoexec macro and run my code for adding the Excel reference in there !

Thanks all for your suggestions and help !

George

[HiTechCoach]

Hi HitechCoach.

I have use late binding technique to add the excel reference. (or at least I guess that is what the code i used from the internet did ).

George,

You are NOT using Late Binding is you ever set the reference.

As soon as you create the reference you are using Early Binding.

You know you are using Late Binding if you DO NOT set a reference, in your case to Excel.

I would recommend that you look at the like I posted and use the method. This will allow you to set the reference while developing so you get intelli-sense (the only reason I set the reference). When done developing you remove the reference before compiling and deploying the front end.

Late Binding is simple. No Reference required. And all it usually only takes is a change to a few lines of code from early binding to late binding.