Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Stored Procedure

Author

Topic

myhab
Starting Member

13 Posts

Posted - 2003-08-16 : 08:37:29

Can i creat stored procedure for mdb files,how is it possible ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-16 : 08:53:46

You can sort of accomplish the same using parameterized queries in MS Access. For instance, if you had a table with addresses and you wanted to search for a city, put the following in the criteria section of the City column in the design grid:

[Enter City]

When you run the query you'll be prompted to provide a city name.

From ADO, you can create a Command object and create a parameter that matches the name you put as the query parameter. In that case, you'd be better off using a parameter name with no spaces in it:

[FindCity]

And your ADO code would look like:

Set cn=CreateObject("ADODB.Connection")
Set cm=CreateObject("ADODB.Command")
Set rs=CreateObject("ADODB.Recordset")

city="New York"     'you can substitute user input here

'set name of query/procedure
cm.CommandText="MyQueryName"    

'set command type (you can also use adCmdTypeStoredProc if you use the ADO include file)
cm.CommandType=4    

'add parameter
cm.Parameters.Append cm.CreateParameter("FindCity", adVarChar, adParamInput, 50, city)

'open connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\accessdb.mdb;"

'set command object to use this connection
cm.ActiveConnection=cn     

set rs=cm.Execute()     'fill recordset with results from procedure

.... do whatever you need with recordset

rs.Close          'close recordset
cn.Close          'close connection
set cm=Nothing    'destroy objects when done
set rs=Nothing
set cn=Nothing

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-16 : 09:55:34

(of course it depends much on what someone names Stored Procedure)

No, it's impossible. In MS Access we can create only 6 types of objects:
1) table
2) query (a single SQL (DML or DQL command) statement)
3) form
4) report
5) macro
6) VBA module

myhab
Starting Member

13 Posts

Posted - 2003-08-18 : 10:51:24

You indicate it's possible to use Query instead of StoredProcedure in mdb file so How is it possible to Convert this storesd procedure to Query in mdb

ALTER Storedprocedure MySite_Login
(

@Email nvarchar(15),
@Password nvarchar(15),
@CustomerID int OUTPUT
)
AS

SELECT
@CustomerID = CustomerID

FROM
Customers

WHERE
Email = @Email
AND
Password = @Password

IF @@Rowcount < 1
SELECT
@CustomerID = 0

myhab
Starting Member

13 Posts

Posted - 2003-08-18 : 14:41:58

Hi Guys pretty hard Ok now I'll explain ya ,,

I've got a table Like this

----------------------------------------
ID |Password | Name |Email
----------------------------------------
1 | 1000 |Edward|Edward@yahoo.com
AutoNumber)

Here we go :
This Query Works Fine :
PARAMETERS [@Emai] Text ( 255 ), [@Password] Text ( 255 );
SELECT Customer.Email, Customer.Name, ID
FROM Customer
WHERE Email=[@Email] And Password=[@Password];

---------------------
it asks Email and password but I need one more parameter like @ID that return the @ID=id if Email and passowrd does not Exist then @ID=Null and return it ?

I hope it helps to help ME !!!!!!!!!!

Thanks Alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources