|
 |
access thread: Re: Calling an SQL stored procedure
Message #1 by "Mavin Specimen" <mspecimen@h...> on Tue, 10 Jul 2001 15:00:17
|
|
Can I call an SQL 7 stored procedure from an Access mdb. NOT an adp. If so
how would one go about this?
Thoughts/comments.
Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Tue, 10 Jul 2001 08:40:25 -0700
|
|
You sure can--use ADO's Command object. Here's some sample code that calls
an ORACLE sp.
===================================================
Dim cmd as ADODB.Command
Dim con as ADODB.Connection
Dim Network as IWshRuntimeLibrary.IWshNetwork
Dim prm as ADODB.Parameter
Set cmd = New ADODB.Command
Set con = New ADODB.Connection
Set Network = New IWshRuntimeLibrary.IWshNetwork_Class
With con
.ConnectionString = "Provider=MSDAORA.1;" _
& "Password=tiger;" _
& "User ID=scott;" _
& "Data Source=devlpac;" _
& "Persist Security Info=True"
.Open
End With
With cmd
Set .ActiveConnection = con
.CommandType = adCmdStoredProc
.CommandText = "SCOTT.LOGLOGIN"
Set prm = .CreateParameter("M_NAME" _
, adVarChar _
, adParamInput _
, 4000)
.Parameters.Append prm
Set prm = .CreateParameter("U_NAME" _
, adVarChar _
, adParamInput _
, 4000)
.Parameters.Append prm
.Parameters("M_NAME").Value = Network.ComputerName
.Parameters("U_NAME").Value = Network.UserName
.Execute , , adAsyncExecute
End With
Set cmd = Nothing
con.Close
Set con = Nothing
===================================================
Cheers,
-Roy
-----Original Message-----
From: Mavin Specimen [mailto:mspecimen@h...]
Sent: Tuesday, July 10, 2001 8:00 AM
To: Access
Subject: [access] Re: Calling an SQL stored procedure
Can I call an SQL 7 stored procedure from an Access mdb. NOT an adp. If so
how would one go about this?
Thoughts/comments.
Message #3 by "Rogers, Robert" <rrogers@b...> on Tue, 10 Jul 2001 12:18:43 -0400
|
|
Of course
In general, you'll use the ADO Command object and its Execute method to send
commands down the wire to the SQL Server. Example: Code to execute a
stored procedure name sp_routine might look similar to the following:
cmdLocal.commandtext = "Exec sp_routine @name""" & strNameParam & """"
Set rstLocal = cmdLocal.Execute()
-----Original Message-----
From: Mavin Specimen [mailto:mspecimen@h...]
Sent: Tuesday, July 10, 2001 11:00 AM
To: Access
Subject: [access] Re: Calling an SQL stored procedure
Can I call an SQL 7 stored procedure from an Access mdb. NOT an adp. If so
how would one go about this?
Thoughts/comments.
|
|
|
View Cart
