Solved

Sql stored procedure pass parameter from MS Access form code, visual basic

Posted on 2012-03-14
8
649 Views
Last Modified: 2012-03-23
Hi.  I need to pass a rowid column (multiple rows) to a sql stored procedure from MSAccess form code.  I've only ever excuted sql stored procedures that did not need a parameter.  I'm sure this is much simpler than it looks to me in many code samples i've looked at.  I just don't have the time to learn more about this myself and I also need this to be efficient.

I have a MS Access database (running mdb in msaccess 2007, 2000 compatibility mode).  I have a table I'll call MyMSAtbl with one column named RowID.  This table may have a few thousand records at once.

I have a  Sql Server table (2008 r2) and stored procedure that deletes the records in the sql table for the RowID #'s in my ms access table.

Everything is in place except I do not know how to set and pass my table recid's to the  stored procedure.

Here is the Vendors Sql Stored Procedure:
CREATE PROCEDURE spExp_Delete
@ExpDataRowID bigint
AS
DELETE FROM MySqlTbl
WHERE ExpDataRowID = @ExpDataRowID

Here is my code to execute the stored proc.:
Private Sub ClearExp_Click()
Dim iReturnVal As Integer

   cmd.ActiveConnection = "DRIVER={SQL Server};SERVER=myserver;DATABASE=mydb;UID=myuser;PWD=mypass"
   
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dbo.spExp_delete"
    cmd.CommandTimeout = 0
    cmd.Execute

On Err GoTo ExitHere
If cmd.ActiveConnection.State = adStateOpen Then
    cmd.ActiveConnection.Close
End If

ExitHere:

End Sub

So all I need to do now is select my RowID as @ExpDataRowID but i'm not sure how to set this and pass it as a parameter.

Help!  Thanks!
0
Comment
Question by:HometownComp
  • 3
  • 3
  • 2
8 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
The simplest way to call a Stored Proc that doesn't need any further interaction is like this:

YourConnection.Execute "EXEC spExp_delete @YourParamName=" & YourValue

If YourParamName is a Text or Date field:

YourConnection.Execute "EXEC spExp_delete @YourParamName='" & YourValue & "'"
0
 

Author Comment

by:HometownComp
Comment Utility
if i set my value (YourValue) to "Select [RowID] from [myMSAtbl]" etc., would that pass all the RowID's?  or do I have to Loop through the records and run the execute command for each row?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
No, it won't pass them all. If you need to delete ALL of the values, you'll have to either (a) pass each of those values individually or (b) alter your Stored Procedure to do this automatically, if possible. Does the SQL Server have the same "data" as your Access form? That is, how does your form determine which records should be deleted? If that form uses data based in the server, then you could alter the Stored Proc to use the same algorithm that your form does. If not, and if you're deleting records on the Server based on user choices (from data based in Access), then you'll just have to call this over and over.

Or you could alter the SP to accept a string of values, and then parse those values in the SP. Might be easier if you did that on the client side, however.
0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
when i had to do something similar, i created a copy of the local table on the sql server - used odbc to link it, and then had access write records into the odbc table.... and amended the stored procedure to do the delete on the join to the odbc table.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:HometownComp
Comment Utility
The sql server db i'm working with is another vendor's app.  They want our app to delete only specific (processed) rows in case more records end up in their table.  We link to their table but because MSaccess 2007 doesn't recognize their bigint key field, we make our own table, convert bigint to int and can see and work with all the data.  At the end of our processing we need to clear their tables data.  I'm wondering now.. by executing the stored procedure for every rowid, how that might effect resources.  We don't want this to take more than a few minutes the most.  I can try it and see how long it takes but does anyone know how this will affect resources.  We have our own sql db back end for our msaccess app.
0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
is this something you could use row id between in the stored proc..

e.g. are you only processing new records, and you know the min and max records?

id
34678
..
..
..
45679

exec mystoredproc 34678, 45679

mystored proc:
delete from mytabletodelte where id >= 34678 and id <= 45679
0
 
LVL 84
Comment Utility
How many records do you typically delete? Anytime you call recursive/loop code, you run the risk of performance issues.

If you cannot use the block code suggested by Sudonim, then perhaps you could send a comma-delimited list of value to the SP, and alter the SP to delete with the IN keyword:

DELETE FROM YourTable WHERE YourIDField IN (@YourParam)

So your client-side would simply loop through an buildup a string, which would be passed as a String/Text value to the SP.
0
 

Author Closing Comment

by:HometownComp
Comment Utility
Thank you, all for your comments, especially LMS Consulting.  It was the sql syntax that was throwing me off.  All I had to do was add a recordset to select my rowid and add & myvalue etc.  ... something we do all the time.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ms/access report does not open query 20 31
Access Date Query 28 28
Dateadd 3 20
Access Migration to Sql Server 2 7
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now