VBA References

[emmahope206]

Hi All,

I'm in a sticky situation, I work for a massive global company and have an IT department that is impenetrable. The situation is as follows:

We have a 2013 Excel workbook connected to a 2013 Access database* (using MS Query), most of us are on Windows 7 (handful on Windows 10 but upgraded from Win7 or Office 365 but upgraded from Office 2013). Excel has a VBA reference to "Microsoft Office 15.0 Object Library" & it works fine for all of these people.

*It is actually about 60 different Access databases, the Excel workbook picks the right one depending on a combination of drop down options the user selects in Excel plus a password they add.

Out new starters are being added with Windows 10 and Office 2013 and for some reason (unknown & unfathomable**), IT are setting this up with a Office16 folder with a totally different C drive path (I believe the Office15 folder does exist but does not contain the appropriate DLLs). (**possibly getting ready for Office365 upgrade, possibly just because they seem to be there to drive us crazy!)

We are not allowed to amend either the Office15 or Office16 folder ourselves and at one point, IT were copying the DLLs over from the one folder to the other but are now refusing due to the number of new starters we have (100s) & now we cannot get them to engage with us at all.

I have tried to recreate the issue but cannot as I have the correct DLLs in the correct folders. I have also tried to programmatically add the references to the correct folders but have had little success for two reasons. 1. the end users are not capable of updating the security settings to allow the 'trust access to the VBA project object model' (they tend to be junior, very new & often there are language barriers) and 2. as I cannot see or recreate the issue I can't even be sure if I am getting the path right. (as I can't recreate it, I can't even work out the GUID etc!).

I am using MSQuery and this methodology as the Access databases MUST be passworded and encrypted as they contain personal data and this can only be visible to certain people with certain levels of access.

I have tried copying the DLLs from my c drive into a shared location and re-pointing the VBA to these, thinking this would help but the VBA project does not understand this.

I'm sorry I don't really understand what is meant by early & late binding (I have seen this on some similar posts).

Can someone give me any suggestions on how to get around this issue?

Thanks
Emma

 

[Marc L]

There is no error message, even when the user right clicks on the table and just tries to 'refresh', it just does not return anything (which confirms Rory's point that it's not a VBA reference issue)

No error message may come from an On Error codeline hidding any issue …​

​

When opening the References option in the VBE Tools menu any reference issue is displayed as 'missing' …​

​

 

[jkpieterse]

IMHO this is a clear case of a bad installation. IT hasn't done the job right. If you ask me being able to use ODBC in office is crucial.