Odd problem running a query from VBA (1 Viewer)

[The_Doc_Man]

Actually, for Access, I would expect 12/30/1899 as the "zero" date. Excel uses 12/31/1899 as its zero date. Neither Access nor Excel would store 1/1/1900 as 0 internally. See also later discussion after my code example.

You are right that a JOIN statement would require "ON" clauses but you are incorrect that you can't use field names. I think this would be legal and might even work more or less as you want:

UPDATE
    tabMain AS M INNER JOIN tabTemp AS T ON M.Last = T.Last AND M.First = T.First
SET
    M.Position = T.[Position],
    M.MoveDate = T.[Move Date],
    M.RecLastImported = Now()
WHERE
    T.Ambig_Name = False AND
        ( NZ( M.Position,'' ) <> T.[Position] OR
        NZ( M.MoveDate, 0) <> NZ( T.[Move Date],0 ) ) ;

Here's the thing: That NZ on M.MoveDate works OK to set dates to represent zero because a date IS a number internally to Access. At worst, if the JOIN ... ON doesn't work because of bad data, it might still be fixable.

Probably should toss an NZ for T.Position just to avoid nulls on the "T" side of your query. Also, you wrote T.[Position] but in fact you can avoid that. There are no spaces in T.Position so you don't need bracketing. Also, is it correct that you use M.MoveDate but T.[Move Date] ?

 

[CJ_London]

No don’t think I’ll bother, don’t want to waste my time

 

[CJ_London]

With regards the sql, you have fields called first and last which are reserved words which is not recommended - they can cause misleading errors. at the very least enclose in square brackets. Also you may get an error if any of them are null. Making docs suggested change to a criteria should resolve that one though.

I would also look at your data, the implication would appear to be you only expect one match, it may be you are getting more than one

 

[Papa_Bear1]

Not to light another fuse, but I think we all know Access <> Oracle. But that is kind of the point too. I've built a lot of nice, efficient, handy tools with Access. Is it perfect? No. Does it meet every requirement? No. Is it bulletproof? Not by a longshot. But you can get a LOT done with it - and VERY quickly. I've built tools that translate jobs that took many hours before the solution --- to minutes. I've both thoroughly enjoyed using it - and also encountered various frustrations with MS's lack of attention to things that matter to us... ...and so it goes...

 

[conception_native_0123]

I've both thoroughly enjoyed using it - and also encountered various frustrations with MS's lack of attention to things that matter to us... ...and so it goes..

access is just fine. many people do wonders with it, as I have done some myself. even though some people think I'm a fake. my comment was geared more towards the fact that everything has been moving towards the cloud for quite some time. Access is not a cloud or web application. that is all.

 

[Papa_Bear1]

With regards the sql, you have fields called first and last which are reserved words which is not recommended - they can cause misleading errors. at the very least enclose in square brackets. Also you may get an error if any of them are null. Making docs suggested change to a criteria should resolve that one though.

I would also look at your data, the implication would appear to be you only expect one match, it may be you are getting more than one

Right --- Well - those are not the real fieldnames - sorry 'bout that - as I said - I had to sort of mash up a sample...

I think it may be too difficult to troubleshoot here without the real query. I was hoping - though - that the issue was NOT the query - since it CAN run - just by clicking the play button. Really was almost hoping it was a known bug with a known work-around or something like that... Ha... But it is indeed possible that there is still something wrong with it. I think I will systematically remove fields until it works. If I find anything out worthy of note - perhaps it would be worth posting here.

Thanks all for the suggestions --- all very good!

 

[theDBguy]

Really was almost hoping it was a known bug with a known work-around or something like that...

At the risk of repeating myself, one workaround I know of is to introduce a time delay, just in case the problem has something to do with a timing issue. Please let us know the results once you get a chance to try it.

 

[The_Doc_Man]

At the risk of asking a question because I missed the answer in skimming through this, are we talking about an Access back end (vs. some type of active SQL back end)? If this IS a native Access back-end file, then timing should not be a consideration because Access is single-threaded - unless somehow Access 365 has suddenly been upgraded to go multi-threaded. And by now I would have thought that I would have heard of such a thing if it had happened. But I have not heard of this. If I missed that it is an active back-end case, timing can be an issue.

As to this error message, actually it tells me a bunch.

Cannot update '(expression)'; field not updateable.

This message is ABSOLUTELY correct if your query contains any kind of expression and you are trying to update through that query. You see, if you have a query like this behind a form:

SELECT A, B, C + D FROM MYTABLE ;

You cannot use that query to update MYTABLE because Access doesn't know how to write back C+D. It could write back C. It could write back D. But what should it write back to update C+D, which is an expression? Or an "(expression)" if you prefer. This is why, for future reference, you should ask error message questions with the complete text of the error message as well as the number.

 

[Minty]

Just to add to the comments - your hash up of this NZ(M.Movedate,'1/1/1900')
(
NZ(M.Position,'') <> T.[Position] OR
NZ(M.MoveDate, '1/1/1900') <> T.[Move Date]
);

I don't think can work if MoveDate is actually a date it should be #01/01/1900#

 

[Papa_Bear1]

At the risk of asking a question because I missed the answer in skimming through this, are we talking about an Access back end (vs. some type of active SQL back end)? If this IS a native Access back-end file, then timing should not be a consideration because Access is single-threaded - unless somehow Access 365 has suddenly been upgraded to go multi-threaded. And by now I would have thought that I would have heard of such a thing if it had happened. But I have not heard of this. If I missed that it is an active back-end case, timing can be an issue.

As to this error message, actually it tells me a bunch.



This message is ABSOLUTELY correct if your query contains any kind of expression and you are trying to update through that query. You see, if you have a query like this behind a form:

SELECT A, B, C + D FROM MYTABLE ;

You cannot use that query to update MYTABLE because Access doesn't know how to write back C+D. It could write back C. It could write back D. But what should it write back to update C+D, which is an expression? Or an "(expression)" if you prefer. This is why, for future reference, you should ask error message questions with the complete text of the error message as well as the number.

OK - but if the query is fundamentally flawed, how is it that it works fine upon hitting 'play' in VBA after clicking Debug? This is why I started the thread thinking it was NOT the query... I don't see how the query can be OK and not OK at the same time. It works perfectly if I simply let it run after it "pseudo" errors...

 

[Papa_Bear1]

At the risk of repeating myself, one workaround I know of is to introduce a time delay, just in case the problem has something to do with a timing issue. Please let us know the results once you get a chance to try it.

I tried putting a loop in that error-catching cycle - but still no go. It seems the query won't work when "requested" to be run from VBA - but when "requested" be run by ME - (whether a pre-made query, or simply letting it continue even) - THEN it runs. I find this to be really strange...

 

[CJ_London]

Have you tried stopping the code before it runs then stepping through it?

 

[Papa_Bear1]

Just to add to the comments - your hash up of this NZ(M.Movedate,'1/1/1900')
(
NZ(M.Position,'') <> T.[Position] OR
NZ(M.MoveDate, '1/1/1900') <> T.[Move Date]
);

I don't think can work if MoveDate is actually a date it should be #01/01/1900#

Interesting.
As I've noted - (I think) the query works fine when I hit the play button after it errors... so again - I don't see how it can be a flaw with the query - when it works - just only after it complains... I will double-check this though!!

 

[theDBguy]

I tried putting a loop in that error-catching cycle - but still no go. It seems the query won't work when "requested" to be run from VBA - but when "requested" be run by ME - (whether a pre-made query, or simply letting it continue even) - THEN it runs. I find this to be really strange...

Hi. Sorry to hear that. Since we can't see what you're doing, I can't imagine what else to recommend. Hope you find a solution. Let us know if you do. Good luck!

PS. If you decide to post a sample/demo db, we could try to go further.

 

[Papa_Bear1]

Have you tried stopping the code before it runs then stepping through it?

Actually, no - I had not tried that. I JUST tried that - pausing it right before where it complains, and when I step through - it works... Hmmmmm... Does that generate any new ideas?

 

[Papa_Bear1]

Hi. Sorry to hear that. Since we can't see what you're doing, I can't imagine what else to recommend. Hope you find a solution. Let us know if you do. Good luck!

PS. If you decide to post a sample/demo db, we could try to go further.

Kudos to @theDBguy - as he put me on a track that seems to have resolved this mystery.

After doing a little online hunting for "Debug mode behaves differently than run mode" kind of thing - I came across someone with the same kind of problem - but in Excel. Their solution was the same as @theDBguy recommendation - "...give it some time...".

Although I did put a delay loop within the error trapping feedback loop - I had just run a long variable to 10000000 or something like that. The solution in the Excel forum called a separate routine to run the delay. It also happened to include DoEvents in that delay loop.

I incorporated a separate delay function as they did, and included the DoEvents as well.
I ran it with a 4-second delay.
It worked!
I ran it with a 2-second delay.
It worked!
Just out of curiosity - I decided to try and ONLY run a DoEvents right before the problem query - with no call to another routine or anything else.
It worked!

I'm kind of annoyed because I'm pretty sure that was one of the first things I tried.
Which has me wondering if this is just another case where it isn't really fixed.
I thought I had this fixed once before - but it wasn't really fixed.

For now - though, it seems like DoEvents right before this query has made all things right with the world.
As to why - well that is well outside my realm of understanding...

Thanks so much to all for helping!

 

[Papa_Bear1]

Actually, for Access, I would expect 12/30/1899 as the "zero" date. Excel uses 12/31/1899 as its zero date. Neither Access nor Excel would store 1/1/1900 as 0 internally. See also later discussion after my code example.

You are right that a JOIN statement would require "ON" clauses but you are incorrect that you can't use field names. I think this would be legal and might even work more or less as you want:

UPDATE
    tabMain AS M INNER JOIN tabTemp AS T ON M.Last = T.Last AND M.First = T.First
SET
    M.Position = T.[Position],
    M.MoveDate = T.[Move Date],
    M.RecLastImported = Now()
WHERE
    T.Ambig_Name = False AND
        ( NZ( M.Position,'' ) <> T.[Position] OR
        NZ( M.MoveDate, 0) <> NZ( T.[Move Date],0 ) ) ;

Here's the thing: That NZ on M.MoveDate works OK to set dates to represent zero because a date IS a number internally to Access. At worst, if the JOIN ... ON doesn't work because of bad data, it might still be fixable.

Probably should toss an NZ for T.Position just to avoid nulls on the "T" side of your query. Also, you wrote T.[Position] but in fact you can avoid that. There are no spaces in T.Position so you don't need bracketing. Also, is it correct that you use M.MoveDate but T.[Move Date] ?

Right - so for the date - I was merely trying to handle nulls - I didn't really care what value it took, as long as it set it to something. I probably assumed too much that adding the NZ as written made the problem go away - but it at least "seems" to work. I will double-check it more closely.

Not sure what you mean about the join. The issue I faced was that I had to use multiple fields as a join, where some could be null. The only efficient way I've found to handle that is to concatenate them since at least one of them would not be null. However, the formalized join with "ON" is not particularly fond of such "constructs" and won't even present that join in the visual builder (for example) - so - since it is an inner join - I figured I'd just move it to the WHERE clause.

It is interesting that it works without the NZ on the T side of it -- I guess it is setting it to Null when null, and the value when it has a value.

The T side of this is a temporary table with the not-so-great data from Excel in it - and it has fieldnames that might have spaces in them. Since my code is dynamically handling things - I just always wrap them in the square brackets on that side. The example, again, was just my hand-jammed text - but many of them do have spaces - requiring the brackets etc.) The M side of things is the table I have control over - thus - no spaces. (Who are these people putting spaces in field headers!? Ha - happens a LOT in Excel - but folks treat it like Word - so it is not a surprise.

Thanks!

 

[theDBguy]

Kudos to @theDBguy - as he put me on a track that seems to have resolved this mystery.

After doing a little online hunting for "Debug mode behaves differently than run mode" kind of thing - I came across someone with the same kind of problem - but in Excel. Their solution was the same as @theDBguy recommendation - "...give it some time...".

Although I did put a delay loop within the error trapping feedback loop - I had just run a long variable to 10000000 or something like that. The solution in the Excel forum called a separate routine to run the delay. It also happened to include DoEvents in that delay loop.

I incorporated a separate delay function as they did, and included the DoEvents as well.
I ran it with a 4-second delay.
It worked!
I ran it with a 2-second delay.
It worked!
Just out of curiosity - I decided to try and ONLY run a DoEvents right before the problem query - with no call to another routine or anything else.
It worked!

I'm kind of annoyed because I'm pretty sure that was one of the first things I tried.
Which has me wondering if this is just another case where it isn't really fixed.
I thought I had this fixed once before - but it wasn't really fixed.

For now - though, it seems like DoEvents right before this query has made all things right with the world.
As to why - well that is well outside my realm of understanding...

Thanks so much to all for helping!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.