Simba Spark Driver fails for big datasets in Excel

pvalcheva — Tue, 24 Jun 2025 09:06:03 GMT

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

Re: Simba Spark Driver fails for big datasets in Excel

mark_ott — Wed, 29 Oct 2025 20:09:55 GMT

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.

topic Re: Simba Spark Driver fails for big datasets in Excel in Data Engineering

Simba Spark Driver fails for big datasets in Excel

Re: Simba Spark Driver fails for big datasets in Excel

Common Causes and Solutions

1. DSN Configuration

2. ODBC Driver

3. Connection String Options

4. Error Handling

5. Network or Firewall Issues

6. Excel Process Bitness

7. Missing Required Libraries

General Troubleshooting Steps

Example of a Minimal DSN Connection