How to call SQL server stored procedure with parameter in ms access 2010 VBA

Question

0.00/5 (No votes)

See more:

, +

I have an MS Access application that contains all tables linked to SQL Server, so in MS Access VBA code I work with those tables very simple, I access them via name, like [Customers].

Also I have a stored procedure in SQL Server called sp_CopyData which I need to call from my VBA code. I want search by name in VBA.
thanks

What I have tried:

SQL

Create PROCEDURE [dbo].[sp_searchh]

 @CustomersName varchar(50)

AS
BEGIN

select Id,age,salary,CustomersName from Table1 where CustomersName =(@CustomersName)

END

Posted 6-Aug-16 10:45am

Adam_adam

Updated 6-Aug-16 22:12pm

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-08-06T22:12:00

You can filter data from linked tables the same way you do that with "normal" tables in MS Access, for example by using Parameters[^]

SQL

PARAMETERS [cusname] CHAR;
SELECT C.Id, C.age, C.salary, C.CustomersName
FROM [Customers] AS C
WHERE C.CustomersName = [cusname];

Conclusion: You don't need to write code to get data from MS SQL Server, because the data are already linked to MS Access database.
For further details, please see:
Everything About Using Parameters from Code[^]
Tips and Techniques for Queries in Access 2007[^]