Greetings,
I have searched hi and lo through the groups and haven't found my
problem specifically. I have listed below what I have tried based on
what I have found in the groups. Please let me know if what I am
trying to do is even possible; if it is, any insight is greatly
appreciated.
I am building an ADO recordset from an SQL server, connection
properties as follows (coding in VBA/A2K):
conn.Provider = "MSDataShape.1"
conn.Properties("Data Source").Value = "Server"
conn.Properties("Initial Catalog").Value = "Database"
conn.Properties("Password").Value = "pw"
conn.Properties("User ID").Value = "id"
conn.Properties("Data Provider").Value = "SQLOLEDB.1"
conn.Properties("Persist Security Info").Value = True
SQL="Select field1, field2, "MadeUpField" as field3 From tblFields"
rst.Open SQL, conn, adOpenDynamic, adLockPessimistic
The recordset will be set to a form's recordset in A2K. The values for
field3 come from another entire data source, so I am trying to fill in
the values for field3 dynamically by moving through the recordset,
before binding the recordset to the form.
I have tried to modify the field3 value in the recordset resulting from
using the SQL above as follows:
Do Until rst.EOF
rst.Fields("field3").Value = strSomeValue
rst.Update
rst.MoveNext
Loop
This gives the 'Multi-step operation generated errors. Check each
status value.' error when it gets to the
rst.Fields("field3").Value = strSomeValue line of code.
I have also tried using this SQL:
SQL="Select field1, field2, From tblField" (not including the added
field)
and this code to append the field to the recordset:
rst.Fields.Append "field3", adChar, 50
rst.CursorLocation = adUseClient
rst.Open SQL, conn(same as above), adOpenDynamic, adLockPessimistic
rst.MoveFirst
Do Until rst.EOF
rst.Fields("field3").Value = strFieldValue
rst.Update
rst.MoveNext
Loop
This code give me 'Item cannot be found in the collection corresponding
to the requested name or ordinal when it gets to the
rst.Fields("field3").Value = strSomeValue line of code.
I am dying here, any help is greatly appreciated!
rn