SQL query help with Access Db

[ChewDoggie]

G'morning all,

I'm not good with sql statements and don't know how to do what I want. If someone could help, that'd be great. I want to query a table and include, in the results, a field that does NOT reside in the table (or in Any Table for that matter) and this field must be updatable (in the recordset). So I have this query that looks like this:

sql = "select racerid, fname, lname, amanum, city, '' as status from racer"
rsRacer.Open sql, cn, adOpenKeyset, adLockOptimistic
If not rsRacer.EOF Then
rsRacer!Status = "Y"
Else
rsRacer!Status = "N"
End If
rsRacer.Update

The "status" field doesn't reside in the racer table. ALL OF THE OTHER FIELDS DO. WHen I execute this sql statement, and try to update the "status" field with new data, I get an error msg, "Overflow"

How can I get around this?

Thanks!

ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[Remou]

Hi
This can never work, no matter what you do. You must use a field in a table. If the racer table is unsuitable, create a new table.

 

[Golom]

The implication of racer.Update is that you want to save the modified values back to the underlying database. Since the field doesn't exist in the database, where do you expect it to be saved?

You can
- retrieve fields that exist from the database
- disconnect the recordset
- manually add the "Status" field
- update it

but that won't solve your "Update" issue. There's still no place to save it.

As it stands, your query is a non-updatable query because of the status field. Why isn't that field in the table since you obviously want to retain it?

 

[ChewDoggie]

Great point! Thanks Golom! That's the answer I was looking for.



ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org