Hi,
App:Access 2003, SQL Server 2000
I cant seem to figure out what im doing wrong.
Calling a stored procedure on MS SQL 2000 from Access I know its working cus part of the stored procedure adds + 1 to a record.
Here is my stored proc : -
CREATE PROCEDURE dbo.prc_get_unique_load
AS
DECLARE @gKey int
DECLARE @rKey int
DECLARE @OutKey int
/*Read in current unique ID*/
SELECT @gKey = (SELECT load_key FROM tbl_cfg)
/* Set it to the next value straight away*/
SET @rKey = @gKey + 1
/*Save it back to the database before we do anything else*/
UPDATE tbl_cfg SET LOAD_KEY = @rKey
SET @OutKey = @gKey
SELECT @OUTKEY AS UniqueKey
GO
As you can see it gets the value from Load_key, adds 1 to it. It then displays the vlaue it first recevied from load_key. I wish to get this value and return it to my access app!
For some reason I cant get it to work, I keep getting ERROR:3704 Operation is not allowed when the object is closed.
I can understand this error as in its saying ive nto got a recordset there so how can I do a "if not rs.eof"
What am i missing to retrieve a record back to my access app?? Below is the code I have used to call the stored proc..I have tried many different lumps of code, all run the stored proc but do not return data!
I guess I dont understand how the two interact properly!
Dim cnn
Dim cmd
Dim sql
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.Open "Provider=sqloledb;Data Source=GMSQL1;Initial Catalog=GLOAD;Integrated Security=SSPI;"
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "prc_get_unique_load"
End With
rs.Open cmd, , adOpenStatic, , adCmdStoredProc
I have also tried : -
Dim myADO As New ADODB.Connection
Dim sSQL As String
Dim rs As New ADODB.Recordset
myADO.Open "Provider=sqloledb;Data Source=GMSQL1;Initial Catalog=GLOAD;Integrated Security=SSPI;"
sSQL = "exec prc_get_unique_load"
Set rs = myADO.Execute(sSQL)
My lack of understanding is causing me some confusion im sure and thats where I am going wrong so if somone could point my mistakes out it would be greatfully appreciated!
regards
Antony
Start Free Trial
