Hello,

I'm trying to connect to an ADO recordset coming from a Pervasive
2000i database via ODBC, using VB6, and update two fields, but for
some reason I'm not able to do the update. Below is what I hope is
the relevant part of my code. The database connection seems to open
and read records fine, but further down I (try to) assign the value of
a variable to a field as follows -- "rst!BegAccumDepr = BegAccumDepr"
and at that point the code errors out with the following message
"Run-time error: '-2147352566 (8002000a)' Out of present range." The
field I want to update does not actually exist in the database, I
create that one and another at the end of the SELECT clause of my
query: "SELECT field, field, field, 0 AS BegAccumDepr, 0 AS
CurrentDepr" to use as a placeholder. Ultimately I need to use the
updated recordset to run a Crystal report. Any help is appreciated.


Robin


~~~~~~~~~

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
cnn.ConnectionString = "DSN=LP70Lease"
cnn.Open

rst.ActiveConnection = cnn
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

rst.MoveFirst

~~~~~~~~~~~~~~~~~

Robin,
the SQL AS clause creates an alias. It does not create a field.
You have not created a field so you can not update it.

ADO fails because there is no underlying field to update.

If you want to create a field in the underlying table you can use
ALTER TABLE [table name] ADD [field name] [column data type] [size];

"Robin" <duncanstreet2004 (AT) yahoo (DOT) com> wrote

Thanks for your reply. I was suspecting that something like this was
the case, but was hoping there was a way around it. If I can't make
changes to the underlying table (which is actually a SELECT statement
using 6-7 tables) can I create a place to store these additional
"fields" by creating a view with my select statement and adding those
two additional fields? If that won't work, and knowing I can't alter
any tables, do I have any other options?

Thanks again.


Robin


"Doug Bell" <dug@bigpond> wrote

Robin,

I am not totally familiar with using views and Crystal but I think like
in Access we can create a Query and then maybe Crystal can be run based
on the results of that query rather than a table.

Dale,

Yes, you're right, in CR9 they're called SQL Commands. I am basing my
report on a SQL Command (essentially a query file) that's the same as
the SQL statement I'm using in my code, but I need to take some of
those database fields, do some calculations, and then send two
additional "fields" or pieces of information back to the report to
display so that the report doesn't have to do the calculations. I
can't have the report do it because it takes an exorbitant amount of
time, so I wanted to have the code/server do the calculations and then
just ask the report to display. I tried to create placeholders for
these calculated values and then update them as I mentioned, but since
as I did it those fields don't really exist, I guess I can't. I have
also discovered that I can't create a view with a couple of extra
fields and then update those extra fields, so that idea's out too.

I wonder if the next option I should investigate is creating a new
table from my SQL statement, and add in the two fields there, and then
I WOULD have real fields to update. I've done this plenty of times in
Access, but am a little nervous about doing it in Pervasive given the
fact that it's a record manager and all that, but perhaps that's
unfounded.

Thanks for your help.


Robin



Dale <dale0610 (AT) hotmail (DOT) com> wrote

I have been able to solve this problem by creating a CSV file from
code, and then running my report on that.