CODEWITHSUNDEEP
ms-accessodbcsnowflake-cloud-data-platform
BrianHtx
BrianHtx

Reputation: 81

ODBC connection string to Snowflake for Access Pass Thru Query

I am trying to create a connection string to get to Snowflake data from Access 2010 and above. I can create a database DSN and link to the tables I use, but I need to build DSN-less connection strings for distributed applications. Here's what I have so far, it fails with the message "ODBC connection to xxxx failed". Here's what I have so far:

ODBC;Driver={SnowflakeDSIIDriver}; Server=https://server name; Role=role name;Warehouse=warehouse name;Database=db name;Schema=schema name;UID=snowflake ID; PWD=snowflake password;

Upvotes: 8

Views: 5405

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11066

I can confirm that DNS-free connections work fine in Access 2013. I have not tested on Access 2010, but I have it available if that needs testing.

The first problem I encountered is that the Snowflake ODBC driver reports 32/64-bit in the ODBC section of Control Panel, but it may not have one or the other installed.

In my case, it showed in the DSN sources as 32/64-bit, but I had only the 64-bit version installed. Notice that after installing the 32-bit driver, the Programs and Features (where to go normally for uninstalling apps) shows both the 64 and 32 bit drivers.

enter image description here

After installing the 32-bit driver, it was just a matter of getting the connection string right. You want to copy it from the URL on your Snowflake web UI. Strip off the https:// part, and then keep everything up to and including the snowflakecomputing.com in the url. That's what you'll use for the server.

Edit 2: I missed the part of the question that referenced pass through queries and was describing a procedure I tested recently for DNS-free connection using VBA. I tested the pass-through connection and it worked fine. The only difference is in the ODBC connection string you need to keep the "ODBC;" prefix:

ODBC;Driver{SnowflakeDSIIDriver};server=<your_URL_everything_before_snowflakecomputing.com>.snowflakecomputing.com;uid=greg;pwd=xxxxxx

Edit: One thing I forgot and am adding... The built-in Access data engine did not work for me to connect with a DNS-free connection. The code shows that it's using ActiveX Data Objects (ADO). You need to add a reference to that in your VBA project:

enter image description here

enter image description here

' For the account, use everything after https:// up to and including 
' snowflakecomputing.com in your URL when connecting to Snowflake using the web UI. 
 
Const SNOWFLAKE_ACCOUNT = "<your_account>.<your_region>.snowflakecomputing.com"
Const SNOWFLAKE_USER = "greg"
Const SNOWFLAKE_PASSWORD = "xxxxx"

Public Sub Main()
    Dim odbc As String
    Dim sfCon As ADODB.Connection
    Set sfCon = OpenDatabaseConnection(GetConnectionString())
    If Not sfCon Is Nothing Then
        'Use the connection here...
        sfCon.Close
    End If
End Sub

Private Function GetConnectionString()

    GetConnectionString = "Driver={SnowflakeDSIIDriver}" + _
                          ";server=" + SNOWFLAKE_ACCOUNT + _
                          ";uid=" + SNOWFLAKE_USER + _
                          ";pwd=" + SNOWFLAKE_PASSWORD + _
                          ";network_timeout=60" + _
                          "login_timeout=60"

End Function


Public Function OpenDatabaseConnection(ConnString As String) As ADODB.Connection
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = New ADODB.Connection
    
    With database
        .ConnectionString = ConnString
        .ConnectionTimeout = 60
        .Open
    End With
     
    Set OpenDatabaseConnection = database
     
    Exit Function
Handler:
    MsgBox "Error: " + Err.Description
End Function

Upvotes: 4

Steven Segal
Steven Segal

Reputation: 41

I think you are on the right track. I have the same thing and it works.

ODBC;
driver={SnowflakeDSIIDriver};
server=accountname.snowflakecomputing.com;database=dbname;
schema=public;
warehouse=whname;
role=rlname;
Uid=userid;
Pwd=password;

Very odd that the DSN one works and your doesn't.

Upvotes: 4

Related Questions