PDA

View Full Version : Utilizing Stored procedure parameters in VBA



nockvb
04-04-2014, 11:46 AM
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
04-04-2014, 11:54 AM
Are you familiar with code tags ?

nockvb
04-04-2014, 12:46 PM
sorry about that. I just fixed it.

Bob Phillips
04-04-2014, 03:07 PM
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
04-04-2014, 03:09 PM
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
04-05-2014, 12:12 PM
Here is the project like requested.

Bob Phillips
04-05-2014, 04:40 PM
Where does this invoke a stored procedure?

nockvb
04-05-2014, 04:59 PM
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
04-06-2014, 02:36 AM
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
04-06-2014, 04:22 AM
You might benefit from:

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

SamT
04-06-2014, 10:31 AM
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
04-06-2014, 01:04 PM
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
04-06-2014, 01:06 PM
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
04-06-2014, 02:27 PM
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
04-06-2014, 02:53 PM
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
04-07-2014, 06:33 AM
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
04-07-2014, 06:57 AM
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
04-07-2014, 07:34 AM
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/index.php?t=envbadac

SamT
04-07-2014, 08:10 AM
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
04-07-2014, 03:13 PM
Thank you Kenneth and SamT........I will use your suggestions and see if I can get to what I am trying to accomplish.

snb
04-08-2014, 12:43 AM
Assuming:

- a database G:\Access\fiets.mdb
- a 'stored procedure' (= Query), name Q_test

you can retrieve the data from this database, using the stored procedure 'Q_test', and put them in a table in sheet1, using:


Sub M_snb()
With Sheet1.ListObjects.Add(0, "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Access\fiets.mdb", , , Range("$A$1")).QueryTable
.CommandType = 3
.CommandText = "Q_test"
.Refresh False
End With
End Sub

the same result using the Microsoft ActiveX Data Objects 2.0 Library:


Sub Access_query_snb()
' Reference to Microsoft ActiveX Data Objects 2.0 Library

With New Recordset
.Open "SELECT * FROM Q_test", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Access\fiets.mdb"
Sheet1.Cells(20, 1).CopyFromRecordset .DataSource
End With
End Sub