Solved

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

Posted on 2012-03-14
8
652 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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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.

895 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