Excel 2016 - VBA Changes

[JRinderer]

Hello all, I've spent the past few hours combing the net trying to find a possible solution. I have a program that populates an Excel sheet from an Access table. The issue I'm having is that everyone in the office on 2010 is able to run this without any problems; but a user with 2016 is having them. I've verified the file is correct (not on an older version); verified she's connected to the appropriate network.

I'm getting an Run Time Error 91 Object Variable or With Block Variable not Set on this line of code (I have the entire section included as well:

Set rst = .currentDb.OpenRecordset("SELECT " & sqlString & " FROM SomeTable WHERE " & whereCaluse)

For the life of me... I can't figure it out. I've tried to do some research on if something between 2016 and 2010 changed that could cause this but she just started reporting this issue.

Dim wrkBk As Workbook
Dim wrkSht As Worksheet
'ThisWorkbook.Sheets("WorkSheet").Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
With Application
    .ScreenUpdating = False
End With


WasWrkBookOpen
'ThisWorkbook.RefreshAll
Set wrkBk = ThisWorkbook
Set wrkSht = wrkBk.Sheets("Tracker")


'MsgBox (valsFromBttn)


wrkSht.Columns("A:BAZ").EntireColumn.Hidden = False


wrkSht.Cells.AutoFilter


wrkSht.Cells.ClearContents


ThisWorkbook.Sheets("Data-Original").Cells.ClearContents
'MsgBox (sqlString)
Dim appAcc As New Access.Application
Dim rst As DAO.Recordset
Dim i As Long
Const path As String = "\SomeLocation\AccessDatabaseFile"


If sqlString = "" Then
    MsgBox ("Please note it looks like you haven't select a team. You will be given the full default view. If you believe you've reached this message in error contact John Rinderer")
    sqlString = "*"
    whereCaluse = "[Site Num] <> 'TEST1'"
End If


'[Site Num] <> 'TEST1' AND (Market = 'AROK' or Market = 'COLORADO' or Market = 'S.TX' or Market = 'UnAssigned-Ter')")


With appAcc
    .OpenCurrentDatabase path
    Set rst = .currentDb.OpenRecordset("SELECT " & sqlString & " FROM LaunchPadDb WHERE " & whereCaluse) '****THIS IS WHERE WE ERROR OUT*******
    For i = 1 To rst.Fields.Count - 1
        Sheet9.Cells(1, i + 1).value = rst.Fields(i).Name
    Next i
    Sheet9.Range("A2").CopyFromRecordset rst
End With


Set rst = Nothing
appAcc.CloseCurrentDatabase
Set appAcc = Nothing

As a side note I don't get the option to Debug, a message box pops up that says "Ok" and "Help".

My References are:
Visual Basic For Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library
Accessibility CplAdmin 1.0 Type Library
Microsoft Data Access Components Installed Version
Microsoft Access 14.0 Object Library
Microsoft ActiveX Data Objects 2.0 Library
Microsoft Forms 2.0 Object Library
Microsoft DAO 3.6Library

I've attempted to move DAO reference; but that didn't appear to work either.

I'm thinking it's possibly:

1. Reference Error
2. Error in the Code --Perhaps I didn't set an object I declared? But would that all the sudden have caused this to stop?--
3. Perhaps there's an update in 2016 that could be causing this.

Typically this error indicates I haven't ended a With Statement, or Set a variable. But as you can see Set is clearly in the line it errors out on. I cannot replicate this error on my own machine. I've requested a second computer with 2016 Office to test this out.

I seem to have followed all the necessary rules; but the computer is very rarely wrong. I have to be missing something.

 

[Norie]

Is a database actually being opened here?

With appAcc
    .OpenCurrentDatabase path

Have you checked that path has the correct, well path, name, extension etc for the database you want to open?

 

[JRinderer]

Yes, the path name is correct and the database opens correctly. It's that Set rst =

I'm wondering if maybe, for 2016 the Dim appAcc As New Access Application isn't "good enough"? For example, perhaps I should change to Dim appAcc as Access.Application and then Set appAcc = New Access.Application? But then why the flip is it opening the Db fine?

I've included the full string path (with some editing for security) as reference:

Const path As String = "\\wl-##-#\Shared\\@Reporting Tools\Data Sources\JRindererDataSources\PadDb.accdb"

 

[Norie]

If that wasn't 'good enough' you would know about it well before the point where you are currently getting the error.

If you change the code to this does it make any difference?

Dim appAcc As New Access.Application
Dim objDB As Object
Dim rst As DAO.Recordset
Dim i As Long

Const path As String = "\SomeLocation\AccessDatabaseFile"


    If sqlString = "" Then
        MsgBox ("Please note it looks like you haven't select a team. You will be given the full default view. If you believe you've reached this message in error contact John Rinderer")
        sqlString = "*"
        whereCaluse = "[Site Num] <> 'TEST1'"
    End If


    '[Site Num] <> 'TEST1' AND (Market = 'AROK' or Market = 'COLORADO' or Market = 'S.TX' or Market = 'UnAssigned-Ter')")


    Set objDB = appAcc.OpenCurrentDatabase(path)

    Set rst = objDB.OpenRecordset("SELECT " & sqlString & " FROM LaunchPadDb WHERE " & whereCaluse)    '****THIS IS WHERE WE ERROR OUT*******
    
    For i = 1 To rst.Fields.Count - 1
        Sheet9.Cells(1, i + 1).Value = rst.Fields(i).Name
    Next i
    
    Sheet9.Range("A2").CopyFromRecordset rst

    Set rst = Nothing
    objDB.Close


    Set appAcc = Nothing

By the way, why are you opening the database/Access for this?

As far as I can tell it could all be done using ADO without opening the database.

 

[JRinderer]

I was initially using ADO, but one of the users was getting an error "Library not found" (I can't remember the exact wording). After doing some quick research we just needed to download a file from Microsoft website and that would have updated the library. However, IT refused to give us permission, and we can't install our own software or applications.

Since this user was unable to use the processing feature of this tool I reverted to a DAO connection; which ran through for her and everyone else just fine. So I'm doing it because IT won't fix the issue; I've considered using some "alternative means"... enabling the local admin and installing what I want... but I don't want to push anymore than I have.

I did something similar to what you suggested to test this out when I first came across this error at her machine:

But instead of using

Dim objDB as object

I used

Dim db as DAO.Database

Then follow the same steps you outlined. Same result. But I'm wondering if a normal object would behave differently than a DAO.Database?

I'm waiting on IT to come back with my backup PC and Office 2016 to see if I can figure this out. I'll try this out once it comes back. Since I can't keep her PC and I have Office 2010 I'm unable to do any real testing. The code runs just fine on my machine and everyone else's.

 

[Norie]

What library was missing when you were using ADO?

Were you using early or late binding?

As for using Object vs DAO.Database, it shouldn't make a difference - you would still be dealing with the same thing.

I just use Object as a kind of generic type when working with, well, objects.

 

[JRinderer]

Good question, I honestly can't remember. It was about 2 months ago, I have one of the bookmarks saved referencing missing libraries and errors they can cause; but I didn't bookmark the Microsoft page where I could have downloaded it.

I typically use early binding (userforms sometimes will be late), I removed the old code to save space and improve readability so I can't say for certain; but knowing my habits I'd feel safe saying it was early binding.

 

[Norie]

It might be worth looking into using late-binding especially if the earlier problem was to do with the version of a library (or Excel/Office).

PS Late-binding a userform? Not sure I've heard of that - can't you enlighten me.

 

[JRinderer]

I could be completely misunderstanding the concept, but I believe late binding is when you haven't specifically declared say an Excel.Application as

 Dim Objct as Excel.Application

but you declared it

Dim Objct as Object.

It's based on how you declare the variables correct? So if I state, explicitly in the Dim statement the type of object that's early binding. If I call it Object, and then set it later to the type of object that's late binding.

So for example I'll set up an object variable like:

dim form1 as Object

set form1 = Userform1

form1.visible = true

By all means, feel free to correct me as it's entirely possible I'm wrong, and if I am wrong I want to be corrected. You don't learn by never questioning yourself. If I were an expert I wouldn't be asking questions .

Update on my issue:

I got my backup PC back with Office 16. I refreshed (grabbed the data using the code referenced in earlier posts) without any issue. However, I remembered the user was on WiFi.

I disconnected wired, went wireless, and sure enough I ran into the same issues. It get's stranger; this isn't the case in 2010 Office. I went back to my main PC and was able to run on wireless and wifi without issue.

Is it possible that perhaps Office 2016 takes up more resources; and my code isn't efficient enough to operate over wifi? Is the above code I'm referencing not as efficient for a DAO connection?

Is there a faster more reliable way to extract the contents of a table for Access (while I can't do ADO for everyone perhaps I offer two version?) and bring them into an Excel sheet?

 

[JRinderer]

Update:

I've re-run the test again to confirm it's an issue with WiFi and now it seems to run fine. I'm still not sure exactly what the issue is but it appears to be related to WiFi connection and Office version.

I think I'm going to rebuilt this in Excel 2016 and see if I can replicate the error again. This morning I repeated the test 10 times; and each time confirmed Wired connection had no problems WiFi on 2016 would not refresh.

I repeated again after lunch to confirm and start working: and now it's operating without any issue.

Gotta figure this one out; it's only a matter of time before IT has us all on Office 2016 and I can't have this phantom error running around unchecked. I'll update if I can pin-point it.