I am having trouble getting my SQL Data into an Excel Spreadsheet I am getting Provider Cannot be found. It may not be Properly Installed. I am using SQL Server Management Studio with SQL\SQLExpress. Please help! Here is my code:
Sub datapull(Scenario As String)
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim sSQL As String
Dim ceRg As Worksheet
Set ceRg = ThisWorkbook.Worksheets("Scenario")
If ceRg.FilterMode Then
ceRg.ShowAllData
End If
ceRg.Range("A3").ClearContents
' Create the connection string.
sConnString = "Provider=Sample;Data Source=SQL|SQLExpress;Initial Catalog=Scenario;Trusted_Connection=yes;"
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the connection and execute.
conn.Open sConnString
sSQL = "SELECT [Order #],[LOCATION],,[FORM],"
sSQL = sSQL & "CAST([Est MIRU Compl] as date),CAST([Critical Obligation Date] as date)"
sSQL = sSQL & "[OBLIG TYPE], [Comments],[CUSTOMER], [SCENARIO],"
sSQL = sSQL & "FROM [dbo].[vwRG_SCENARIO]"
sSQL = sSQL & "WHERE [Scenario] = '" & Scenario & "' "
sSQL = sSQL & "ORDER BY [ORDER #]"
Set rs = conn.Execute(sSQL)
' Check we have data.
If Not rs.EOF Then
' Transfer result.
CErig.Range("A3:BA50000").CopyFromRecordset rs
' Close the recordset
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If
End Sub