Connect To Sql Server With Vba & Ado

  • Hi guys


    My brother-in-law has a question. See below, anyone?

    Quote

    I am trying to connect an excel spreadsheet to SQL Server 2005 using VBA and ADO. The problem is that I have to install the SQL Server client on my machine because Excel does not seem to want to talk to the db unless I use the SQLNCLI.1 provider in the connection string.



    This is far from ideal as I want to be able to distribute the spreadsheet without having force the user to install the SQL Server client libraries. I would have thought that installing the latest version of MDAC would solve the problem, but it seemed to make no difference.

  • Re: Connect To Sql Server With Vba & Ado


    How is he currently connecting? I have never had to do this... using the relevant MDAC has always been sufficient


    Simple example below...(using MDAC 2.8)


    [vba]
    Sub Add_Results_Of_ADO_Recordset()
    'This was set up using Microsoft ActiveX Data Components version 2.8


    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stSQL As String
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnStart As Range


    Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=BI;" & _
    "Data Source=AURDWDEV01"
    'where BI is SQL Database & AURDWDEV01 is SQL Server

    Set wbBook = ActiveWorkbook
    Set wsSheet = wbBook.Worksheets(1)

    With wsSheet
    Set rnStart = .Range("A1")
    End With

    stSQL = "SELECT * FROM Settings"

    Set cnt = New ADODB.Connection

    With cnt
    .CursorLocation = adUseClient
    .Open stADO
    .CommandTimeout = 0
    Set rst = .Execute(stSQL)
    End With

    'Here we add the Recordset to the sheet from A1
    rnStart.CopyFromRecordset rst


    'Cleaning up.
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing

    End Sub[/vba]

  • Re: Connect To Sql Server With Vba & Ado


    Thanks for the info. I will try later today. I noted you are using the SQLOLEDB.1 provider. I think SQl Server 2005 only likes the SQLNCLI.1 provider, but will check.

  • Re: Connect To Sql Server With Vba & Ado


    Quote from Red

    Thanks for the info. I will try later today. I noted you are using the SQLOLEDB.1 provider. I think SQl Server 2005 only likes the SQLNCLI.1 provider, but will check.


    Red,


    I tested the code with SQL 2005 from Excel 2003 & 2007 using that provider. Works fine ;)


    This amended version works with the sample database Adventureworks on a local instance


    [vba]
    Sub Add_Results_Of_ADO_Recordset()
    'This was set up using Microsoft ActiveX Data Components version 2.8

    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stSQL As String
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnStart As Range


    Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=AdventureWorks;" & _
    "Data Source=."
    'where Adventureworks is SQL Database & . is Localhost SQL Server

    Set wbBook = ActiveWorkbook
    Set wsSheet = wbBook.Worksheets(1)

    With wsSheet
    Set rnStart = .Range("A1")
    End With

    stSQL = "SELECT * FROM Production.Product"

    Set cnt = New ADODB.Connection

    With cnt
    .CursorLocation = adUseClient
    .Open stADO
    .CommandTimeout = 0
    Set rst = .Execute(stSQL)
    End With

    'Here we add the Recordset to the sheet from A1
    rnStart.CopyFromRecordset rst

    'Cleaning up.
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing

    End Sub
    [/vba]

  • Re: Connect To Sql Server With Vba & Ado


    Thanks WIll[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Just Tested it. Works fine. SQL 2005 must support older connection string types too. On connectionstrings.com it did not specify oledb as a vaslid type for 05. Just goes to show what they know eh!. Thanks again WIll[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Just Tested it. Works fine. SQL 2005 must support older connection string types too. On connectionstrings.com it did not specify oledb as a vaslid type for 05. Just goes to show what they know eh!. Thanks again WIll