Link to home
Start Free TrialLog in
Avatar of HometownComp
HometownComp

asked on

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!
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HometownComp
HometownComp

ASKER

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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
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.
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.