OLE error when starting VBA in Excel 2016?

Question

I am trying to use Excel as a database, and I am following a tutorial from this site .

The problem is that whenever I try to "Refresh drop-down lists" in the file below, I get this error: "Microsoft expects another application to complete the OEL action."

What am I missing or doing wrong here, and how do I do this?

I use Excel 2016 Home and Student that support uptodate. I also enable macros when opening a workbook.

The same file works fine when opened in Excel 2007. I also noticed that the Microsoft ActiveX Data Objects 6.0 library refers to "msado60.dll" in the example, while the file "msado60.tlb" in Excel 2016 (which I use).

Excel File Link

Private Sub cmdShowData_Click() 'populate data strSQL = "SELECT * FROM [data$] WHERE " If cmbProducts.Text <> "" Then strSQL = strSQL & " [Product]='" & cmbProducts.Text & "'" End If If cmbRegion.Text <> "" Then If cmbProducts.Text <> "" Then strSQL = strSQL & " AND [Region]='" & cmbRegion.Text & "'" Else strSQL = strSQL & " [Region]='" & cmbRegion.Text & "'" End If End If If cmbCustomerType.Text <> "" Then If cmbProducts.Text <> "" Or cmbRegion.Text <> "" Then strSQL = strSQL & " AND [Customer Type]='" & cmbCustomerType.Text & "'" Else strSQL = strSQL & " [Customer Type]='" & cmbCustomerType.Text & "'" End If End If If cmbProducts.Text <> "" Or cmbRegion.Text <> "" Or cmbCustomerType.Text <> "" Then 'now extract data closeRS OpenDB rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Sheets("View").Visible = True Sheets("View").Select Range("dataSet").Select Range(Selection, Selection.End(xlDown)).ClearContents 'Now putting the data on the sheet ActiveCell.CopyFromRecordset rs Else MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly Exit Sub End If 'Now getting the totals using Query If cmbProducts.Text <> "" And cmbRegion.Text <> "" And cmbCustomerType.Text <> "" Then strSQL = "SELECT Count([data$].[Call ID]) AS [CountOfCall ID], [data$].[Resolved] " & _ " FROM [Data$] WHERE ((([Data$].[Product]) = '" & cmbProducts.Text & "' ) And " & _ " (([Data$].[Region]) = '" & cmbRegion.Text & "' ) And (([Data$].[Customer Type]) = '" & cmbCustomerType.Text & "' )) " & _ " GROUP BY [data$].[Resolved];" closeRS OpenDB rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Range("L6").CopyFromRecordset rs Else Range("L6:M7").Clear MsgBox "There was some issue getting the totals.", vbExclamation + vbOKOnly Exit Sub End If End If End If End Sub Private Sub cmdUpdateDropDowns_Click() strSQL = "Select Distinct [Product] From [data$] Order by [Product]" closeRS OpenDB cmbProducts.Clear rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Do While Not rs.EOF cmbProducts.AddItem rs.Fields(0) rs.MoveNext Loop Else MsgBox "I was not able to find any unique Products.", vbCritical + vbOKOnly Exit Sub End If '---------------------------- strSQL = "Select Distinct [Region] From [data$] Order by [Region]" closeRS OpenDB cmbRegion.Clear rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Do While Not rs.EOF cmbRegion.AddItem rs.Fields(0) rs.MoveNext Loop Else MsgBox "I was not able to find any unique Region(s).", vbCritical + vbOKOnly Exit Sub End If '---------------------- strSQL = "Select Distinct [Customer Type] From [data$] Order by [Customer Type]" closeRS OpenDB cmbCustomerType.Clear rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic If rs.RecordCount > 0 Then Do While Not rs.EOF cmbCustomerType.AddItem rs.Fields(0) rs.MoveNext Loop Else MsgBox "I was not able to find any unique Customer Type(s).", vbCritical + vbOKOnly Exit Sub End If End Sub 

Answer 1

In the comments, your OpenDB method opens an ADO connection. It seems you are not closing anywhere.

You are trying to open a connection that is already open. An OLE server error reports that the server (Excel) is busy because another ADO connection is connected to it. All you have to do is make sure that you only open the connection once and then close it when you finish working with it.

Answer 2

I had a similar problem. This worked for me:
1. From the Tools menu, select Options.
2. Click on the "General" tab. 3. Select the "Ignore other applications that use dynamic data exchange (DDE)" check box, and then click "OK."

I would recommend changing this setting while working with your tutorial. Although he solved this problem for me, it also made Excel behave strangely in some other circumstances.

If you think that the problem is related to your specific version of ADO, you can also try using the link to an older version (for example, Microsoft ActiveX Data Objects 2.8 Library).

Answer 3

I just checked your code (installed by Excel 2013) and everything was fine. Mistakes did not happen or something like that. I also checked the link to the Microsoft ActiveX data object library, and for me it is also ".tlb". Therefore, I think this is not a problem.

But there is a problem that, in my opinion, could be the cause of your error:

When a line of code is rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic , the macro code will probably continue to work and call the next line until the SQL query has been executed. Therefore, calling rs.RecordCount on the next line may result in an error if the request is still running.

Since I could not reproduce your error, I cannot continue testing to solve your problem. Therefore, I hope my idea can help you or anyone else solve your problem.