469,648 Members | 1,196 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

ADO updateable Recordset in a loop

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I want the recordset (rs) in the following function to be updateable.
How do I do it?

Even though the code is VBA I will be translating to VBS for a MS SQL
Server DTS package. That I know how to do. I just don't know how to
open the Connection and/or Recordset so I can change the SQL WHERE
clause in the loop & then update the resulting recordset.

Thanks for any help. :::mgf

----------------------------------------------------------------

Function ChangeParamsInLoop()

On Error GoTo err_

Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim i As Integer

cmd.ActiveConnection = CurrentProject.Connection
' Parameter is indicated by "?"
cmd.CommandText = "SELECT * FROM billing_period WHERE p_id = ? "

For i = 1 To 4
' Parameters are indexed 0-n.
' Parameters values are placed in Array(0, 1, 2, 3, 4,...n).
' The first ? in the CommandText = index 0. The second ? in the
' CommandText = index 1, etc.
Set rs = cmd.Execute(, Array(i), adCmdText)
Debug.Print rs!p_id, rs!start_date, rs!end_date
Next i

exit_:
On Error Resume Next
rs.Close
Exit Function

err_:
Debug.Print "Error: " & Err.Description
Resume exit_

End Function

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQV3wFIechKqOuFEgEQJKTQCaAtD2Mk0Fkg0vCPKWN3tYoO FrHnEAoKGC
hIfLVcXgoTESeg/trUH0GcwK
=Y6cY
-----END PGP SIGNATURE-----

Nov 13 '05 #1
2 4958
The recordset command returned by the Execute command is never updateable.
Rather than execute the command, open a recordset using the command:

rs.Open cmd, cnxn, adOpenKeyset, adLockOptimistic, adCmdText
Darryl Kerkeslager

"MGFoster" <me@privacy.com> wrote:

I want the recordset (rs) in the following function to be updateable.
How do I do it?
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim i As Integer

cmd.ActiveConnection = CurrentProject.Connection
' Parameter is indicated by "?"
cmd.CommandText = "SELECT * FROM billing_period WHERE p_id = ? "

For i = 1 To 4
' Parameters are indexed 0-n.
' Parameters values are placed in Array(0, 1, 2, 3, 4,...n).
' The first ? in the CommandText = index 0. The second ? in the
' CommandText = index 1, etc.
Set rs = cmd.Execute(, Array(i), adCmdText)
Debug.Print rs!p_id, rs!start_date, rs!end_date
Next i

Nov 13 '05 #2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thanks. With changes your solution worked.

Here are changes:

For i = 1 to 4
cmd.Parameters(0) = i
rs.Open cmd, , adOpenKeyset, adLockOptimistic, adCmdText
Debug.Print rs!p_id, rs!start_date, rs!end_date
rs.Close
Next i

Since the Command object had already set the ActiveConnection (before
the loop), the reference to the Connection was not needed in the rs.Open
command.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQWBdQoechKqOuFEgEQKCagCdE7rxBMP4Zpo/LDPlSFssZeGFXdgAn2Uf
gykWtCLYweG8n+xIrxcJ5vs1
=1LDX
-----END PGP SIGNATURE-----
Darryl Kerkeslager wrote:
The recordset command returned by the Execute command is never updateable.
Rather than execute the command, open a recordset using the command:

rs.Open cmd, cnxn, adOpenKeyset, adLockOptimistic, adCmdText
Darryl Kerkeslager

"MGFoster" <me@privacy.com> wrote:
I want the recordset (rs) in the following function to be updateable.
How do I do it?
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim i As Integer

cmd.ActiveConnection = CurrentProject.Connection
' Parameter is indicated by "?"
cmd.CommandText = "SELECT * FROM billing_period WHERE p_id = ? "

For i = 1 To 4
' Parameters are indexed 0-n.
' Parameters values are placed in Array(0, 1, 2, 3, 4,...n).
' The first ? in the CommandText = index 0. The second ? in the
' CommandText = index 1, etc.
Set rs = cmd.Execute(, Array(i), adCmdText)
Debug.Print rs!p_id, rs!start_date, rs!end_date
Next i



Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Marie | last post: by
1 post views Thread by Edward | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.