Use the input to an Access 2003 form as parameters to a SQL Server stored procedure

I've got to let end users (really just one person) load billing batch
files into a third party app table. They need to specify the billing
cycle name, the batch name, and the input file name and then I can use
these values to execute a SQL Server stored procedure to load them
into the batch table from the input file (the stored procedure uses
Dynamic SQL to Bulk Insert the file to a temp table then processes it
into the final table.)

My first idea was to give them (really just one person) an Access form
front end to a linked table in the SQL Server database and let them
input the three values into that linked table. Then I could run the
stored procedure from an insert trigger on the table on the SQL
Server. Unfortunately, you can't run Bulk Insert from a trigger.

I can't think of any other way to do it. The non technical end user
has to be able to fill in the three values somehow and kick off the
SQL Server stored procedure.

Any ideas? Fast and dirty is fine.

re: Use the input to an Access 2003 form as parameters to a SQL Server stored procedure

On Thu, 29 Sep 2005 23:48:49 GMT, fumanchu <leondobr@verizon.net>
wrote:

You want fast and dirty: install the SQL Server Client Utilities on
the user's workstation. Among other things, this installs Query
Analyzer. You can create a .SQL file that the user can load into this
app, enter the values for a few parameters, and click the button to
run the sproc.

Slow and clean? Create an Access ADP (data project) to handle this
using a form and calling the sproc over ADO.

-Tom.


[color=blue]
>I've got to let end users (really just one person) load billing batch
>files into a third party app table. They need to specify the billing
>cycle name, the batch name, and the input file name and then I can use
>these values to execute a SQL Server stored procedure to load them
>into the batch table from the input file (the stored procedure uses
>Dynamic SQL to Bulk Insert the file to a temp table then processes it
>into the final table.)
>
>My first idea was to give them (really just one person) an Access form
>front end to a linked table in the SQL Server database and let them
>input the three values into that linked table. Then I could run the
>stored procedure from an insert trigger on the table on the SQL
>Server. Unfortunately, you can't run Bulk Insert from a trigger.
>
>I can't think of any other way to do it. The non technical end user
>has to be able to fill in the three values somehow and kick off the
>SQL Server stored procedure.
>
>Any ideas? Fast and dirty is fine.[/color]

re: Use the input to an Access 2003 form as parameters to a SQL Server stored procedure

fumanchu wrote:[color=blue]
> I've got to let end users (really just one person) load billing batch
> files into a third party app table. They need to specify the billing
> cycle name, the batch name, and the input file name and then I can use
> these values to execute a SQL Server stored procedure to load them
> into the batch table from the input file (the stored procedure uses
> Dynamic SQL to Bulk Insert the file to a temp table then processes it
> into the final table.)
>
> My first idea was to give them (really just one person) an Access form
> front end to a linked table in the SQL Server database and let them
> input the three values into that linked table. Then I could run the
> stored procedure from an insert trigger on the table on the SQL
> Server. Unfortunately, you can't run Bulk Insert from a trigger.
>
> I can't think of any other way to do it. The non technical end user
> has to be able to fill in the three values somehow and kick off the
> SQL Server stored procedure.
>
> Any ideas? Fast and dirty is fine.[/color]

I've done this before by using a passthough query. In VBA I change the
..SQL for a saved Passthrough query that has the appropriate Connection
settings already and the Returns Records Property set to No. You change
the .SQL to the EXEC statement for your SP:

Air Code:

Dim qd as DAO.Querydef
Set qd = CurrentDB.Querydefs("myPassthrough")
qd.SQL = "EXEC mySP '" & paramvalue1 & "', '" & _
pramvalue2 & "', '" & paramvalue3 & "'"
qd.Execute
set qd=Nothing

--
Bri

re: Use the input to an Access 2003 form as parameters to a SQL Server stored procedure

I'm not really sure this will work because of the Bulk Insert - seems
to be an ODBC error thing. Thank you anyway

For now I set up an Access Form for my user to load the three values
into a linked table on the server. I set up a SQL Server Agent task to
run every minute for an hour a day during peak season. Each time it
runs essentially it strips off the top row of the table without a
timestamp, sets the timestamp, and then feeds the three values to the
sp that loads the file into the batch table. When it finishes it sets
a different timestamp.

She can check her batches after a minute by reopening her form and by
looking for the timestamps.

Messy. Just awful, really. But it works.

I don't have a way to set up a SQL Server client on her machine nor do
I have time to learn VBA. It's straight Access or nothing for this one
shot solution.

I can get the batch name and accounting period from pulldowns linked
to tables. Is there an easy way to get file names from a given
directory into a pulldown?

Any more Rube Goldberg ideas, anyone?


On Fri, 30 Sep 2005 16:19:14 GMT, Bri <not@here.com> wrote:
[color=blue]
>
> fumanchu wrote:[color=green]
>> I've got to let end users (really just one person) load billing batch
>> files into a third party app table. They need to specify the billing
>> cycle name, the batch name, and the input file name and then I can use
>> these values to execute a SQL Server stored procedure to load them
>> into the batch table from the input file (the stored procedure uses
>> Dynamic SQL to Bulk Insert the file to a temp table then processes it
>> into the final table.)
>>
>> My first idea was to give them (really just one person) an Access form
>> front end to a linked table in the SQL Server database and let them
>> input the three values into that linked table. Then I could run the
>> stored procedure from an insert trigger on the table on the SQL
>> Server. Unfortunately, you can't run Bulk Insert from a trigger.
>>
>> I can't think of any other way to do it. The non technical end user
>> has to be able to fill in the three values somehow and kick off the
>> SQL Server stored procedure.
>>
>> Any ideas? Fast and dirty is fine.[/color]
>
>I've done this before by using a passthough query. In VBA I change the
>.SQL for a saved Passthrough query that has the appropriate Connection
>settings already and the Returns Records Property set to No. You change
>the .SQL to the EXEC statement for your SP:
>
>Air Code:
>
>Dim qd as DAO.Querydef
>Set qd = CurrentDB.Querydefs("myPassthrough")
>qd.SQL = "EXEC mySP '" & paramvalue1 & "', '" & _
> pramvalue2 & "', '" & paramvalue3 & "'"
>qd.Execute
>set qd=Nothing[/color]

re: Use the input to an Access 2003 form as parameters to a SQL Server stored procedure

<comments inline>
fumanchu wrote:[color=blue]
> I'm not really sure this will work because of the Bulk Insert - seems
> to be an ODBC error thing. Thank you anyway[/color]

My suggested solution will run ANY SP that requires Parameters. What the
SP does is a separate issue. The SP runs directly in SQL Server, no ODBC
except to send the Passthrough SQL to the server.
[color=blue]
> For now I set up an Access Form for my user to load the three values
> into a linked table on the server. I set up a SQL Server Agent task to
> run every minute for an hour a day during peak season. Each time it
> runs essentially it strips off the top row of the table without a
> timestamp, sets the timestamp, and then feeds the three values to the
> sp that loads the file into the batch table. When it finishes it sets
> a different timestamp.
>
> She can check her batches after a minute by reopening her form and by
> looking for the timestamps.
>
> Messy. Just awful, really. But it works.[/color]

I agree, messy. And unnecessary.
[color=blue]
> I don't have a way to set up a SQL Server client on her machine nor do
> I have time to learn VBA. It's straight Access or nothing for this one
> shot solution.[/color]

Without using VBA you won't be able to do a lot of things. It isn't that
complicated to learn the basics of it if you've ever done any
programming in any other language.
[color=blue]
> I can get the batch name and accounting period from pulldowns linked
> to tables. Is there an easy way to get file names from a given
> directory into a pulldown?[/color]

I don't know of a way that doesn't involve VBA. Again, the code is not
that complex, but there is code involved.
[color=blue]
> Any more Rube Goldberg ideas, anyone?[/color]

I personally try to make things simpler, but I don't always succeed. :{)
[color=blue][color=green]
>>I've done this before by using a passthough query. In VBA I change the
>>.SQL for a saved Passthrough query that has the appropriate Connection
>>settings already and the Returns Records Property set to No. You change
>>the .SQL to the EXEC statement for your SP:
>>
>>Air Code:
>>
>>Dim qd as DAO.Querydef
>>Set qd = CurrentDB.Querydefs("myPassthrough")
>>qd.SQL = "EXEC mySP '" & paramvalue1 & "', '" & _
>> pramvalue2 & "', '" & paramvalue3 & "'"
>>qd.Execute
>>set qd=Nothing[/color][/color]

I'll try to flesh this out a bit more now that I know you aren't
familiar with VBA.

Since you have already linked the SQL tables to the Access DB, I'll
assume you know what I mean by 'Connection'. So, from the Database
Window, go to the Queries Tab. Click 'New', then pick Design View. while
viewing the QBE grid, in the Menu, pick Query - SQL Specific -
Passthrough Query. You now have the SQL View of the query. View the
Properties Box. Click on the 'ODBC Connection String', then the button
with three dots. Make the same Connection that you did for the Linked
Tables. Then set 'Returns Records' to No (I'm assuming that the SP is
doing actions, not sending back records). Save the Query (for this
example I'm assuming it is called myPassthrough).

Next, go to Design View of the Form that has the controls that the user
will pick the Parameter values from. I'm assuming that the names of the
three controls are named; ParamControl1,ParamControl2, ParamControl3.
Create a Command Button (RunSPbutton). This will fire up the Wizard. You
can pick any process to setup the button, we will replace the code next.
In the Properties box click on - Events Tab - On Click Event - button
with three dots. You should now see the VBA code that the wizard setup
for you for whatever process you picked above. Its handy to leave the
Error trapping code there. Here is the code you will put there in place
of the Wizard Code:

Private Sub RunSPbutton_Click()
On Error goto Err_RunSPbutton_Click
Dim qd as DAO.Querydef
Set qd = CurrentDB.Querydefs("myPassthrough")
qd.SQL = "EXEC mySP '" & Me!ParamControl1 & "', '" & _
Me!ParamControl2 & "', '" & Me!ParamControl3 & "'"
qd.Execute

Exit_RunSPbutton_Click:
Set qd=Nothing
Exit Sub

Err_RunSPbutton_Click:
Msgbox Err.Description
Resume Exit_RunSPbutton_Click

End Sub

Then save it. The user will fill in the three controls and then click
the button, the SP will run on the server with the parameters.

Lets see how you do with this before getting into the VBA code for
loading a Combo with Filenames.
--
Bri

re: Use the input to an Access 2003 form as parameters to a SQL Server stored procedure

Thanks! I've managed to get this to work with my bits!
Tested and all.
It's the minor things that drive me nuts, like the quoting. Why the
double quotes around " & Me!ParamControl1 & " ? Can't find that
anywhere.


On Mon, 03 Oct 2005 17:58:33 GMT, Bri <not@here.com> wrote:
[color=blue]
> <comments inline>
>fumanchu wrote:[color=green]
>> I'm not really sure this will work because of the Bulk Insert - seems
>> to be an ODBC error thing. Thank you anyway[/color]
>
>My suggested solution will run ANY SP that requires Parameters. What the
>SP does is a separate issue. The SP runs directly in SQL Server, no ODBC
>except to send the Passthrough SQL to the server.
>[color=green]
>> For now I set up an Access Form for my user to load the three values
>> into a linked table on the server. I set up a SQL Server Agent task to
>> run every minute for an hour a day during peak season. Each time it
>> runs essentially it strips off the top row of the table without a
>> timestamp, sets the timestamp, and then feeds the three values to the
>> sp that loads the file into the batch table. When it finishes it sets
>> a different timestamp.
>>
>> She can check her batches after a minute by reopening her form and by
>> looking for the timestamps.
>>
>> Messy. Just awful, really. But it works.[/color]
>
>I agree, messy. And unnecessary.
>[color=green]
>> I don't have a way to set up a SQL Server client on her machine nor do
>> I have time to learn VBA. It's straight Access or nothing for this one
>> shot solution.[/color]
>
>Without using VBA you won't be able to do a lot of things. It isn't that
>complicated to learn the basics of it if you've ever done any
>programming in any other language.
>[color=green]
>> I can get the batch name and accounting period from pulldowns linked
>> to tables. Is there an easy way to get file names from a given
>> directory into a pulldown?[/color]
>
>I don't know of a way that doesn't involve VBA. Again, the code is not
>that complex, but there is code involved.
>[color=green]
>> Any more Rube Goldberg ideas, anyone?[/color]
>
>I personally try to make things simpler, but I don't always succeed. :{)
>[color=green][color=darkred]
>>>I've done this before by using a passthough query. In VBA I change the
>>>.SQL for a saved Passthrough query that has the appropriate Connection
>>>settings already and the Returns Records Property set to No. You change
>>>the .SQL to the EXEC statement for your SP:
>>>
>>>Air Code:
>>>
>>>Dim qd as DAO.Querydef
>>>Set qd = CurrentDB.Querydefs("myPassthrough")
>>>qd.SQL = "EXEC mySP '" & paramvalue1 & "', '" & _
>>> pramvalue2 & "', '" & paramvalue3 & "'"
>>>qd.Execute
>>>set qd=Nothing[/color][/color]
>
>I'll try to flesh this out a bit more now that I know you aren't
>familiar with VBA.
>
>Since you have already linked the SQL tables to the Access DB, I'll
>assume you know what I mean by 'Connection'. So, from the Database
>Window, go to the Queries Tab. Click 'New', then pick Design View. while
>viewing the QBE grid, in the Menu, pick Query - SQL Specific -
>Passthrough Query. You now have the SQL View of the query. View the
>Properties Box. Click on the 'ODBC Connection String', then the button
>with three dots. Make the same Connection that you did for the Linked
>Tables. Then set 'Returns Records' to No (I'm assuming that the SP is
>doing actions, not sending back records). Save the Query (for this
>example I'm assuming it is called myPassthrough).
>
>Next, go to Design View of the Form that has the controls that the user
>will pick the Parameter values from. I'm assuming that the names of the
>three controls are named; ParamControl1,ParamControl2, ParamControl3.
>Create a Command Button (RunSPbutton). This will fire up the Wizard. You
>can pick any process to setup the button, we will replace the code next.
>In the Properties box click on - Events Tab - On Click Event - button
>with three dots. You should now see the VBA code that the wizard setup
>for you for whatever process you picked above. Its handy to leave the
>Error trapping code there. Here is the code you will put there in place
>of the Wizard Code:
>
>Private Sub RunSPbutton_Click()
>On Error goto Err_RunSPbutton_Click
>Dim qd as DAO.Querydef
>Set qd = CurrentDB.Querydefs("myPassthrough")
>qd.SQL = "EXEC mySP '" & Me!ParamControl1 & "', '" & _
> Me!ParamControl2 & "', '" & Me!ParamControl3 & "'"
>qd.Execute
>
>Exit_RunSPbutton_Click:
> Set qd=Nothing
> Exit Sub
>
>Err_RunSPbutton_Click:
> Msgbox Err.Description
> Resume Exit_RunSPbutton_Click
>
>End Sub
>
>Then save it. The user will fill in the three controls and then click
>the button, the SP will run on the server with the parameters.
>
>Lets see how you do with this before getting into the VBA code for
>loading a Combo with Filenames.[/color]

re: Use the input to an Access 2003 form as parameters to a SQL Server stored procedure

fumanchu wrote:[color=blue]
> Thanks! I've managed to get this to work with my bits!
> Tested and all.
> It's the minor things that drive me nuts, like the quoting. Why the
> double quotes around " & Me!ParamControl1 & " ? Can't find that
> anywhere.[/color]
[color=blue][color=green]
>>qd.SQL = "EXEC mySP '" & Me!ParamControl1 & "', '" & _
>> Me!ParamControl2 & "', '" & Me!ParamControl3 & "'"[/color][/color]

SQL Server expects the parameters to be enclosed in single quotes. This
line of code is concatenating text (the stuff in the double quotes) with
the values of the controls to create a string with all of it together.
If your three controls had the values red, green and blue, then the
resulting string would be:

EXEC mySP 'red', 'green', 'blue'

hope that clears it up a bit.

--
Bri