Getting ADO recordset from a SQL stored procedure 1

[camelman]

I am using an ADODB.recordset to display data on a form (microsoft access 2000). This recordset needs to get its data from an SQL server 2000 stored procedure.
I do this in the on_open event of the form so that the data is displayed when you open the form.
I must be able to write to the recordset (using code on the form).

I have used the following code. My connection seems to work but the recordset doesn't seem to open. I get a "recordset not open" error on the line that says "While not .eof"

Private Sub Form_Open(Cancel As Integer)

'CREATE CONNECTION
Dim ado_conn As ADODB.Connection
Dim strServer, strDbase As String

Set ado_conn = New ADODB.Connection
strServer = "myserver"
strDbase = "myDB"

ado_conn.ConnectionTimeout = 15
ado_conn.Provider = "SQLOLEDB"
ado_conn.Properties("Data Source&quot.Value = strServer
ado_conn.Properties("Initial Catalog&quot.Value = strDbase
ado_conn.Properties("Integrated Security&quot.Value = "SSPI"
ado_conn.Properties("User ID&quot.Value = "UserName"
ado_conn.Properties("PassWord&quot.Value = "Password"
ado_conn.Open

'CREATE RECORDSET
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Source = "sp_jonsproc " _
& [Forms]![client menu]![client details]![ID No]

Set rst.ActiveConnection = ado_conn
rst.Open

With rst
While Not (rst.EOF)
'!Balance = !balance * 10
.MoveNext
Wend

'Display the recordset on the form.
Set Me.Recordset = rst

End With
End Sub

 

[tlbroadbent]

You can not update a record set created by calling a stored procedure. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[camelman]

Thanks for the info. At least I won't spend hours trying to do something that isn't possible.
Short of writing the entire recordset to an MS-Access temp table and reading it back into an updatable recordset is their any way for me to do what I am trying to do ?

My end goal is to get data from a stored procedure (passing the clients ID_No as a parameter to get the right info)
I then need to mess around with this data and then place it on form.

Any suggestions are welcome as this has kept me awake for 2 nights running.

If Terry reads this I must say that I admire your willingness to help young developers. I have just discovered tek-tips and am doing my best to help whoever I can during my work day since I know how much time experienced developers have saved me.

 

[tlbroadbent]

Thanks for your kind expression of appreciation. I rather enjoy helping where I can. Would you mind posting the SQL Stored Proc so I could make some recommendations on your process?

Thanks, Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[camelman]

Its' quite a meanie. I use this procedure to get a "bank statement" type report.
I couldn't figure out how to calculate a running balance so my plan was to pass a blank balance field to microsoft access where I would open the stored procedure as a recordset. I would then run through the recordset in date order - one transaction at a time - and writing the running balance to each record. I would then display this recordset on my MS-Access 2000 form.

CREATE PROCEDURE [sp_jonsproc]
@client_ID_No nvarchar(30)
AS
BEGIN
--credits
select [client Id No], [date], 'REC' + [Receipt no] as [reference no], reference,' ' as [creditor name], 0 as debits, amount as credits into #temptable from credits where [client Id No] = @client_ID_No union all
select [client Id No], [date], 'SET' + cast([Settlement No] as nvarchar(20)) as [reference no], 'settlement' as reference, ' ' as [creditor name] , 0 as debits, [monies received] as credits from settlements where [client Id No] = @client_ID_No union all
select [client Id No], [date], 'REF' + cast([Refund no] as nvarchar(20)) as [reference no], reference, isnull([name],' ') as [creditor name], 0 as debits, amount as credits from refund left outer join [creditors details] on refund.[creditor no] = [creditors details].[creditor no] where [client Id No] = @client_ID_No union all
--debits
select [client Id No], [date], 'CRF' + [credits].[Receipt no] as [reference no], reference, ' ' as [creditor name], [Monies Rec Fees] as debits, 0 as credits from [credit fees], [credits] where ([credit fees].[Receipt no] = [credits].[Receipt no]) and ([client Id No] = @client_ID_No) union all
select [client Id No], [date], 'CMC' + cast([Charge no] as nvarchar(20)) as [reference no], reference,' ' as [creditor name], Charge as debits, 0 as credits from [communication charges] where [client Id No] = @client_ID_No union all
select [client Id No], [date], 'FSET' + cast([Settlement No] as nvarchar(20)) as [reference no], 'settlement Fee' as reference, ' ' as [creditor name], ([Monies Received] - [Monies Offered on settlement]) as debits, 0 as credits from settlements where [client Id No] = @client_ID_No union all
select [ID No] as [client ID No],[Admin Order Date] AS [date], 'AOF' + [ID No] as [Reference no], 'Admin order fee' AS reference,' ' as [Creditor Name], [Admin Order Fee] AS debits, 0 as credits from [Client Details] where [Id No] = @client_ID_No

select *, 0 as balance from #temptable order by date
END
GO

This is my first Access / SQL program so I am hitting my head against the wall with quite a few things.

 

[tlbroadbent]

I've added a few things. You'll need to adjust for your needs. The changes are highlighted, except for format and allignment changes made to aid readability. Hopefully, I didn't make too many errors! ;-)

Please let me know if you have any questions or find something doesn't work.

------------------------------

CREATE PROCEDURE [sp_jonsproc]
@Client_ID_No nvarchar(30)
AS

SET NOCOUNT ON

/* Declare variable to be used to calcualte running balance */
Declare @bal decimal(18,2)

--credits
Select [Client Id No],
[Date],
'REC' + [Receipt no] As [reference no],
reference,
' ' As [creditor name],
/* make sure column data type is consistent */
cast(0 As decimal(18,2)) As debits,
cast(amount As decimal(18,2)) As credits,
/* add balance column while creating table */
cast(0 As decimal(18,2)) As Balance
into #temptable From credits
Where [Client Id No] = @Client_ID_No

Union All

Select [Client Id No],
[date],
'SET' + cast([Settlement No] As nvarchar(20)) As [reference no],
'settlement' As reference,
' ' As [creditor name],
0 As debits,
[monies received] As credits,
0 As Balance
From settlements
Where [Client Id No] = @Client_ID_No

Union All

Select [Client Id No],
[date],
'REF' + cast([Refund no] As nvarchar(20)) As [reference no],
reference, isnull([name],' ') As [creditor name],
0 As debits,
amount As credits,
0 As Balance
From refund left outer join [creditors details]
on refund.[creditor no] = [creditors details].[creditor no]
Where [Client Id No] = @Client_ID_No

Union All

--debits
Select [Client Id No],
[date],
'CRF' + [credits].[Receipt no] As [reference no],
reference, ' ' As [creditor name],
[Monies Rec Fees] As debits,
0 As credits,
0 As Balance
From [credit fees] Inner Join [credits]
On [credit fees].[Receipt no] = [credits].[Receipt no]
Where ([Client Id No] = @Client_ID_No)

Union All

Select [Client Id No],
[date],
'CMC' + cast([Charge no] As nvarchar(20)) As [reference no],
reference,' ' As [creditor name],
Charge As debits,
0 As credits,
0 As Balance
From [communication charges]
Where [Client Id No] = @Client_ID_No

Union All

Select [Client Id No],
[date],
'FSET' + cast([Settlement No] As nvarchar(20)) As [reference no],
'settlement Fee' As reference, ' ' As [creditor name],
([Monies Received] - [Monies Offered on settlement]) As debits,
0 As credits,
0 As Balance
From settlements
Where [Client Id No] = @Client_ID_No

Union All

Select [ID No] As [Client ID No],
[Admin Order Date] AS [date],
'AOF' + [ID No] As [Reference no],
'Admin order fee' AS reference,
' ' As [Creditor Name],
[Admin Order Fee] AS debits,
0 As credits,
0 As Balance
From [Client Details]
Where [Id No] = @Client_ID_No

Order By [Date]

/* set beginning balance - if you have a beginning balance stored this is where to select it */
Select @bal = BegnningBalance From SomeTable Where [Id No] = @Client_ID_No

/* Create the running balance. Make sure the formula is correct. Yes, the syntax is correct (see faq183-207) */
Update #temptable Set @bal = balance = @bal + [credits] - [debits]

/* return the non-updatable record set to the application */
Select * From #temptable Order By [Date]

/* Drop the temporary table */
Drop Table #temptable

GO Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[camelman]

Worked like a charm.

Thanks