473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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 5176
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: !TG | last post by:
I currently use Do while loop, but I'd rather use a For Loop though I have never gotten the hang of them. Would some one please be so kind as to show me how to loop through a recordset.
7
by: ren | last post by:
Hello, When I open with Access 2000 a .DBF table and try to modify the content of a (random) record, I get the message 'This recordset is not updateable". And indeed, I can't change the content...
3
by: jm | last post by:
I have a simple query. It has two tables. If I open the tables separately, I can add records. If I open them up together, the recordset is not updateable. As best I know, this has never been a...
4
by: MNC | last post by:
I'm using Access2002, and can't seem to get an updateable recordset going :-( What am I doing wrong, here's the code. The form's controls are not locked, the recordset type is Dynaset (changing...
5
by: Marie | last post by:
Access97 I have a table containing addresses with a separate field for State. Is there a way to create a query that returns an unique list of the states in that table and still be updateable? I...
1
by: Edward | last post by:
I've recently migrated the back end of one of my client's applications from Access to SQL Server. One of the forms is based on an Access query thus: SELECT dbo_tblDistributionDetail.*,...
3
by: dd_bdlm | last post by:
Please help this one is driving me mad! I have searched and read all the topics on the error message I am receiving but none seem to apply to me! I have quite a complex query linking all parts...
27
by: Bob | last post by:
running access 2k; mdb w/ linked tables to another mdb (front/back-end); trying to run a query that updates a table FROM information from a crosstab query. I AM NOTT trying to update the...
2
by: jghouse | last post by:
Everyone, Hopefully you can help me with a little problem I am having. I have a need to limit the records shown in a form by a few different criteria. I also need these records to be editable....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.