473,741 Members | 5,161 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2803
On Thu, 29 Sep 2005 23:48:49 GMT, fumanchu <le******@veriz on.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.Query defs("myPassthr ough")
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.Query defs("myPassthr ough")
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.Query defs("myPassthr ough")
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,P aramControl2, 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_Cli ck()
On Error goto Err_RunSPbutton _Click
Dim qd as DAO.Querydef
Set qd = CurrentDB.Query defs("myPassthr ough")
qd.SQL = "EXEC mySP '" & Me!ParamControl 1 & "', '" & _
Me!ParamControl 2 & "', '" & Me!ParamControl 3 & "'"
qd.Execute

Exit_RunSPbutto n_Click:
Set qd=Nothing
Exit Sub

Err_RunSPbutton _Click:
Msgbox Err.Description
Resume Exit_RunSPbutto n_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!ParamControl 1 & " ? 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.Query defs("myPassthr ough")
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,P aramControl2, 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_Cli ck()
On Error goto Err_RunSPbutton _Click
Dim qd as DAO.Querydef
Set qd = CurrentDB.Query defs("myPassthr ough")
qd.SQL = "EXEC mySP '" & Me!ParamControl 1 & "', '" & _
Me!ParamControl 2 & "', '" & Me!ParamControl 3 & "'"
qd.Execute

Exit_RunSPbutt on_Click:
Set qd=Nothing
Exit Sub

Err_RunSPbutto n_Click:
Msgbox Err.Description
Resume Exit_RunSPbutto n_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!ParamControl 1 & " ? Can't find that
anywhere.

qd.SQL = "EXEC mySP '" & Me!ParamControl 1 & "', '" & _
Me!ParamControl 2 & "', '" & Me!ParamControl 3 & "'"


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
2348
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 on each other in a hierarchical manner. It all works fine in Access, but in the last query (Qrylevel3CostTotals) there's a criteria which I would like to set from an HTML form and thats where I am confused! How do I make this work in an ASP...
0
5409
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 Application Block (DAAB) in our .Net projects. We use SqlHelper in SQL based projects, and OracleHelper in Oracle based ones. OracleHelper was not published officially by Microsoft as part of the DAAB but it was given as a helper code in a sample .Net...
2
11708
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. tCetecM1CUST (SQL Table that contains the Customer Information) tAccountingDetail (SQL Table that contains the information in the form) frmAccountingEntry (Access form used to enter data) spGetCustomerInformation (Stored Procedure which returns data using...
6
4751
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 appreciated. Thanks in advance
15
7258
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? Thank you.
15
4637
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 communicate with a MySQL database table on a web server, from inside of my company's Access-VBA application. I know VBA pretty well but have never before needed to do this HTTP/XML/MySQL type functions.
43
2613
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 server. here is what i was trying, but pieces of it continue to break for one reason or another. the thinking behind this function was like this: if the input is less than 10 characters long, fail. if its 10 characters or greater, but it doesnt...
23
3424
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 and ODBC 3.525.1117.0, on Windows XP Pro 5.1.2600 SP2 Nu 2600. I failed executing an Oracle stored procedure from Access, and a trigger to store data to a temporary table was active from SQL*Plus, but
6
4506
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 quite often to figure out how to get these applications to talk with eachother. Todays problem: I want to pass time figures to a stored procedure using a forms Input Paramaters property, namely @StartTime and @EndTime (both are defined as datetime...
0
9484
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9342
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9211
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6756
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4572
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3286
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2195
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.