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

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!
HometownCompAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HometownCompAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Simon BallChief information OfficerCommented:
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
HometownCompAuthor Commented:
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
Simon BallChief information OfficerCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
HometownCompAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.