April 16, 2009 at 3:07 am
I am working with an Access front end and SQL server b/e on a distributed database that has been in existance for some time. The current design downloads recordsets of upwards of probally 30mill records to the local macine to be processed. This has fine in the past but now excedes network capabilities. I have written stored procedures to run on the b/e but just can't quite work out the best way to pass parameters to them.
Ideas Please
April 16, 2009 at 9:06 am
I've used an ADODB.Command for this. Here's a sample that I've used:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.MyStoredProcedure"
With cmd.Parameters
.Append cmd.CreateParameter("Return", adInteger, adParamReturnValue)
.Append cmd.CreateParameter("Param1", adInteger, adParamInput, , 1)
.Append cmd.CreateParameter("Param2", adVarChar, adParamInput, 8000, "MyString")
End With 'cmd.Parameters
cmd.Execute
Set cmd = Nothing
This was in an ADP so the Connection was simple. If you're using a regular database as your front end, you will need to specify the connection string.
April 17, 2009 at 9:25 am
I'm in the same boat. Currently with a broken paddle. Based on the suggested code, I tried...
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.getModelOffice"
With cmd.Parameters
.Append cmd.CreateParameter("Param1", adVarChar, adParamInput, 8000, "WL")
End With 'cmd.Parameters
cmd.Execute
Set cmd = Nothing
This runs, but without displaying any records. Do I need to use a recordset or something else. If so...how?
Any help is greatly appreciated. This problem has been driving me bonkers for quite some time now.
Thanks
April 17, 2009 at 1:15 pm
The code I had was to strictly run a stored procedure. Unfortunately, I've never tried to return a recordset from a stored procedure. Here's some info I copied from Access Help that might point you in the right direction. Note the section I bolded.
Use a Command object to query a database and return records in a Recordset object, to execute a bulk operation, or to manipulate the structure of a database. Depending on the functionality of the provider, some Command collections, methods, or properties may generate an error when referenced.
With the collections, methods, and properties of a Command object, you can do the following:
Define the executable text of the command (for example, an SQL statement) with the CommandText property.
Define parameterized queries or stored-procedure arguments with Parameter objects and the Parameters collection.
Execute a command and return a Recordset object if appropriate with the Execute method.
Specify the type of command with the CommandType property prior to execution to optimize performance.
Control whether the provider saves a prepared (or compiled) version of the command prior to execution with the Prepared property.
Set the number of seconds that a provider will wait for a command to execute with the CommandTimeout property.
Associate an open connection with a Command object by setting its ActiveConnection property.
Set the Name property to identify the Command object as a method on the associated Connection object.
Pass a Command object to the Source property of a Recordset in order to obtain data.
Access provider-specific attributes with the Properties collection.
Note
To execute a query without using a Command object, pass a query string to the Execute method of a Connection object or to the Open method of a Recordset object. However, a Command object is required when you want to persist the command text and re-execute it, or use query parameters.
To create a Command object independently of a previously defined Connection object, set its ActiveConnection property to a valid connection string. ADO still creates a Connection object, but it doesn't assign that object to an object variable. However, if you are associating multiple Command objects with the same connection, you should explicitly create and open a Connection object; this assigns the Connection object to an object variable. If you do not set the Command object's ActiveConnection property to this object variable, ADO creates a new Connection object for each Command object, even if you use the same connection string.
To execute a Command, simply call it by its Name property on the associated Connection object. The Command must have its ActiveConnection property set to the Connection object. If the Command has parameters, pass their values as arguments to the method.
If two or more Command objects are executed on the same connection and either Command object is a stored procedure with output parameters, an error occurs. To execute each Command object, use separate connections or disconnect all other Command objects from the connection.
April 24, 2009 at 8:40 am
Little known fact - you can execute stored procedures with an Access VBA code command like the examples below. We use this format all the time and it has the added benefit that it is easier (for me anyway!) to read and looks very similar to T-SQL.
' Basic setup - Create a connection object to the SQL database
Dim gConDB As New ADODB.Connection
Set gConDB = New ADODB.Connection
gConDB = CurrentProject.Connection
gConDB.Open strConnectionString 'Should have been set up earlier
' Once you have done that you can call stored procedures directly by name and pass parameters
' almost as if you were doing this in T-SQL
' e.g. SQL code would look like: EXEC sptblA_Insert 'First Data item', 99, 'Third data item'
' Access code would be
gConDB.sptblA_Insert "First Data item", 99, "Third data item"
'or
gConDB.sptblA_Insert Me.FieldOne, 99, strWithSecondData
' You don't need to use single quotes for the string data as JET will convert Access syntax to SQL.
' You can also execute T-SQL directly:
gConDB.Execute "INSERT tblA (FirstStringField, FirstNumber, SecondStringField) _
& " VALUES 'First Data Item', 99, 'Second Data Item' "
' When doing this, you do have to use exact T_SQL syntax
' To return records you set up an ADO recordset and pass it to the gConDb call as the last parameter
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
gConDB.spGetMeSomeData WithThisKey lngEndID, rs
' Data return is managed by Jet (invisibly), so as long as your T-SQL stored procedure on the server runs in
' under the time-out limit, you can get as many rows back as you want.
' The greates benefit to using this technique is that the data that comes back has all the same field names as
' it has been given in the stored procedure.
If rs.EOF And rs.BOF Then ' NO DATA returned
'Do something about no data returned
Else
strFirstValueIneed = rs.NameOfFieldNeeded
' You can also get data by relative field index (0 based), but that's not good programming practice IMHO
strFirstFieldIneed = rs(0)
'BIG CAVEAT: If the T-SQL stored proc is using nested stored procedures, you tend to get multiple record sets returned which upsets Jet, so ALWAYS put a SET NOCOUNT ON at the beginning of the stored procedures you are calling and SET NOCOUNT OFF at the end (not so important). We put them at the beginning and end of ALL stored procedures including the ones being called from the original T-SQL stored procedure.
Hope this is enough to get you started.
-Rob Marmion (Can I put my company name and contact info?)
"The purpose of life is a Life of Purpose"
May 1, 2009 at 11:19 am
We accomplish this by using a form which captures the parameters and ends with an 'OK' button. The 'OK' button is an event which creates a SQL command string to execute the stored procedures and includes the parameters. The event then opens the report which uses the Pass-
Through query as a source. This works well and it's simple to debug because you can look at the PT query and execute it directly in query analyzer (if you built it correctly).
Another way would be to use a form which populates a linked table to store the parameters - then the stored procedure would pick up the parameters from the table. This could still use a PT query but it would be static instead of recreated at each run (since it includes the parameters).
May 16, 2009 at 9:51 am
Here's one more way to do that in an MDB file:
Create a pass-thru query, and in the button click event on your form, you re-write the SQL string with the parameter.
For example:
the procedure is prcMyProc with either a string or date parameter
the parameter is the value of MyControl on the form MyForm
you have created a pass-thru query named MyQuery
Private Sub MyButton_Click()
CurrentDB.QueryDefs("MyQuery").SQL = "prcMyProc '" & Forms!MyForm!MyControl & "' "
DoCmd.OpenQuery "MyQuery"
End Sub
if the parameter is numeric then you would leave out the single quotes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply