Create a connection to sql server

[devon59]

I am not sure how I could create a connection of my code in excel 2003 to the sql server. In inherited this connection code from other people but now that we change the server name etc... the connection doesn't work. Basically, what I am trying to do is connecting the functions I have in my VBA code to this new server. The name of the server that I am trying to connect to is p3sql and the database is called abcd. Do I have to change anything in Provider= or security?
**************************************
Const sConnection = "Provider=sqloledb;Server=p3sql;database=abcd;Integrated Security=SSPI"
***************************************
Thank you

[Hack]

Welcome to the forums.

Connecting to SQL Server from Excel is very easy. In fact, I have two Excel applications that connect to three different SQL Server databases depending on what the user needs to do.

It is nothing more than a matter of the connection string.

What problems are you having? What does "connection doesn't work" mean?

[devon59]

When I used that script, I didn't get any return on the functions I created. I know there's notthing wrong with the function because I tested it in sql management studio and it returns the right information. So I suspect that somehow, my connection is wrong because when I input the function in excel, I just got #VALUE

[devon59]

I thought maybe if I post the whole code, somebody might be able to help me. Below is the code I inherited from someone else. When I do the debug, the error seems to be coming from Sub Refreshworkbook() section. However, I don't know how to fix it. Thank you so much for your help.

***************************************************
Option Explicit

Const sConnection = "Provider=sqloledb;Server=p3sql;database=ABCD;Integrated Security=SSPI"


Dim cnStaging As ADODB.Connection
Sub RefreshWorkBook()

Application.CalculateFull

cnStaging.Close
Set cnStaging = Nothing

End Sub

Sub StagingConnection()
If cnStaging Is Nothing Then
Set cnStaging = New ADODB.Connection
cnStaging.Open sConnection
ElseIf (cnStaging.State And 0) Then ' State is closed
cnStaging.Open sConnection
End If
End Sub

Function sfResp(Optional Project As String, Optional lot As String) As Long
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim rsReceiptDate As ADODB.Recordset

StagingConnection

Set rs = New ADODB.Recordset
sSQL = "SELECT SUM(CASE WHEN amount > 0 THEN 1 ELSE 0 END) AS Responses," & vbCrLf
sSQL = sSQL & " SUM(amount) As Revenue," & vbCrLf
sSQL = sSQL & " rtrim(ltrim(substring(appealcode,1,4)+ " - " + substring(appealcode,7,1))) As project" & vbCrLf
sSQL = sSQL & " substring(appealcode,7,1) As lot" & vbCrLf
sSQL = sSQL & "FROM dbo.gift R" & vbCrLf
sSQL = sSQL & "WHERE substring(appealcode,1,2) in ('DA','DH') " & vbCrLf

If Len(Project) > 0 Then
sSQL = sSQL & " AND rtrim(ltrim(substring(appealcode,1,4)+ " - " + substring(appealcode,7,1))) in (" & Project & ") " & vbCrLf
End If

If Len(lot) > 0 Then
sSQL = sSQL & " AND substring(appealcode,7,1) in (" & lot & ")" & vbCrLf
End If
rs.Open sSQL, cnStaging

If IsNull(rs!Responses) Then
sfResp = 0
Else
sfResp = rs!Responses
End If

Set rs = Nothing
End Function