INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Recordset and command object

Recordset and command object

(OP)
What kind of recordset is created when set to a command.execute statement?

CODE

Set cmdGSTSalesAmount = New ADODB.command
    cmdGSTSalesAmount.ActiveConnection = cnAccounting
    cmdGSTSalesAmount.CommandText = "SELECT sum(TotalGSTSales) as MonthTotalGSTSales,sum(TotalNonGSTSales) as MonthTotalNonGSTSales, sum(GSTAmount) as MonthGSTTotal, sum(DebitTotal) as MonthInvoiceAmount FROM Invoices WHERE Printed = True AND  DebitTransType = 'I' AND InvoiceDate BETWEEN ? AND ?"
    cmdGSTSalesAmount.CommandType = adCmdText
    cmdGSTSalesAmount.Prepared = True
    Set prm = cmdGSTSalesAmount.CreateParameter("Start", adDate, adParamInput)
    cmdGSTSalesAmount.Parameters.Append prm
    Set prm = cmdGSTSalesAmount.CreateParameter("End", adDate, adParamInput)
    cmdGSTSalesAmount.Parameters.Append prm

And then a recordset is created with

CODE

    cmdGSTSalesAmount.Parameters("Start").Value = StartDate
    cmdGSTSalesAmount.Parameters("End").Value = EndDate
    Set rsGSTAmount = cmdGSTSalesAmount.Execute

David Paulson

RE: Recordset and command object

(OP)
Well, it looks like you can navigate forward and backwards, but unfortunately it is not an updateable one.  Is there a way to create an updateable recordset in this fashion?

David Paulson

RE: Recordset and command object

>What kind of recordset is created when set to a command.execute statement?

Probably a static cursor with a cursor location of adUseServer.


Alot of overhead using an adOpenDynamic cursor type.

Probably better to use adKeySet for the CursorType and a adLockOptimistic LockType, and  adUseServer for the CursorLocation.

RE: Recordset and command object

(OP)
Hi
This is what I have done

CODE

    cmdGSTSalesAmount.Parameters("Start").Value = StartDate
    cmdGSTSalesAmount.Parameters("End").Value = EndDate
    rsGSTAmount.CursorType = adOpenKeyset
    rsGSTAmount.LockType = adLockOptimistic
    Set rsGSTAmount = cmdGSTSalesAmount.Execute

adopenkeyset has a value of 1
adlockoptimistic has a value of 3

When the execute statement has been issued the cursortype now has a value of 3 or adopenstatic and the locktype has a value of 1 or adlockreadonly. So these values are inherited from the configuration of the command.  Can I change these somehow?

David Paulson

RE: Recordset and command object


You would then need to use it as follows:

    '(The following two settings can also be changed to adUseClient and adLockOptimistic or adLockBatchOptimistic, and then used with an INSERT statement or UpdateBatch, as it would then always be a client static cursor)

    rsGSTAmount.CursorLocation = adUseServer
    rsGSTAmount.LockType = adLockOptimistic

    rsGSTAmount.Open cmdGSTSalesAmount, , adOpenKeyset

RE: Recordset and command object

(OP)
Thanks SBerthold

David Paulson

RE: Recordset and command object

David

Why to use the command parameters since you build the SQL statement on the fly? Even more why use the command object?
A simple recordset could do the job, although I cant say if it would be slower. About the LockType =adLockOptimistic or adLockPessimistic,

Quote:

Queries with aggregated data are not updatable see here
If you don't need to move back and forth, use a CursorType forward-only & LockType read-only for a cursor location of adUseServer. Much faster.

------------------------
Happy New Year to All

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close