connect to a SQL server database in VBA in Excel

[csbdeady]

Hi there

Silly question - how do I connect to a SQL server database in VBA in Excel.

I've set up my ODBC connection in Windows, but can't get any of the sample Excel code to work.

Thanks!
-Colin

 

[amal1973]

this will run a stored procedure in sql 2000..but will accept a perameter
Private Sub CommandButton3_Click()
Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim objField As ADODB.Field
Dim loffset As Long

Sheet2.Cells.Delete
'Create the connection String
szConnect = "Provider=SQLOLEDB;Data Source =Server.Name;" & _
"Initial Catalog=Database.Name ;Integrated Security=SSPI"

Set objConn = New ADODB.Connection
Set rsData = New ADODB.Recordset

If TextBox1.Value = "" Then
Exit Sub
End If
objConn.Open szConnect
objConn.CommandTimeout = 50
objConn.sp_RCMonthServInfoCost TextBox1.Value, TextBox2.Value, rsData

'Make sure we got records back
If Not rsData.EOF Then
Sheet2.Range("A2&quot.CopyFromRecordset rsData
rsData.Close

With Sheet2.Range("A1&quot
For Each objField In rsData.Fields
.Offset(0, loffset).Value = objField.Name
loffset = loffset + 1
Next objField
.Resize(1, rsData.Fields.Count).Font.Bold = True
End With


Sheet2.UsedRange.EntireColumn.AutoFit
Else
MsgBox "error: No records returned.", vbCritical
End If
'Clean up our Objects
If CBool(objConn.State And adStateOpen) Then objConn.Close
Set objConn = Nothing
If CBool(rsData.State And adStateOpen) Then rsData.Close
Set rsData = Nothing

End Sub

 

[csbdeady]

Thanks for that I'll take a look through.
-Colin