ADODB commands slower in Office 365 64-bit

[EasterVBA]

Hi. I have recently been upgraded to Office 365 64-bit and I have noticed that the ADO commands for updating data in a SQL Server database are dramatically slower... paticularily in a large (ish) spreadsheet. This simple code example below connects to a SQL Server database and updates a single value in a database data table. If I run this code in a blank workbook it runs in approx 2 seconds. However, if I run the same code within a workbook that has other VBA modules and multple worksheets in it is taking 60 seconds to complete. I dont understand why it is taking so much longer given that it is not trying to write to or update any cells. I have tried turning off events , screen updating etc. but this had no impact. Please help!

Sub ConnectAndUpdateTest()

Dim SqlConnection As ADODB.Connection
Dim sConnectionString As String

'===== CONNECT TO THE SERVER AND DATABASE ========
Set SqlConnection = New ADODB.Connection
SqlConnection.ConnectionString = "Provider=SQLOLEDB;Data Source = [ServerName] ;Initial Catalog=[DatabaseName]"
SqlConnection.Open
SqlConnection.CommandTimeout = 800

'===== UPDATE A VALUE IN A TABLE =================
SqlConnection.Execute ("UPDATE tblUpdateArray SET [UpdatedID] = 1 WHERE [UpdatedID] = 0")

End Sub

 

[RoryA]

Is the problem the same in any large workbook, or just a specific one?

 

[EasterVBA]

Is the problem the same in any large workbook, or just a specific one?

Hi , any workbook- I have tested a few. When I say 'large' really its only around 2mb so not that large. I ran a test starting with a blank workbook and then progressively copied over sheets from another workbook and tested it each time. as the workbook grew in size, so did the delay with the ADODB?

 

[RoryA]

Anything unusual in the sheets you copied over - eg use of linked pictures?

How are you running the code?

 

[EasterVBA]

Anything unusual in the sheets you copied over - eg use of linked pictures?

How are you running the code?

Hi - nothing unusual in the sheets. I avoid any kind of links in Excel. The sheets have some conditional formatting and dynamic ranges but nothing out of the ordinary. I am running the code via VBA editor and pressing F5 in the procedure. This feature has effected all of my workbooks that have worked fine for years in previous versions of Excel but they were all 32-bit. I am guessing its the 64-bit that causing the problem? it seems VBA is doing a lot of work (compiling?) outside of just running this simple update statement.

 

[RoryA]

I don't really see how compilation would be affected by having a lot of worksheets, unless they all have code in them - but even then, running a bit of ADO shouldn't require compiling the worksheet code.

Does it make any difference if you close the VBE and run the code via the macros dialog?