-----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----- 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
-----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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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.
|
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...
|
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...
|
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...
|
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...
|
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.*,...
|
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...
|
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...
|
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....
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |