Solved

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

Posted on 2012-03-14
8
657 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
ID: 37719266
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
ID: 37719476
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
ID: 37719506
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 15

Expert Comment

by:Simon Ball
ID: 37719538
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
 

Author Comment

by:HometownComp
ID: 37719955
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
ID: 37724274
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
ID: 37725462
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
ID: 37758288
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

830 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