Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

Thread: Utilizing Stored procedure parameters in VBA

  1. #1
    VBAX Regular
    Joined
    Mar 2014
    Posts
    13
    Location

    Utilizing Stored procedure parameters in VBA

    I have Userform that does manupulate a spreadsheet by reading the column titles and rows of the cells to add or edit the values. What I want to do now is modify this so I can pass in the parameters with the text box with a stored procedure that looks into a data file in a spreadsheet.

    Here is how I have it working:

    The text Fileds are: ID, Name, Title

    The first text box has this code:
        Private Sub TextBox1_Change()  
        GetData  
        End Sub
    The commandButtons have these codes:

    Private Sub CommandButton1_Click()  
        EditAdd  
        End Sub
    
        Private Sub CommandButton2_Click()
        ClearForm
        End Sub
    
        Private Sub CommandButton3_Click()
        Unload Me
        End Sub
    NOW THE Functions and Subs
    Dim id As Integer, i As Integer, j As Integer, flag As Boolean 
        Sub GetData()
    
        If IsNumeric(UserForm1.TextBox1.Value) Then  
         flag = False  
         i = 0  
         id = UserForm1.TextBox1.Value  
    
    
         Do While Cells(i + 1, 1).Value <> ""
    
             If Cells(i + 1, 1).Value = id Then
                 flag = True
                 For j = 2 To 3
                     UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
                 Next j
             End If
    
             i = i + 1
    
         Loop
    
         If flag = False Then
             For j = 2 To 3
     UserForm1.Controls("TextBox" & j).Value = ""
             Next j
         End If
    
        Else  
         ClearForm  
        End If
    
        End Sub 
     
        Sub ClearForm()  
    
        For j = 1 To 3 
         UserForm1.Controls("TextBox" & j).Value = ""  
        Next j  
    
        End Sub 
     
    
        Sub EditAdd() 
        Dim emptyRow As Long
    
        If UserForm1.TextBox1.Value <> "" Then  
         flag = False  
         i = 0  
         id = UserForm1.TextBox1.Value  
         emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1  
    
         Do While Cells(i + 1, 1).Value <> ""  
     
             If Cells(i + 1, 1).Value = id Then  
                 flag = True  
                 For j = 2 To 3  
                     Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value  
                 Next j  
             End If  
    
             i = i + 1  
    
         Loop  
    
         If flag = False Then  
             For j = 1 To 3  
                 Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value  
             Next j  
         End If  
    
        End If  
    
        End Sub
    THE STORED PROCEDURE THAT WILL CREATE THE TABLE VALUES IN THE SPREADSHEET:

    Select ID, Name, Title From Employees
    Last edited by Simon Lloyd; 04-04-2014 at 01:20 PM. Reason: changed quotes for code tags

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,577
    Are you familiar with code tags ?

  3. #3
    VBAX Regular
    Joined
    Mar 2014
    Posts
    13
    Location
    sorry about that. I just fixed it.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,086
    Location
    You need to set the SP to have parameters, and if you use the ADO Command, you can easily pass parameters via that method.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,893
    Location
    Nockvb,

    Can you upload a copy of the workbook with the form and the worksheet with the Data File.

    In order to make your code self explanatory and efficient, some changes should be made, and it is much easier to show you the changes, instead of walking you thru them one at a time.
    Please take the time to read the Forum FAQ

  6. #6
    VBAX Regular
    Joined
    Mar 2014
    Posts
    13
    Location
    Here is the project like requested.
    Attached Files Attached Files

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,086
    Location
    Where does this invoke a stored procedure?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Regular
    Joined
    Mar 2014
    Posts
    13
    Location
    I guess my initial post wasn't clear but I said that I'm trying to get some help as to how to convert what I have already so it can pass a stored procedure value via the text field and affect the table dump on the sheet. Pass the Parameter values into the worksheet where I will have the table record set with the ID, Name, Title column values showing.

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,086
    Location
    So you are not talking about database stored procedures at all. The line

    Select ID, Name, Title From Employees
    in your first post sure looks like SQL.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,577

  11. #11
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,893
    Location
    Is this what you are trying to say?
    I guess my initial post wasn't clear but I said that I'm trying to get some help as to how to convert what I have [in the UserForm] already so it can pass [to] a stored [SQL] procedure [the required parameters] via the [UserForm's] text[Box] field[s] and ... dump ... [the returned] values [from the Stored Procedure] into the worksheet where I will have the table ... with the ID, Name, Title column ...[Heads] showing.
    Please take the time to read the Forum FAQ

  12. #12
    VBAX Regular
    Joined
    Mar 2014
    Posts
    13
    Location
    xld........... Yes that is a stored procedure call indeed but that is not what I was asking for. I was asking for someone to help me convert my initial process so it will incorporate stored procedure like the one I posted. I don't understand why that is not clear unless you want to make it difficult.

    SamT..........yes that is what I'm trying to accomplish.

  13. #13
    VBAX Regular
    Joined
    Mar 2014
    Posts
    13
    Location
    snb........thanks for the link I will check it out. At the same time good to know someone actually understands what I was trying to accomplish.

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,086
    Location
    What you posted was not a stored procedure, it was just inline SQL. Of course that can be incorporated in an SP, but an SP needs a definition (such as the name that will be used when executing it, and any parameters), and the execution needs to be defined, as I assume that you won't invoking it from SSMS or the Access client. What you asked for is so trivial, I assumed that you were asking how to pass that parameter in a call from Excel. From your answers, and the page that snb directed you too,it appears that you do not mean a database at all, Excel is NOT a database, at best it is a (poor) data container, and doesn't support stored procedures.

    Why you posted that SQL is beyond my comprehension.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,893
    Location
    OK!

    Now let me condense all of your posts into a form that everybody can understand.

    The OP wants to use a Stored Procedure [details unknown] in an [unknown] database to retrieve certain [unknown] values and insert those values in a worksheet.
    @ Nockvb, as you can see, we just don't have enough information yet to help you. We need to know the unknown.
    Please take the time to read the Forum FAQ

  16. #16
    VBAX Regular
    Joined
    Mar 2014
    Posts
    13
    Location
    xld.......are you kidding me? If you don't want to help then just don't waste your time posting at all. I understand what I posted was a line of SQL code that is going to be used in my proc. being a smart A** about it is just elementary. If you don't want to help then don't help.

  17. #17
    VBAX Regular
    Joined
    Mar 2014
    Posts
    13
    Location
    SamT....... hahahaha. Yes something like that. I figured people would just create a test scenario to explain how it works. I didn't think I needed to explain every detail. I thought that was a given. I guess in this forum I have to. Oh well.

    Here are details if anyone wants to help but it looks like they truly are not here to help:

    Database Name: DBTest

    StoredProcedure: SimpleTest.sql

    with this simple statement: yeah yeah to the picky people I understand there are many tags that go with it but this is as simple as I can make it without dumping ever single line.

    Select ID, Name, Title From Employees
    This statement will get those values for those columns and post it on the sheet. (I can't make that any simpler and clearer).

    So the main question is that how can I pass values via my textbox and manipulate the data on that sheet. It can be to filer it or add/edit etc.....

  18. #18
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,884
    Location
    XLD and snb are experts so if they are confused, I stand no chance of helping you. That never stopped me from trying though.

    I am not sure what your level of expertise is. From what I see, like the others, I don't think you understand stored procedures. These are SQL routines stored in a database file like Access. When you say database, what is the type SQLLite, SQLServer, MySQL, Access, etc. It matters greatly, ergo requests for such dtails has been made.

    It looks to me that you are just wanting to query an Excel file. In either case if Excel or a true database program, one helper said to use ADO. There are several ways to use ADO. Of course to use ADO, it is all in the details, ergo the many requests for more details from you to better help you, not to deride you.

    See if this or the next code which is directly related to stored procedures gives you any ideas to accomplish your goals.

    'romperstomper, http://www.excelforum.com/excel-programming/665066-send-results-of-sql-query-to-an-array.html
    
    
    ' Tools > References > Microsoft ActiveX Data Objects 2.8 Library
    ' or
    ' Tools > References >  Microsoft ActiveX Data Objects 6.0 Library
    Sub GetData()
       ' Sample demonstrating how to return a recordset from a workbook
       Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.RecordSet, strConn As String
       Dim varData As Variant
       Dim wbName As String
       Set cn = New ADODB.Connection
    '   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";" & _
    '         "Extended Properties=""Excel 8.0;HDR=Yes;"""
    
    'XLSM files in Excel 2010 via ConnectionStrings.com:
    'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;
    'Extended Properties="Excel 12.0 Macro;HDR=YES";
       'wbName = ActiveWorkbook.FullName
       wbName = """" & "\\matpc37\Excel\Test\Test.xlsm" & """"
       With cn
          .Provider = "Microsoft.ACE.OLEDB.12.0"
          .ConnectionString = "Data Source=" & wbName & ";" & _
             "Extended Properties=""Excel 12.0 Macro;HDR=Yes;"""
          .Open
       End With
       strQuery = "SELECT * FROM [Sheet1$];"
       Set rst = New ADODB.RecordSet
       rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
       ' dump array of data into variable
       'varData = rst.GetRows
       Worksheets(2).Range("A1").CopyFromRecordset rst
       rst.Close
       Set rst = Nothing
    '   cn.Close
       Set cn = Nothing
    End Sub
    For a stored SQL in Access:
    Option Explicit
    'http://msdn.microsoft.com/en-us/library/office/aa188518%28v=office.10%29.aspx
    'http://www.vbaexpress.com/forum/showthread.php?t=24118
    'http://www.vbaexpress.com/forum/showthread.php?t=24575
    'http://www.vbaexpress.com/forum/showthread.php?t=23783
    'http://www.vbaexpress.com/forum/showthread.php?t=26145
    Sub Test()
      Dim mdbPath As String, dbName As String, cmdText As String
      Dim rngDestination As String
      'mdbPath = "E:\ADO\NWind2003.mdb"    'change the path here to suit your needs
      'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
      mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
      dbName = "NWind2003_1"              'change the database name here to suit your needs
      cmdText = "Aug94"                   'change the stored SQL here to suit your needs
      rngDestination = "A1"               'change the destination range here to suit your needs
      
      'Clear previous data
      Cells.Delete
    
      InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
      
      'Insert other data to the right of A1 with a blank column separating the two
      rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
      cmdText = "Sales by Category"
      InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
    End Sub
    
    ' http://www.vbaexpress.com/forum/showthread.php?t=43307
    Sub Test_InsertTableWithStoredSQL()
      Dim databaseName() As Variant, cmdText() As Variant, i As Integer
       '
      databaseName = Array("qOne", "qTwo", "qThree")
      cmdText() = Array("Aug94", "Order Subtotals", "Sales by Category")
           
      For i = LBound(databaseName) To UBound(databaseName)
        Debug.Print databaseName(i), cmdText(i), Range("A" & Rows.Count).End(xlUp).Offset(1).Address
          InsertTableWithStoredSQL "c:\myfiles\edrive\excel\ado\NWind2003.mdb", _
            CStr(databaseName(i)), CStr(cmdText(i)), Range("A" & Rows.Count).End(xlUp).Offset(1).Address, _
            True
      Next i
    End Sub
    
    Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
      cmdText As String, rngDestination As String, _
      Optional bFieldNames = True)
    
        With ActiveSheet.QueryTables.Add(Connection:=Array( _
                                                     "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & mdbPath & ";Mode=ReadWrite;Extended Properties=""" _
           , """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab" _
           , "ase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";J" _
           , "et OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co" _
           , "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("" & rngDestination & ""))
            .CommandType = xlCmdTable
            .CommandText = Array(cmdText)
            .Name = dbName
            .FieldNames = bFieldNames
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceDataFile = mdbPath
            .Refresh BackgroundQuery:=False
        End With
    End Sub
    Wether Access or other database, the main thing you need to get right is the connection string. ConnectionStrings.com

    For more on ADO, search this site or: http://www.erlandsendata.no/english/...php?t=envbadac

  19. #19
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,893
    Location
    SamT....... hahahaha. Yes something like that
    Yes, my bad. I forgot the first and most important part of your question. How to pass the values from the UserForm's controls into the Stored Procedure.

    With UserForm1
    IDparam = .TextBox1
    Nameparam = .TextBox2
    Titleparam= .TextBox3
    End With
    '
    '
    'From Kenneth's example
    cmdText = 'Concatenate Parameter Names and the above Variables here
    Last edited by SamT; 04-07-2014 at 08:20 AM.
    Please take the time to read the Forum FAQ

  20. #20
    VBAX Regular
    Joined
    Mar 2014
    Posts
    13
    Location
    Thank you Kenneth and SamT........I will use your suggestions and see if I can get to what I am trying to accomplish.

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
  •