473,233 Members | 1,984 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 software developers and data experts.

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.
Nov 13 '05 #1
6 2776
On Thu, 29 Sep 2005 23:48:49 GMT, fumanchu <le******@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.
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.


Nov 13 '05 #2
Bri

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

Nov 13 '05 #3
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 <no*@here.com> wrote:

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

Nov 13 '05 #4
Bri
<comments inline>
fumanchu wrote:
I'm not really sure this will work because of the Bulk Insert - seems
to be an ODBC error thing. Thank you anyway
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.
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 agree, messy. And unnecessary.
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.
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.
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?
I don't know of a way that doesn't involve VBA. Again, the code is not
that complex, but there is code involved.
Any more Rube Goldberg ideas, anyone?


I personally try to make things simpler, but I don't always succeed. :{)
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


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

Nov 13 '05 #5
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 <no*@here.com> wrote:
<comments inline>
fumanchu wrote:
I'm not really sure this will work because of the Bulk Insert - seems
to be an ODBC error thing. Thank you anyway


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.
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 agree, messy. And unnecessary.
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.


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


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


I personally try to make things simpler, but I don't always succeed. :{)
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


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.

Nov 13 '05 #6
Bri

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

qd.SQL = "EXEC mySP '" & Me!ParamControl1 & "', '" & _
Me!ParamControl2 & "', '" & Me!ParamControl3 & "'"


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

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Marco Alting | last post by:
Hi, I'm still confused about my queries, I want to do something is ASP that is easily done in Access. I'll post the Access queries below as a reference. The main idea is that the queries depend...
0
by: sedefo | last post by:
I ran into this Microsoft Patterns & Practices Enterprise Library while i was researching how i can write a database independent data access layer. In my company we already use Data Access...
2
by: Josh Strickland | last post by:
I am attempting to create an Access database which uses forms to enter data. The issue I am having is returning the query results from the Stored Procedure back in to the Access Form. ...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
15
by: brettclare | last post by:
I have linked a large SQL Server table to Access, however 'only' 2,195,439 records are shown and are available to query. Can I increase the size (cache??)/number of records showing in Access? ...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
43
by: SLH | last post by:
hi people. im trying to validate input received via a text area on an ASP page before writing it to a database. i cant use client side javascript due to policy, so it all has to happen on the...
23
by: Gloops | last post by:
Hello everybody, Is anyone able to give me some indications about how to develop an Access interface for an Oracle database ? I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0...
6
by: garyb2008 | last post by:
Hello All Ive been creating my first access project in Access 2000 and SQL Server. Its up and running now for 35 users and has been pretty much a success, though ive been stopped in my tracks...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.