ADODB - Excel crasch when Connection is set to Nothing

For posts that don't quite fit into any of the other forums, including posts about these forums themselves.

Moderators: EViews Gareth, EViews Moderator

TonyBrohm
Posts: 5
Joined: Tue Nov 12, 2013 2:52 am

ADODB - Excel crasch when Connection is set to Nothing

Postby TonyBrohm » Tue Apr 24, 2018 3:40 am

Hi!
After upgrading EViews from version 9.5 to 10+ Excel crash when running my Excel VBA-code using ADODB driver. The error occur when reaching the line that set the connection to Nothing (if I remove this line of code, Excel instead crash when exiting the Sub). Sometimes I get past this line but If I run the Sub ones more or trying exit Excel, then the error occur.

Code: Select all

Public Sub Test()
    Dim filePath As String
    filePath = "C:\Temp\workfile.wf1"
   
    Dim cs As String
    connString = "Provider=EViewsOleDbProvider.EViewsProv;Max Objects=0;Path=""" & filePath & """"
   
    Dim conn As ADODB.connection: Set conn = New ADODB.connection
    conn.ConnectionString = connString

    conn.Open

    Dim rcrdst As ADODB.Recordset: Set rcrdst = conn.OpenSchema(adSchemaCatalogs)

    Dim catalogs As New Collection
    While Not rcrdst.EOF
        catalogs.Add CStr(rcrdst("CATALOG_NAME").Value)
        rcrdst.MoveNext
    Wend
   
    rcrdst.Close
    conn.Close
    Set rcrdst = Nothing
    Set conn = Nothing          ' <--  Excel crash when reaching this line
   
End Sub


When reading the Windows log it says an there was an error in module KERNELBASE.dll.

I reference the EViews 9.0 Type Library, and also tried the EViews 10.0 Type Library (had to install the 32-bit version of EViews 10 to get access to that one), but the error occur regardless of version. Except of course if I go back to EViews 9.5 where the code work with no issues.

Hopefully you can see what I do wrong. I appreciate any help.

My PC:
EViews 10 (64-bit) Enterprise Edition - Mar 21 2018 Build
Microsoft Office 365 ProPlus - Version 1708 (8431.2153)
Windows 10 Enterprise - 1703 (15063-1029)

EViews Steve
EViews Developer
Posts: 530
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: ADODB - Excel crasch when Connection is set to Nothing

Postby EViews Steve » Tue Apr 24, 2018 7:54 am

First, your code sample is only using our OLEDB provider, which has not changed between EViews 9.5 and EViews 10 (I just confirmed this by looking at the EViewsOleDbProvider.dll file in both program home directories and they're the same file). Also, using this OLEDB provider does not require you to reference our type library, as that is only for COM automation programming. You should remove this library reference from your test code sample in order to reduce possible issues with it.

Next, our OLEDB provider does require a valid EViews license to be installed on your machine so please verify that both EViews 10 and EViews 9.5 software runs without any licensing issues on the same machines that are running your Office VBA code.

I tested your code to loop thru the page names of one of my workfiles and it succeeded without any problems (with the EViewsOleDbProvider dll from EViews 10 active), so I don't have a good explanation for this difference in behavior you are seeing. Perhaps this is an issue with 32-bit vs 64-bit? I would do the following to help isolate the issue.

Open a DOS command window with Admin rights and go to your "EViews 10" folder (it doesn't matter 32-bit or 64-bit), then manually register the EViewsOleDbProvider.dll by running the following command:

Code: Select all

regsvr32 EViewsOleDbProvider.dll

like this:
example1.png
example1.png (8.89 KiB) Viewed 428 times


and then wait for the success message.
example2.png
example2.png (2.74 KiB) Viewed 428 times

Next, navigate to either the "x86" subdirectory or the "x64" subdirectory (whichever one exists in your EViews 10 folder) and then run the same command to register the other bit-version of the same dll.

After you've successfully registered both versions of the DLL, restart your Office application and try running your VBA code again. This time you can be certain that the code being used is from the directory you last registered the DLLs from.

Repeat this process with the same DLL files from your EViews 9 folder (if you still have it) and see if your VBA code returns to normal.

Let me know what you find.

Steve

TonyBrohm
Posts: 5
Joined: Tue Nov 12, 2013 2:52 am

Re: ADODB - Excel crasch when Connection is set to Nothing

Postby TonyBrohm » Wed Apr 25, 2018 9:07 am

Thank you for pointing out the reference issue. In the main project there is two references, the first one is the EViews 9.0 Type Library and the second one is named EViewsOleDbProvider Type Library. I managed to remove the wrong one when making this test Sub.

The license gets thru the register process and I can see the EViews license number and my registered name when checking Help -> About EViews.

I did the manual registration of the EViewsOleDbProvider.dll in the order you told me except for the EViews 9 version, as I don't have EViews 9 on any of the two PC's I am testing EViews 10 and the code on. Sadly the problem remain.

About the manual registration of EViewsOleDbProvider.dll. I get the feeling that its only the last registered dll that are used?
When looking at the references and looking at the file location, it is always associated with latest registered EViewsOleDbProvider.dll.
VBA_References.png
VBA_References.png (34.42 KiB) Viewed 411 times

With this in mind I tried the four different dll's, but this didn't solve the problem.

I noticed that Excel crash always occur the second time I run the code. So the error are not as unpredictable as I first thought.

Tony

EViews Steve
EViews Developer
Posts: 530
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: ADODB - Excel crasch when Connection is set to Nothing

Postby EViews Steve » Wed Apr 25, 2018 10:13 am

You don't need to reference the "EViewsOleDbProvider 1.0 Type Library" either. You only need that if you plan on instanciating the COM object directly which you are not doing. You're just passing the connection string that mentions our object's PROGID and having the ADODB.Connection object instanciate it for you. So please remove all EViews-related references and try again.

The reason I had you call regsvr on both DLLs is because if your VBA client is 32-bit, it'll want to use our 32-bit oledb driver. If it's 64-bit, then it'll want to use our 64-bit driver. Having both versions registered from the same "EViews 10" folder means regardless of the bitness of your VBA client, you know it's using one of those two DLL files.

Again since this DLL file is identical to the one that was released with EViews 9, your crash has to be caused by something else that changed on your system between now and then. It was not caused by you switching to EViews 10 -- at least not directly.

If you're still having problems with crashes, please try a different smaller workfile. In EViews 10, create a simple workfile with three pages, each with a different name and see if that resolves your crash.

If it doesn't, then I would try making sure you install any patches for your Office installation to see if that resolves any issues.

Finally, I would try your VBA code on a different machine to see if the crashing continues.

If so, send me the workfile you created above (with the three pages) and your sample VBA code, and also explicit instructions on how you are running the code to generate the crash and I will try again to reproduce the problem. Oh and let me know if your Office 365 is 32-bit or 64-bit.

Steve

Just a thought, since our OLEDB object is being loaded by ADODB.Connection, maybe your ADODB reference is what changed? Which version are you using in your sample? My previous test was with "Microsoft ActiveX Data Objects 2.8 Library":
ref.png
ref.png (16.37 KiB) Viewed 407 times

EViews Steve
EViews Developer
Posts: 530
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: ADODB - Excel crasch when Connection is set to Nothing

Postby EViews Steve » Wed Apr 25, 2018 10:23 am

Good news, I was able to reproduce a crash when I ran your sample code over and over on my system. I'll see if it's something we can fix and I'll let you know when I have more information.

EViews Steve
EViews Developer
Posts: 530
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: ADODB - Excel crasch when Connection is set to Nothing

Postby EViews Steve » Wed Apr 25, 2018 11:47 am

Ok, I was able to find the cause of the crash and we have a fix ready for our next EViews 10 patch. It was in a different DLL that the OLEDB provider DLL uses.

The patch will be released sometime next week, but if you'd like the fix earlier, email me at steve@eviews.com and reference this post and I'll send it to you.

Steve


Return to “Any Other Business”

Who is online

Users browsing this forum: No registered users and 1 guest