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


fumanchu wrote: 
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.

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

.