SQL connections and data import into Excel

Option Explicit



'
'  ::::: GENERIC CONNECTION STRING FUNCTION
'

Function constrLD(enumC As Integer, Optional MyInitialDB As String = "MY_DATABASE_NAME") As String



Select Case enumC

Case 1:
constrLD = "Provider=sqloledb;;Data Source=MyServer;Password=MyPa$$word;Persist Security Info=True;User ID=MyUserName;Initial Catalog=" & MyInitialDB & ";"
Case 2:
constrLD = "OLEDB;Provider=sqloledb;Data Source=MyServer;Initial Catalog=" & MyInitialDB & ";Integrated Security=SSPI "
Case Else:
constrLD = ""

End Select

End Function


'
'::::::::::::::::::: RESULTS TO SHEET ::::::::::::::::::
'

Sub SQL_TO_SHEET(STRcon As String, STRqry As String, Optional JustWrite As Boolean, Optional MyTimeout As Long = 12000)

Dim statement As String

Dim conn As ADODB.Connection, rSRESULT As Recordset

Set conn = New ADODB.Connection

Dim objCommand As ADODB.Command
Set objCommand = New ADODB.Command
Dim IntColindex As Integer  ' :: INDEX VARIABLE FOR LOOP ::

statement = "set NOCOUNT ON;" & Chr(13)
statement = statement & STRqry

conn.ConnectionString = STRcon

conn.Open


objCommand.CommandTimeout = 9999999
objCommand.ActiveConnection = conn


':: EXECUTE TO RESULT SET ::

    
Set rSRESULT = conn.Execute(statement)


'---------------------------------------------------

' :: RETURN VALUES IF SET TO RETURN RECORDSET ::
If JustWrite = False Then
    ActiveSheet.Range("A2").CopyFromRecordset rSRESULT
    ' ::GET HEADER INFO ::
    For IntColindex = 0 To rSRESULT.Fields.Count - 1
        Range("A1").Offset(0, IntColindex).Value = rSRESULT.Fields(IntColindex).Name
    Next
End If

'---------------------------------------------------

' :: CLOSE CONNECTION ::
    conn.Close


End Sub


'
'::::::::::::::::::: RESULTS TO STRING ::::::::::::::::::
'



Function SQL_TO_STRING(STRcon As String, STRqry As String, ColNamSrch As String) As String

Dim statement As String, ColumnMatch As Integer, IntColindex As Integer

Dim conn As ADODB.Connection, rSRESULT As Recordset

Set conn = New ADODB.Connection


conn.ConnectionString = STRcon
    
conn.Open

statement = "set NOCOUNT ON;" & Chr(13)

statement = STRqry


Set rSRESULT = conn.Execute(statement)


For IntColindex = 0 To rSRESULT.Fields.Count - 1
    If rSRESULT.Fields(IntColindex).Name = ColNamSrch Then ColumnMatch = IntColindex
Next
    

On Error Resume Next
SQL_TO_STRING = rSRESULT.Fields(ColumnMatch)



' :: CLOSE CONNECTION ::
    conn.Close


End Function


'
'::::::::::::::::::: RESULTS TO ARRAY ::::::::::::::::::
'



Function SQL_TO_ARRAY(STRcon As String, STRqry As String, ColNamSrch As String) As Variant()

Dim statement As String, ColumnMatch As Integer, IntColindex As Integer
Dim conn As ADODB.Connection, rSRESULT As Recordset
Dim RtnArray() As Variant  ' Array for results ( as string )
Dim RsCnt As Integer '  Range of array.
Dim LP As Integer ' GENERIC LOOP

Set conn = New ADODB.Connection


conn.ConnectionString = STRcon   'constrLD(1)
    
conn.Open

statement = "set NOCOUNT ON;" & Chr(13)
statement = statement & STRqry


Set rSRESULT = conn.Execute(statement)

rSRESULT.MoveFirst

Do While rSRESULT.EOF = False
    rSRESULT.MoveNext
    RsCnt = RsCnt + 1
Loop


rSRESULT.MoveFirst

ReDim RtnArray(RsCnt)


For LP = 1 To RsCnt
    RtnArray(LP) = rSRESULT.Fields(ColNamSrch)
    rSRESULT.MoveNext
Next LP


' :: CLOSE CONNECTION ::
    conn.Close

SQL_TO_ARRAY = RtnArray  ' RE-CAST THE TEMP ARRAY TO THE MAIN ARRAY


End Function


'
' ::: EXECUTE ONLY ::::
'



Function SQL_EXEC_ONLY(STRcon As String, STRqry As String) As Boolean

Dim statement As String, ColumnMatch As Integer, IntColindex As Integer

Dim conn As ADODB.Connection, rSRESULT As Recordset


SQL_EXEC_ONLY = False  ' :: SET INITIAL STATE TO FAILED ::

Set conn = New ADODB.Connection

conn.ConnectionString = STRcon
    
conn.Open

statement = "set NOCOUNT ON;" & Chr(13)

statement = STRqry

' Execute the statement.
For LP = 1 To 5
Debug.Print Chr(13)
Next LP

On Error GoTo ErrorCloseConn
Set rSRESULT = conn.Execute(statement)


SQL_EXEC_ONLY = True
GoTo CloseConn

ErrorCloseConn:
SQL_EXEC_ONLY = False
Debug.Print ":: ## : FAILED : ## ::" & vbNewLine & "[" & statement & "]" & conn.Errors.Item(0).Description

CloseConn:
' :: CLOSE CONNECTION ::
    conn.Close


End Function