?
Solved

cursor type: opening access parameter query using ado command

Posted on 2002-04-10
10
Medium Priority
?
247 Views
Last Modified: 2006-11-17
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?
0
Comment
Question by:khairil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 2

Expert Comment

by:TravisHall
ID: 6932859
If you are using the Open method of your Recordset object to run the query, just specify a different CursorType (the third argument) when you call Open.

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.
0
 
LVL 13

Author Comment

by:khairil
ID: 6932892
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(<My Parameter Name>, <type> ....)

prmAge.Value = 34

cmdPerson.Parameters.Append prmAge

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?
0
 
LVL 2

Expert Comment

by:TravisHall
ID: 6932901
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 6932970
As Travis has mentioned, you need to use the Recordset Open method.

What you are overlooking is that the Source for the Recordset Open method can be a Command object, so in your case, first set up your Command object as you have done already:

set prmAge = cmdPerson.CreateParameter(<My Parameter Name>, <type> ....)

prmAge.Value = 34

cmdPerson.Parameters.Append prmAge

Then comment the next line as not needed
'set rsPerson = cmdPerson.Execute

Set rsPerson = New ADODB.Recordset
With rsPerson
   .Source = cmdPerson
   ' If you are using a connection object (cn) than comment the next line
   .ActiveConnection = "your connection goes here"
   'Or if you are using a Connection object (cn) then uncomment the next line.
   'Set .ActiveConnection = cn
   .CursorType = adOpenDynamic 'or whatever
   .LockType = adLockOptimistic
   .Open Options:=adCmdStoredProc
End With

...

rs.Close

Set rs = Nothing
Set cmd = Nothing
0
 
LVL 2

Expert Comment

by:TravisHall
ID: 6932971
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6932972
That last line should have read:
Set cmdPerson = Nothing

Anthony
0
 
LVL 2

Expert Comment

by:TravisHall
ID: 6932974
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.
0
 
LVL 13

Author Comment

by:khairil
ID: 6934235
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.
0
 
LVL 2

Expert Comment

by:TravisHall
ID: 6935416
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.
0
 
LVL 13

Author Comment

by:khairil
ID: 6935698
thank you to you all... :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month8 days, 1 hour left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question