Passing Value from Stored Proc to access DB

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

SQL Server 2000 Forums

Transact-SQL (2000)

Passing Value from Stored Proc to access DB

Author

Topic

rnoJoe
Starting Member

4 Posts

Posted - 2007-03-12 : 18:17:49

Hi,

I have a stored procedure that move data from one table to another. I would like the stored procedure to pass a value back to the access database application that called it so the user can know that the data was moved successfully. Any help would be greatly appreciated

code in Access DB:

StrSPEmps = "spEmpUpdate"
Set Db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;dsn=DivisionSQLDb;uid=empdata;pwd=empdata")

Db.Execute StrSPEmps, dbSQLPassThrough
Set Rs = Db.OpenRecordset(StrSPEmps, dbOpenSnapshot, dbSQLPassThrough)
StrFlag = Rs!Flag

Stored Procedure Code

CREATE PROCEDURE spEmpUpdate (@Flag char(2))
AS

If EXISTS (Select * from TableA,TableB where (TabelA.ID = TableB.ID)

Set @Flag= 'T'
Else
Insert INTO TableA Select * from TableB
set @Flag = 'F'
GO

I would like the T or F to be sent back to the acces DB so the user knows if the record was already in TableA or if the records was moved successfully

Thank you

Joe

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-12 : 22:58:57

Use ADO record set to run stored procedure from access application.

mk_garg

rnoJoe
Starting Member

4 Posts

Posted - 2007-03-13 : 11:48:46

using ADO....thats what I thought I was doing. Can you take a look at the code and let me know what I am doing wrong?

thank you for your help

JohnH
Starting Member

13 Posts

Posted - 2007-03-13 : 15:35:39

I'd change the proc just a little. Do use joins and aliases. Uncomment only one of the two commented SELECT statements.

ALTER PROC spEmpUpdate
AS

INSERT TableA
SELECT *
FROM TableB
WHERE NOT EXISTS (SELECT 1 FROM TableA A JOIN TableB B ON A.ID = B.ID)

-- This gives a little more than just 'T' or 'F'
--SELECT @@ROWCOUNT AS 'Rows_Inserted'

-- Or this gives just the minimum
--SELECT CASE WHEN @@ROWCOUNT = 0 THEN 'T' ELSE 'F' END AS 'Results'

Then in your Access DB code, get the 1st row of the recordset and check the value of the 1st column. I've tested the proc, but not the Access bit.

John Hopkins

rnoJoe
Starting Member

4 Posts

Posted - 2007-03-14 : 13:58:48

can anyone help with the VBA code? I cant figure out how to get that result back to my access application.

thanks you John H for your help

michaelxvo
Starting Member

47 Posts

Posted - 2007-03-14 : 16:04:30

quote:
Originally posted by rnoJoe

Hi,

I have a stored procedure that move data from one table to another. I would like the stored procedure to pass a value back to the access database application that called it so the user can know that the data was moved successfully. Any help would be greatly appreciated

code in Access DB:

StrSPEmps = "spEmpUpdate"
Set Db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;dsn=DivisionSQLDb;uid=empdata;pwd=empdata")

Db.Execute StrSPEmps, dbSQLPassThrough
Set Rs = Db.OpenRecordset(StrSPEmps, dbOpenSnapshot, dbSQLPassThrough)
StrFlag = Rs!Flag

Stored Procedure Code

CREATE PROCEDURE spEmpUpdate (@Flag char(2))
AS

If EXISTS (Select * from TableA,TableB where (TabelA.ID = TableB.ID)

Set @Flag= 'T'
Else
Insert INTO TableA Select * from TableB
set @Flag = 'F'
GO

I would like the T or F to be sent back to the acces DB so the user knows if the record was already in TableA or if the records was moved successfully

Thank you

Joe

FROM SQL SERVER

CREATE PROCEDURE spEmpUpdate (@Flag char(2) OUTPUT)
AS

If EXISTS (Select * from TableA,TableB where (TabelA.ID = TableB.ID)

Set @Flag= 'T'
Else
Insert INTO TableA Select * from TableB
set @Flag = 'F'

GO

FROM VBA CODE
dim cn as new adodb.connection
dim cmd as new adodb.command
dim strFlag as string
cn.connectionstring = "Data Source=machinename;initial catalog=databasename;Trusted_connection=Yes;"
cn.open
cmd.ActiveConnection = cn
cmd.CommandText ="spEmpUpdate"
cmd.CommandType=adcmdStoredProc
cmd.parameters.append cmd.createparameter("@Flag",AdChar,AdParamOutput,2)
cmd.execute
strFlag = cmd(0)
msgbox strFlag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources