Passing Input Parameter

  • Thread starter Thread starter Andro Dgebuadze
  • Start date Start date
A

Andro Dgebuadze

Hi

I am new to ADP and have trivial question:
How to pass a parameter to a stored procedure?

Here is my SP:

===================================
CREATE PROCEDURE dbo.[COA to Table1]
@RowID INT
AS INSERT INTO dbo.Table1
(Seg2)
SELECT Seg2
FROM dbo.COA
WHERE (ID = @RowID)
GO
====================================
If I run this this procedure it asks me RowID parameter and when I submit a
number it appents 1 record to Table 1.

But how should I do it by Code? I tried DoCmd.OpenStoredProcedure but it has
no option to pass paramater.
I have tried through ADODB.Command but Execute command was failing.

Can you give me a VBA Script to run this procedure with parameter RowID=77
(for example?)

Thanks

Andro
 
Sorry, but I need it with Code, not through the form.
I think I need cmd.parameter or something like that
Please Help...

Andro



Kevin3NF said:
Take a look at the Input parameters option on the form properties, data tab.

Aircode for that field:

@RowID = text1.value

or something like that...

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

Andro Dgebuadze said:
Hi

I am new to ADP and have trivial question:
How to pass a parameter to a stored procedure?

Here is my SP:

===================================
CREATE PROCEDURE dbo.[COA to Table1]
@RowID INT
AS INSERT INTO dbo.Table1
(Seg2)
SELECT Seg2
FROM dbo.COA
WHERE (ID = @RowID)
GO
====================================
If I run this this procedure it asks me RowID parameter and when I
Click to expand...
submit
a
number it appents 1 record to Table 1.

But how should I do it by Code? I tried DoCmd.OpenStoredProcedure but it has
no option to pass paramater.
I have tried through ADODB.Command but Execute command was failing.

Can you give me a VBA Script to run this procedure with parameter RowID=77
(for example?)

Thanks

Andro
Click to expand...
Click to expand...
 
Andro,

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

cn.Open strConnectionString
With cmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "COA to Table1"
.Parameters.Refresh
.Parameters("@RowID") = 77
.Execute
End With

cn.Close
Set cmd = Nothing
Set cn = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Many thanks Graham



Graham R Seach said:
Andro,

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

cn.Open strConnectionString
With cmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "COA to Table1"
.Parameters.Refresh
.Parameters("@RowID") = 77
.Execute
End With

cn.Close
Set cmd = Nothing
Set cn = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Andro Dgebuadze said:
Hi

I am new to ADP and have trivial question:
How to pass a parameter to a stored procedure?

Here is my SP:

===================================
CREATE PROCEDURE dbo.[COA to Table1]
@RowID INT
AS INSERT INTO dbo.Table1
(Seg2)
SELECT Seg2
FROM dbo.COA
WHERE (ID = @RowID)
GO
====================================
If I run this this procedure it asks me RowID parameter and when I
Click to expand...
submit
a
number it appents 1 record to Table 1.

But how should I do it by Code? I tried DoCmd.OpenStoredProcedure but it has
no option to pass paramater.
I have tried through ADODB.Command but Execute command was failing.

Can you give me a VBA Script to run this procedure with parameter RowID=77
(for example?)

Thanks

Andro
Click to expand...
Click to expand...
 
Back
Top