R
Rod Plastow
Hi.
I have complex SQL containing parameters set up as an ADODB.Command. This
is coded in a class module and the command is instantiated when a class
object is instantiated. A method of the class returns the ADODB Recordset by
executing the command. So far so good and I have been able to demonstrate
this all works correctly and indeed the method returns the desired result set.
Now I wish to use this result set as the recordset for a form. All controls
on the form are set up to match and be compatible with the recordset. This
is where the trouble starts.
When setting the recordset property of the form I receive an error 7965: Not
a valid Recordset property. Searches of the Knowledgebase suggest this
occurs when there is no ActiveConnection associated with the recordset and
that the solution is to set this property. But interrogation of the
recordset reveals that the ActiveConnection is set and any attempt to refresh
it results in an error 3705: Operation not allowed while object is open.
I then tried moving the SQL locally in the code to where I open the form,
hard coding in parameter values for the moment. I used the Recordset.Open
method to obtain the result set and initially I still had the 7695 error.
After some trials and reading I included the statement rst.CursorLocation =
adUseClient before the Recordset.Open and all worked perfectly as I had
originally planned. Moreover with the recordset being obtained through
Recordset.Open I can change the .ActiveConnection property to my heart’s
content.
So what is it about a recordset produced by an ADODB.Command that is
different? Yes the recordset has an .ActiveConnection. There is a property
named ‘Client Cursor’ that is True and I assume this is equivalent to
adUseClient.
Can anyone shed any light on this?
Access version is 2003 SP2. Database being accessed is Jet. ADO library is
v2.1
Regards,
Rod
I have complex SQL containing parameters set up as an ADODB.Command. This
is coded in a class module and the command is instantiated when a class
object is instantiated. A method of the class returns the ADODB Recordset by
executing the command. So far so good and I have been able to demonstrate
this all works correctly and indeed the method returns the desired result set.
Now I wish to use this result set as the recordset for a form. All controls
on the form are set up to match and be compatible with the recordset. This
is where the trouble starts.
When setting the recordset property of the form I receive an error 7965: Not
a valid Recordset property. Searches of the Knowledgebase suggest this
occurs when there is no ActiveConnection associated with the recordset and
that the solution is to set this property. But interrogation of the
recordset reveals that the ActiveConnection is set and any attempt to refresh
it results in an error 3705: Operation not allowed while object is open.
I then tried moving the SQL locally in the code to where I open the form,
hard coding in parameter values for the moment. I used the Recordset.Open
method to obtain the result set and initially I still had the 7695 error.
After some trials and reading I included the statement rst.CursorLocation =
adUseClient before the Recordset.Open and all worked perfectly as I had
originally planned. Moreover with the recordset being obtained through
Recordset.Open I can change the .ActiveConnection property to my heart’s
content.
So what is it about a recordset produced by an ADODB.Command that is
different? Yes the recordset has an .ActiveConnection. There is a property
named ‘Client Cursor’ that is True and I assume this is equivalent to
adUseClient.
Can anyone shed any light on this?
Access version is 2003 SP2. Database being accessed is Jet. ADO library is
v2.1
Regards,
Rod