Missing reference in Excel 2016

[The_Kurgan]

I do a lot of automation between Excel and Access. Unfortunately, I’ve been switched to Excel 2016 and am now missing the “Access Developer Extensions Type Library” reference. Below is just a sample piece of code I would use that is now puking due to the missing reference. Has anyone found a work-around for this? TIA!

'Create the Recordset object
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient


strSQL = "SELECT Phnx_LoanLevel_Master.Data_Date AS Phnx_DT FROM Phnx_LoanLevel_Master GROUP BY Phnx_LoanLevel_Master.Data_Date ORDER BY Phnx_LoanLevel_Master.Data_Date DESC;"
RS.Open strSQL, cn, adOpenStatic, adLockBatchOptimistic
RS.MoveFirst
Do Until RS.EOF = True
    Start_Frm.ListBox1.AddItem RS.Fields(0)
    RS.MoveNext
Loop

 

[RoryA]

What do you use that reference for? It has nothing to do with that code.

 

[The_Kurgan]

Without that reference, the code halts immediately and highlights

RS.CursorLocation = adUseClient

I have a short window where I can use an older version of Excel which contains that reference and it works fine.

If I remove the missing reference, the code will run, but ithangs on

[COLOR=#574123][FONT=Calibri]RS.Open strSQL, cn, adOpenStatic,adLockBatchOptimistic[/FONT][/COLOR]

 

[RoryA]

That code needs a reference to an Activex data objects library, but not developer extensions.

 

[The_Kurgan]

Ok, thank you. I do have the ActiveX box checked. I've tried 6.0 and then 2.8 and it still hangs on the line that opens the recordset. I was switched to a new version of Citrix, but I can't imagine that would have such an effect.

 

[RoryA]

The fact it hangs is not likely to be related to references. I'd suspect a connection issue. Does it timeout eventually?

 

[The_Kurgan]

Sorry for the delay in my posting. I thought I had replied to your question before I left for vacation.

It does not seem to timeout. It usually takes a couple seconds to run, but I have killed it after as much as 30 minutes since the issue started.