CODEWITHSUNDEEP
sqlexcelexcel-2010vba
justaguy
justaguy

Reputation: 3022

SQL query not working as expected in excel 2010

I wrote a 'VBA' that the lab has been using for 2 years that basically used an access database as a query. Recently, a SQL database is being used and an ODBC was created to use in the query. The problem is that query can not been seen by excel, except for my computer. I have SQL Management Studio 2008 on mine, but I can not seem to get the query working on other computers. I have tried different drivers but can not solve this.

Upvotes: 1

Views: 705

Answers (1)

user4039065
user4039065

Reputation:

It's not 'an ODBC driver' that cannot be found; it's the DSN file that you created (with credentials) that is local to your computer. You need to spell everythign out in a connection string that will be carried around in the VBA module.

Sub sql_connect()
    Dim svr As String, db As String, usr As String, pwd As String
    Dim sCNX As String, sSQL As String
    Dim cnx As Object, rs As Object


    Set cnx = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    svr = "THE_SERVER"
    db = "myDatabase"
    usr = "me_user"
    pwd = "me_users_pwd"
    sCNX = "Provider=SQLNCLI11;Server=" & svr & ";Database=" & db & ";UID=" & usr & ";PWD=" & pwd & ";"
    'Debug.Print sCNX 'used for syntax checking
    cnx.Open sCNX

    sSQL = "SELECT TOP 50 * FROM [myDatabase].dbo.[myTABLE]"
    rs.Open sSQL, cnx

    Do While Not rs.EOF
        Debug.Print rs(0) & " - " & rs(1)
        rs.movenext
    Loop

    rs.Close: Set rs = Nothing
    cnx.Close: Set cnx = Nothing

End Sub

There are ways to hide the credentials that you are providing; locking your VBA project with a password would be a start. Creating an MSSQL user with limited, read-only access is also a good step.

I'm not sure if it is apparent, but that user and password as SQL user credentials, not a domain user using a trusted connection.

Upvotes: 2

Related Questions