Access 2007 Not Running Update Query

[Terry Lawson]

I'm sorry the title is not more explicit, but I don't know what else to say.



About 2 weeks ago I was receiving the message "Error Loading DLL" when trying to import either Excel or Text files using the "Get External Data" tab from the front window of Access 2070, was unable to find the missing dll's as indicated by the Microsoft tech note.

I reloaded Office Professional from scratch after removing it, problem seemed to be resolved.

Today I tried to run a query using the Query Design option from the front Access window, the query is a very simple update for test purposes as I thought I was doing something really stupid :

UPDATE [MERG Suppliers] SET [MERG Suppliers].Test = "YY"

The table name and the field name are both valid, the field that should be updated is definitely text.

When I try to run the query nothing at all appears to happen, no error messages and no update.

Please can somebody give me a clue as to what is not going on.

Terry Lawson








Error Loading DLL in Access 2010Error Loading DLL in Access 2010Error Loading DLL in Access 2010Error Loading DLL in Access 2010

[CJ_London]

When I try to run the query nothing at all appears to happen, no error messages and no update.

Don't have any real suggestions but to be clear, you are executing the query (i.e. clicking the run (!) button, not just using the datasheet view which is effectively just a select query so you see what is currently there. I ask, because others have made the same mistake.

[Terry Lawson]

Don't have any real suggestions but to be clear, you are executing the query (i.e. clicking the run (!) button, not just using the datasheet view which is effectively just a select query so you see what is currently there. I ask, because others have made the same mistake.

Definitely tried with both ! and with right click>open on the query object in the left hand pane of the usual Access window

[Micron]

I notice you have no WHERE clause. Maybe not significant but can't say for sure without knowing more. It's not a multi value field or a table with only one record, is it?
When you go to datasheet view (a preview, in other words) how many empty rows do you see? That is an indicator of how many records will be updated.

[John_G]

Since it is a very simple query, you could try running it from the immediate window (Ctrl-G):

CurrentDb.Execute "UPDATE [MERG Suppliers] SET [MERG Suppliers].Test = 'YY", dbFailOnError

Note the single quotes around 'YY'

Before doing that, turn system messages on with: DoCmd.setWarnings True

With warnings on, Access will tell you how many rows will be updated by the query when you run it.

[CJ_London]

since this is a simple example, why not upload your db? remove unrequired tables, forms, etc. then compact and zip

[Micron]

is there a missing single quote that was described?

"UPDATE [MERG Suppliers] SET [MERG Suppliers].Test = 'YY'", dbFailOnError

[John_G]

is there a missing single quote that was described?

Oops! Yes, there is - thanks, Micron. I thought I had checked it......!

[Terry Lawson]

I notice you have no WHERE clause. Maybe not significant but can't say for sure without knowing more. It's not a multi value field or a table with only one record, is it?
When you go to datasheet view (a preview, in other words) how many empty rows do you see? That is an indicator of how many records will be updated.

No criteria as I wanted to keep it very simple, not a multi value field and there are 37 records in the table "MERG Suppliers"

[Terry Lawson]

is there a missing single quote that was described?

"UPDATE [MERG Suppliers] SET [MERG Suppliers].Test = 'YY'", dbFailOnError

I'm a real wally!!!
Query run in immediate window, error message "Macros Not Enabled"
Macros enabled, query runs perfectly

Thank you all for your help,obviously a wood for the trees and senior citizen moment!!

Regards

Terry Lawson