Simba Spark Driver fails for big datasets in Excel

[pvalcheva]

Hello, I am getting the following error when I want to extract data from Databricks via VBA code. The code for the connection is:

Option Explicit

Const adStateClosed = 0

Public CnAdo As New ADODB.Connection
Dim DSN_name As String
Dim WB As Workbook
Dim dashWS As Worksheet
Dim sqlWS As Worksheet

Public Sub Check_CnAdo_Open()

' Set workbook and worksheet references
Set WB = ThisWorkbook
Set dashWS = WB.Sheets("Dashboard")
Set sqlWS = WB.Sheets("Dashboard") ' Change if needed

' Read DSN name from Dashboard named cell
DSN_name = dashWS.Range("DSN_name").Value

' Only open if not already open
If CnAdo.State = adStateClosed Then
CnAdo.ConnectionString = _
"DSN=" & DSN_name & ";" & _
"UseNativeQuery=1;" & _
"EnableResultCache=1;" & _
"SSL=1;" & _
"DisableCloudResultStorage=1;"

CnAdo.CommandTimeout = 3600

On Error GoTo ErrHandler
CnAdo.Open
End If

Exit Sub

ErrHandler:
MsgBox "Connection failed: " & Err.Description, vbCritical, "ODBC Connection Error"

End Sub

[mark_ott]

The code you provided for connecting to Databricks via VBA appears structurally sound, but the cause of the error you are experiencing could stem from several typical issues encountered when using ADODB with Databricks ODBC connections from Excel VBA. Below are the most common causes and troubleshooting steps.

---

Common Causes and Solutions

1. DSN Configuration

• Double-check that the DSN (DSN_name) exists on the machine where the code runs and is correctly configured for Databricks (correct server hostname, HTTP path, token, etc.).

• If you’re using a 64-bit version of Excel, ensure the DSN is set up in the 64-bit ODBC Data Source Administrator (and vice versa for 32-bit Excel).

2. ODBC Driver

• Ensure you have the latest Databricks ODBC driver installed (matching your Excel version’s bitness).

• If the driver is missing or incorrect, the connection will fail to initialize.

3. Connection String Options

• Not all connection string options are recognized. For Databricks, you may need to include options like Host, Port, HTTPPath, and Token. Here is a sample connection string format for Databricks ODBC:

text

"DSN=" & DSN_name & ";UID=token;PWD=your_personal_token;Host=your_server;Port=443;HTTPPath=your_http_path;SSL=1;"

• Double-check documentation for required parameters your DSN may lack, or consider using a DSN-less connection for additional control.

4. Error Handling

• To see the actual error, you can add debug output to indicate where it fails:

text

Debug.Print "DSN_name value: " & DSN_name Debug.Print "Connection string: " & CnAdo.ConnectionString

• This can help you verify that all values picked up from the spreadsheet are correct.

5. Network or Firewall Issues

• Make sure the machine running this code can reach Databricks’s endpoint, and that ports and firewalls are open as needed.

6. Excel Process Bitness

• If you are on a 32-bit Excel but installed a 64-bit ODBC driver (or vice versa), the connection will fail.

7. Missing Required Libraries

• Ensure the “Microsoft ActiveX Data Objects x.x Library” is enabled in the VBA references.

---

General Troubleshooting Steps

• Step 1: Test the DSN via Excel’s Data > Get Data > From ODBC to confirm configuration.

• Step 2: Simplify the connection string to only use DSN and test (remove all extra options temporarily).

• Step 3: Manually set the User ID and Password in code to verify if credential handling is the issue.

---

Example of a Minimal DSN Connection

text

CnAdo.ConnectionString = "DSN=" & DSN_name & ";" CnAdo.Open

If this minimal approach works, incrementally add other parameters to identify which one causes the error.