Results 1 to 9 of 9

Thread: Execute SQL From Excel (VBA)

  1. #1
    Junior Member Mechanic's Avatar
    Join Date
    Mar 2011
    Posts
    12
    Rep Power
    0

    Lightbulb Execute SQL From Excel (VBA)

    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)

    Code:
    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:
    Code:
     
    Me.ComboBox1.List = Application.Transpose(SQLJuicer("SELECT Name FROM Employee", "C:\Mydatabase.mdb", ,True))
    To Execute A SQL:
    Code:
     
    Call SQLJuicer(YourSQLString,"C:\Mydatabase.mdb")
    And to get data to a Range:
    Code:
     
    Call SQLJuicer(YourSQLString,"C:\Mydatabase.mdb",Worksheets("Sheet1").Range("A1"))
    Last edited by Mechanic; 05-18-2011 at 02:01 PM. Reason: examples added
    Mechanic!
    ------------------------------------------------------------------------------------------------------------------------
    //Caffeine is the only way to make my brain run in single-threaded mode. //

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10

  3. #3
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    Mechanic
    I want to get into the SQL statements - so this is very helpful.

    Do you have code that shows how to add a new table as well as how to add fields and set the properties for the added fields.
    xl2007 - Windows 7
    xl hates the 255 number

  4. #4
    Junior Member Mechanic's Avatar
    Join Date
    Mar 2011
    Posts
    12
    Rep Power
    0
    Hi Rasm,

    For that you have to pass a Create Table statement like:

    Code:
    Call SQLJuicer("Create Table Employee (Name TEXT(100), Address TEXT(255), Age Number)","C:\Mydatabase.mdb")
    The above SQL will create a table named 'Employee' with 3 fields Name (Text),Address(Text),Age(Number) and similarly you can write 'AlterTable' statement to modify existing tables.

    Hope this helps... MC
    Mechanic!
    ------------------------------------------------------------------------------------------------------------------------
    //Caffeine is the only way to make my brain run in single-threaded mode. //

  5. #5
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    Coool - I try that - thanks
    xl2007 - Windows 7
    xl hates the 255 number

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    If you are using an Excel 2007 and Access 2007 combo, you should use the following connection string

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & ";Jet OLEDB:Database Password=PWD;"

    Where strDBPath is the variable that contains the path, and "PWD" is your password
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member Mechanic's Avatar
    Join Date
    Mar 2011
    Posts
    12
    Rep Power
    0
    Agree!
    Mechanic!
    ------------------------------------------------------------------------------------------------------------------------
    //Caffeine is the only way to make my brain run in single-threaded mode. //

  8. #8
    Grand Master
    Join Date
    Apr 2011
    Posts
    22
    Rep Power
    10
    For Excel 2007 connections strings, try Connection strings for Excel 2007

    For other connection strings, visit http://www.connectionstrings.com/

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    To run SQL from within Excel where a sheet can act as a database / table, use the modified version of Mechanic's SQLJuicer

    Code:
    Function SQLJuicer(strSQLString As String, strDataBaseAddress As String, Optional rngWhereToPasteRange As Range, Optional blnUsingAccessRDB As Boolean = True, Optional blnReturnListArrayInstead As Boolean = True) As Variant
     
        Dim adoConnection   As Object 'New ADODB.Connection
        Dim adoRcdSource    As Object 'New ADODB.Recordset
        Dim strDBPath As String
     
        Set adoConnection = CreateObject("ADODB.Connection")
        Set adoRcdSource = CreateObject("ADODB.Recordset")
        On Error GoTo Errs:
        strDBPath = strDataBaseAddress
        
        If blnUsingAccessRDB Then
            'For Access DB
            adoConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath
        Else
            'For Excel DB
            adoConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDBPath & "; Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
        End If
     
        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
            ElseIf Not rngWhereToPasteRange Is Nothing Then
                rngWhereToPasteRange.CopyFromRecordset adoRcdSource
            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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. SQL output from Excel VBA macro
    By goldenbutter in forum Excel Help
    Replies: 3
    Last Post: 05-07-2013, 08:07 PM
  2. Upload Excel Data to SQL Table
    By littleiitin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 08-22-2012, 11:02 AM
  3. SQL in Excel
    By Nishant Choudhary in forum Excel Help
    Replies: 2
    Last Post: 01-24-2012, 05:55 AM
  4. Replies: 2
    Last Post: 11-17-2011, 07:49 PM
  5. Execute SQL From Excel (VBA)
    By Mechanic in forum Excel Help
    Replies: 0
    Last Post: 05-13-2011, 10:27 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •