Execute SQL From Excel (VBA)

[Mechanic]

Hi,

This can be used to Fetch(to sheet/control)/Modify/Excute SQL's

-Requires reference to Microsoft ActiveX Data Objects 2.8 Library
(I know a lot of would say can be done with Late binding but i perfer this)

Function SQLJuicer(strSQLString As String, strDataBaseAddress As String, Optional rngWhereToPasteRange As Range, Optional blnReturnListArrayInstead As Boolean = True) As Variant
 
    Dim adoConnection   As New ADODB.Connection
    Dim adoRcdSource    As New ADODB.Recordset
    Dim strDBPath As String
 
    On Error GoTo Errs:
    strDBPath = strDataBaseAddress
 
    adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDBPath
 
    If UCase(Left(strSQLString, 6)) = "SELECT" Then
        adoRcdSource.Open strSQLString, adoConnection, 3
 
        If rngWhereToPasteRange Is Nothing And blnReturnListArrayInstead = True Then
            If (adoRcdSource.BOF Or adoRcdSource.EOF) = False Then
                SQLJuicer = adoRcdSource.GetRows
            End If
        ElseIf Not rngWhereToPasteRange And blnReturnListArrayInstead = False Then
            With rngWhereToPasteRange
                .ClearContents
                .Cells(1).CopyFromRecordset adoRcdSource
                adoRcdSource.Close
            End With
        End If
    Else
        adoConnection.Execute strSQLString
    End If
 
    GoTo NormalExit
Errs:
    MsgBox Err.Description, vbCritical, "Error!"
    Err.Clear: On Error GoTo 0: On Error GoTo -1
 
NormalExit:
    Set adoConnection = Nothing
    Set adoRcdSource = Nothing
    strDBPath = vbNullString
 
End Function

To Load a List to List Box/Combo Box:

 
Me.ComboBox1.List = Application.Transpose(SQLJuicer("SELECT Name FROM Employee", "C:\Mydatabase.mdb", ,True))

To Execute A SQL:

 
Call SQLJuicer(YourSQLString,"C:\Mydatabase.mdb")

And to get data to a Range:

 
Call SQLJuicer(YourSQLString,"C:\Mydatabase.mdb",Worksheets("Sheet1").Range("A1"))