cursor type: opening access parameter query using ado command
hi, i have a situation here.
i have created parameterized in my access data base, and i have successfully run the query and get the result return to my recordset object by using ado command object.
the matter is, the recordset cursor type is adOpenFowardOnly which prevent me to move around the recordset, and once the recordset is open/set, the cursor type cannot be change anymore.
the question is, how can i retrieve the recordset form query using ado command that have other cursor type and location?
i have created parameterized in my access data base, and i have successfully run the query and get the result return to my recordset object by using ado command object.
the matter is, the recordset cursor type is adOpenFowardOnly which prevent me to move around the recordset, and once the recordset is open/set, the cursor type cannot be change anymore.
the question is, how can i retrieve the recordset form query using ado command that have other cursor type and location?
ASKER
yes it is true if i'm using the open method... but because i'm using the ado command, the open method is not invoke, it is like this (some portion of code):
'=========================
set prmAge = cmdPerson.CreateParameter(
prmAge.Value = 34
cmdPerson.Parameters.Appen
set rsPerson = cmdPerson.Execute
'=========================
there is no way i can set cursor location during set. if i set it before invoking set command, all the setting will reset. any idea?
'=========================
set prmAge = cmdPerson.CreateParameter(
prmAge.Value = 34
cmdPerson.Parameters.Appen
set rsPerson = cmdPerson.Execute
'=========================
there is no way i can set cursor location during set. if i set it before invoking set command, all the setting will reset. any idea?
khairil,
You have not used the Open method of the recordset object in that code. If you don't see ".Open" anywhere in your code, you haven't used the Open method.
In case you are not aware, "method" is a technical term, meaning a procedure which is a member of an object.
You have used the Execute method of the Command object. You will need to switch to using the Open method of the Recordset object.
Dim rsPerson As Recordset
Set rsPerson = New Recordset
rsPerson.Open "some SQL", cn, adOpenStatic
where cn is your Connection object and substituting something appropriate for "some SQL".
Yes, you will need to know a tiny bit of SQL to get this to work. Maybe I can help you with that, if you provide me a bit more information about your query.
You have not used the Open method of the recordset object in that code. If you don't see ".Open" anywhere in your code, you haven't used the Open method.
In case you are not aware, "method" is a technical term, meaning a procedure which is a member of an object.
You have used the Execute method of the Command object. You will need to switch to using the Open method of the Recordset object.
Dim rsPerson As Recordset
Set rsPerson = New Recordset
rsPerson.Open "some SQL", cn, adOpenStatic
where cn is your Connection object and substituting something appropriate for "some SQL".
Yes, you will need to know a tiny bit of SQL to get this to work. Maybe I can help you with that, if you provide me a bit more information about your query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
khairil,
You have not used the Open method of the recordset object in that code. If you don't see ".Open" anywhere in your code, you haven't used the Open method.
In case you are not aware, "method" is a technical term, meaning a procedure which is a member of an object.
You have used the Execute method of the Command object. You will need to switch to using the Open method of the Recordset object.
Dim rsPerson As Recordset
Set rsPerson = New Recordset
rsPerson.Open "some SQL", cn, adOpenStatic
where cn is your Connection object and substituting something appropriate for "some SQL".
Yes, you will need to know a tiny bit of SQL to get this to work. Maybe I can help you with that, if you provide me a bit more information about your query.
You have not used the Open method of the recordset object in that code. If you don't see ".Open" anywhere in your code, you haven't used the Open method.
In case you are not aware, "method" is a technical term, meaning a procedure which is a member of an object.
You have used the Execute method of the Command object. You will need to switch to using the Open method of the Recordset object.
Dim rsPerson As Recordset
Set rsPerson = New Recordset
rsPerson.Open "some SQL", cn, adOpenStatic
where cn is your Connection object and substituting something appropriate for "some SQL".
Yes, you will need to know a tiny bit of SQL to get this to work. Maybe I can help you with that, if you provide me a bit more information about your query.
That last line should have read:
Set cmdPerson = Nothing
Anthony
Set cmdPerson = Nothing
Anthony
Whoops, sorry about the duplicate send.
Gakk! Of course, acperkins is quite correct. When I said you need to know some SQL to get this to work, I forgot that you can just use Command object as the source. I don't think I have ever used that in production code, so I had forgotten about it, but I did try it out once just as a test.
Gakk! Of course, acperkins is quite correct. When I said you need to know some SQL to get this to work, I forgot that you can just use Command object as the source. I don't think I have ever used that in production code, so I had forgotten about it, but I did try it out once just as a test.
ASKER
hi all,
actually by the time i read the answer i have found the solution for my problem, actually at first i thought it must be a ado connection object at source property during recordset open command. after reading most of the msdn articles, i came across a sample which using a ado command as it source.
so instead of:
rsPerson.Open <my connection>,....
i can use:
rsPerson.Open <my command>
acperkins just give me other way to open the recordset that why i vote for it.
actually by the time i read the answer i have found the solution for my problem, actually at first i thought it must be a ado connection object at source property during recordset open command. after reading most of the msdn articles, i came across a sample which using a ado command as it source.
so instead of:
rsPerson.Open <my connection>,....
i can use:
rsPerson.Open <my command>
acperkins just give me other way to open the recordset that why i vote for it.
Generally, you'd be wanting to use your Connection object as the second argument to the Open method, and either a Command object or a string containing SQL code as the first.
acperkins gave the correct solution, so of course you were correct to accept it.
acperkins gave the correct solution, so of course you were correct to accept it.
ASKER
thank you to you all... :)
If you are using the Execute method of the Connection object or the Command object, I think you will need to switch to using the Open method of the Recordset object instead. These methods simply use the default cursor type, a forward-only cursor.
You are correct when you state that once the recordset is open, you cannot change its cursor type. That is why you must specify the cursor type as you open it, not afterwards.