Utilizing Stored procedure parameters in VBA

[nockvb]

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

[snb]

Are you familiar with code tags ?

[nockvb]

sorry about that. I just fixed it.

[Bob Phillips]

You need to set the SP to have parameters, and if you use the ADO Command, you can easily pass parameters via that method.

[SamT]

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.

[nockvb]

Here is the project like requested.

[Bob Phillips]

Where does this invoke a stored procedure?

[nockvb]

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.

[Bob Phillips]

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.

[snb]

You might benefit from:

http://www.snb-vba.eu/VBA_Userform_in_database_en.html

[SamT]

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.

[nockvb]

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.

[nockvb]

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.

[Bob Phillips]

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.

[SamT]

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.

[nockvb]

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.

[nockvb]

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.....

[Kenneth Hobs]

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

[SamT]

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

[nockvb]

Thank you Kenneth and SamT........I will use your suggestions and see if I can get to what I am trying to accomplish.