Using Temp Tables with Access

[jjme88]

I have a complex SQL stored procedure that builds a recordset that i want to bind to an MS Access form, no problem so far.

The problem is that the data is not updateable when bound to the form which is of the conious varety.

Is it possible to get the ADO recordset returned by SP into a temp table, or some other form of recrdset, then bind that to the form so that the data can be updated. I apreaiate that this temp table wont then update the underlying records but I can handle that through a save button or something.

Any ideas anyone.

 

[JoeAtWork]

Maybe use a disconnected ADO recordset and bind it to the form. I'm not sure if you can use the original recordset from your SQL stored procedure and then just disconnect it (i.e. set Connection = Nothing), probably not, but it's worth a try.

Otherwise, you could build your own disconnected recordset by initializing a new recordset and adding each field. Then you could copy the results from the original recordset to the disconnected one. The disconnected one should let you do edits. You would of course have to write your own code to save changes back to the database.

 

[PWise]

What i do is put the form that i want to edit in a sub form

i.e.

the main form is bound to a recordset that is returned from a SP that the data is inserted into a temp table with many joins ect.

the rowsource for the subform is an editable sql statement the link field are the PK

 

[jjme88]

JoeAtWork thanks for your idea.

I have had a play with that and i am able to get a disconnect recordset bound to my form but... in the form I get the message 'The recordset is not updatable' after editing a text box. At least now I can actualy change the text in a text box.

any ideas how I get it to accept the change?
The underlying Store Proc creates a temp table that i then create a primary key on before doing a select statement on the data.. if that helps.

 

[PWise]

if your form is bound to a recordset it is not updateable

let me try to explain my provios post

i have a table called students

this is my Sp

create proc xxxx

as

create table #Students (Studentid int) 
insert into #Students (Studentid)
Select Studentid
from Students
left join othertable
on.....
right join table3
on....
where xxxxx
group by Studentid
having......

Select Studentid from #Studentid

my main form is bound to this recordset

i have a subform for that the recordsourse is

Select * from students

the LinkChildFields="Studentid"
and
the LinkmasterFields="Studentid"

now the sub form is editable bul only has the students ids that are returned by the SP