Another query query

[John Sh]

I have a number of update queries, similar to the code below, that operate normally, update the table as expected, if I open them manually but do nothing if opened with code.
I have tried "docmd.openquery" and "Currentdb.execute" with the same result, the table "Main" is not updated.

UPDATE Main INNER JOIN TableTemp ON Main.AccessionNumber = TableTemp.AccessionNumber SET Main.SpeciesEpithet = [TableTemp]![SpeciesEpithet]
WHERE (((TableTemp.SpeciesEpithet)>"") AND ((Main.AccessionNumber)=[TableTemp]![AccessionNumber]));

 

[June7]

You allow empty string in text fields?

There is no need for the same expression in ON and WHERE clauses. Use the ON clause.

You enter data to a temp table via a form then when satisfied with input, commit to Main table? You need to make sure the temp record is first committed to table. Record is committed when: 1) close table/query/form or 2) move to another record or 3) run code to save.

 

[John Sh]

You allow empty string in text fields?

There is no need for the same expression in ON and WHERE clauses. Use the ON clause.

You enter data to a temp table via a form then when satisfied with input, commit to Main table? You need to make sure the temp record is first committed to table. Record is committed when: 1) close table/query/form or 2) move to another record or 3) run code to save.

1) The inherited table is full of empty strings and many are allowed simply because the data is often not available. They are not, however, null!
2) The "Tabletemp" is loaded with these empty fields so the main table can be corrected. The fields to be corrected are bound to the "TableTemp" fields.
3) I used this approach because the select query that loads "Tabletemp" correctly was returning incorrect records from the "Main" table.
4) The query was built in the query design window so I have little control over what it produces.
5) My knowledge of SQL is, at best, extremely limited, but I am slowly learning.
6) Will removing the "Where" clause correct my basic problem of the query not working when called from code?
7) Running the query as strSQL with currentdb.execute returns an error that is particularly unhelpful.
8) Where can I find a reasonably good article on SQL?

Thank you for your response.

 

[John Sh]

I have solved my problem by using a filter.
I am now able to bind to the appropriate table which means I need no select or update queries and my code is much simpler.
I am, however, still interested to know why a query will work when opened manually but not when opened through code.

 

[The_Doc_Man]

Depends on how you open it. Watch out because I'm going to get a little bit pedantic for a moment:

This is what happens when you attempt to open a query.

DoCmd.OpenQuery method (Access)

Office VBA reference topic

learn.microsoft.com

This is what happens when you attempt to run an SQL action sequence.

DoCmd.RunSQL method (Access)

Office VBA reference topic

learn.microsoft.com

The distinction is that a DoCmd.OpenQuery tries to open a SELECT-class query in Datasheet view. Action queries in Access SQL do not return a result-set and so cannot be opened that way - nothing to open, you see. A DoCmd.RunSQL tries to execute an action-class query, which in Access is not expected to return anything so doesn't even try to employ datasheet view. In essence, if you use the wrong DoCmd method/action, you get the wrong operational result. AND using .OpenQuery only makes sense if you were trying to display/visualize the returned recordset from a SELECT-class query anyway.

Now, the preferred answer: IF you want to execute an INSERT, UPDATE, or DELETE query, don't use DoCmd at all. Instead, use the .Execute method, either with the CurrentDB object as a prefix, or with a DAO Database object.

Database.Execute method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

 

[June7]

I disagree. From that first link: "This action runs an action query." I did a test and OpenQuery definitely executed an UPDATE action query object.

The only way I could answer OP's question is to review data and query and code.

I NEVER allow empty string in field.

 

[John Sh]

I disagree. From that first link: "This action runs an action query." I did a test and OpenQuery definitely executed an UPDATE action query object.

The only way I could answer OP's question is to review data and query and code.

I NEVER allow empty string in field.

I am not well enough versed in the intricacies of Access to disagree with doc man but am certain that I have used docmd for action queries. Given I only discovered currentdb.execute yesterday I have no fear in stating that.
As stated in a previous post, I was able to get around my problem using a filter and, yes, I am writing to bound fields. If the data is bad it will still finish up in the table, regardless of the entry method. I am, however, running a split database in the live system.
As for the empty fields, I am dealing with a table of scientific data where information may, or may not, be available, so, empty fields.
The table is some 90 fields wide, all data, blank or not, must be visible on a single form and access cannot handle the number of relationships necessary to normalise the table.
How would you deal with this, short of filling the fields with gobbldey gook?

 

[ebs17]

DoCmd.OpenQuery for itself does exactly what you do with a manual open query. There is no meaningful explanation for differences.

What does "does not work" mean in your wording?
Syntax error with error message? No data changes in the target table?
If no data changes, it may be because the filter used does not offer any records for the action, or that the change to new has already occurred and a second working execution of the query cannot produce a different result.

Running the query as strSQL with currentdb.execute returns an error that is particularly unhelpful.

If I am looking for a problem, I would definitely consider an error message helpful and try to understand it. What is an example of such a message?

 

[The_Doc_Man]

OK, @John Sh - I did not say the action query wouldn't run after an .OpenQuery . Action queries just won't return records so the action doesn't succeed in doing what a .OpenQuery normally does - which is to ... wait for it ... open a query (by default, to datasheet view).

access cannot handle the number of relationships necessary to normalise the table.

My knowledge of SQL is, at best, extremely limited, but I am slowly learning.

I think that because of the second quote, you are perhaps not the best authority to offer the first quote. Access allows up to 32 formal relationships per table (minus the number of indexes). You can also build layered queries to use a "divide and conquer" approach, a type of grouping by layers. If you have as many potential blanks as you describe, your normalization problem isn't due to numbers of fields, it is due to (lack of) content. And finally, you can build queries without ANY relationships - you just have to do them by hand using either WHERE syntax or JOIN syntax (in combination) - and it CAN be done with only WHERE clauses. If you have a WHERE ... X=Y AND W=Z AND ... clause, you can have up to 99 AND keywords.

WHERE (((TableTemp.SpeciesEpithet)>"") AND ((Main.AccessionNumber)=[TableTemp]![AccessionNumber]));

Here I'll simply point out that using ">" is probably incorrect when dealing with strings. You probably want "<>" if you mean "not blank." Or "Len(SpeciesEpithet)>0" for clarity of purpose.

 

[ebs17]

WHERE SpeciesEpithet > ""

This is super correct and neatly worded.

Who claims the opposite, should lead the proof that there is something smaller than the zero string.
NULL contents are also excluded by the comparison, and index usage is possible, while a <> or a calculated field does not provide this.

Whether @John Sh knows this or just copied it correctly doesn't matter at this point. Better would be of course the first case.

 

[CJ_London]

one of the benefits of using null instead of "" is indexing.

You can set an index to ignore nulls so where you have a column of data, of which only 10% of the rows are populated, the index will be much smaller and faster if nulls are ignored.

However as doc says, there is a limit to the number of indexes you can have. But if the table was normalised I would think the 'master' table would only need one index (for the PK) whilst the 'child' tables would only need one or two (depending on the relationship being one to one or one to many). Space would also be saved if the 'null' records are not saved and a left join used).

But we don't know enough about the OP's app requirements and whether performance is an issue