can you update a row in a query using ADO?

[Yessen]

I am trying to update a query using ADO but it seems like its not working

it gets the empty recordset and shows an error saying BOF or EOF.

so i came to conclusion, is it really possible to update a query and not a table?

 

[pbaldy]

What exactly is your code? You should be able to update a query. The fact that you're getting an empty recordset indicates the query doesn't return any records.

 

[Yessen]

What exactly is your code? You should be able to update a query. The fact that you're getting an empty recordset indicates the query doesn't return any records.

strSearch = "[rls]='" & rel & "' and [rnd]='" & round & "' and [acr]='" & app & "' and [init]='" & init_id & "' and [hrs]='" & hour & "'"
Set rst = New ADODB.Recordset
With rst
.Open "QPMES", CurrentProject.Connection, adOpenDynamic
.Filter = strSearch
.MoveFirst
End With

QPMES is my query that gets the values from many TABLES.

it stops working when it gets to line .MoveFirst, which means my recordset is returned empty

 

[pbaldy]

Not necessarily; it could be that the filter results in no records. That would be the first thing to clarify. The fields in the filter are all text? I'd caution that if the query is from many tables, it may be read only, which would mean you won't be able to update it from the recordset anyway. Test by seeing if you can edit directly in the query.

 

[Yessen]

Not necessarily; it could be that the filter results in no records. That would be the first thing to clarify. The fields in the filter are all text? I'd caution that if the query is from many tables, it may be read only, which would mean you won't be able to update it from the recordset anyway. Test by seeing if you can edit directly in the query.

is there a way to print the content of rst?

I tried doing the old simple way msgbox rst but it didnt work...

I just want to see whats going on there after .Filter gets applied to rst.

 

[boblarson]

Your filter doesn't look right to me:

strSearch = "[rls]='" & rel & "' and [rnd]='" & round & "' and [acr]='" & app & "' and [init]='" & init_id & "' and [hrs]='" & hour & "'"


is rls, rnd, acr, init and hrs all TEXT fields? I wouldn't think so. That is probably your problem (as Paul had mentioned earlier). If they aren't all text fields then they don't get the quotes around them. If any are dates they need octothorpes (#) around them. More info is needed regarding these fields before moving on.

 

[Yessen]

Your filter doesn't look right to me:

strSearch = "[rls]='" & rel & "' and [rnd]='" & round & "' and [acr]='" & app & "' and [init]='" & init_id & "' and [hrs]='" & hour & "'"


is rls, rnd, acr, init and hrs all TEXT fields? I wouldn't think so. That is probably your problem (as Paul had mentioned earlier). If they aren't all text fields then they don't get the quotes around them. If any are dates they need octothorpes (#) around them. More info is needed regarding these fields before moving on.

I fixed that part. Paul was right by saing that my data in query from different tables is read-only. how can i fix that? I mean change so it could be updatable.

 

[boblarson]

See if this helps narrow it down:
http://allenbrowne.com/ser-61.html