Excel VBA

css navigation by Css3Menu.com

Retrieve SQL Data

One of the things I have struggled with for years is properly retreiving data from SQL databases and placing correctly in a spreadsheet.

This example simplifies the process.

Sub RetrivDateID(FindDTID As Date)
    Dim RS As ADODB.Recordset
    Dim DaDate As String
    Set Connection = New ADODB.Connection
    Connection.ConnectionString = XYZConnection
    Set RS = New ADODB.Recordset
    
    DaDate = "SELECT * FROM dbo.ARB_DT_Date WHERE (dt_DateValue =  '" _
        & Application.Text(FindDTID, "mm/dd/yyyy") & "')"
        Connection.Open
    RS.Open DaDate, Connection
    If RS.EOF = True Then
        GoTo ErrHandle
    Else
        RetrDate = RS.Fields("dt_id")   'DT_ID for settlement
        Connection.Close
    End If
    Set RS = Nothing		'Don't forget to release
    Exit Sub
ErrHandle:
  '  ErrString = "DTid"
    Connection.Close
    MsgBox "The date was not found in lookups. Err # " & Err.Number _
        & " - " & Err.Source, vbCritical, "Errors"
End Sub

Having a source file with all your common (reusable) functions saves a lot of headache down the road.

© 2006-2017

Updated:  06/14/2017 17:53
This page added:  14 September 2006