Update Query Not Updating!

[GarstonMassive]

Hi I'm working with A97 and am finding that an update query that I'm trying to run is very tempremental in that sometimes it works, other times it doesn't! The query is a very basic one invloving no WHERE clause and is along the lines:

UPDATE tblTest SET fldField1 = 'Hello World'

I run this query when a recordset based on that table is ALWAYS open. Could this be having an effect? Should I close the recordset?

Has anyone got any ideas?

 

[PHV]

I guess that records LOCKED by the recordset aren't updated ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GarstonMassive]

Cheers PHV for your reply.

I've opened the recordset as dynaset though. And, thinking about it, I've even tried to iterate through them and update them (in code within the recordset) and fairly frequently they wont update.

I think this has something to do with either references or compiling the DB.

Any other ideas?

 

[GarstonMassive]

Has anyone else any thoughts on this at all?

 

[abaldwin]

I think it may be helpful to post the code that runs the update. This could be a refresh problem with Access as well.
Please post your code showing the connection string, the build of the sql statement and the execute line.

Thanks


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[GarstonMassive]

Thanks for your reply Alec.

If you look at my original post you'll see the simple update statement in there. It really is no more complicated than that!

There are no connection strings etc.

I think this could potentially be a refresh problem or as I said earlier, something to do with refs, compiling.

 

[GarstonMassive]

By way of follow up this was related to my Access tables not refreshing quickly enough so I put a 5 second delay into the routine just after the VB app had populated the tables, and this had the desired effect.

 

[abaldwin]

Glad I could fix this one for ya too! LOL


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[NvrSaDi]

Expanding on PHV's comment, if you have a form open that is linked to the table with a record open on the form, the query will treat that record as a locked record and bypass it when you run the query.