#ERROR on String Functions

Question

Access DB user, all day every business day for 20 years, currently on Windows 10/64 bit  with a 365 subscription. All updates applied. I have long standing clients with unchanged queries that suddenly start throwing #ERROR messages with string functions even though they've worked for years and just worked moments ago.  I'll Compact & Repair or, sometimes, have to create a new DB and import which works temporarily but then it happens again - sometimes minutes, sometimes a day or two later.  Understand there have been NO changes to anything between when it works and when the errors start throwing.  Same DB, same query, same data, same expressions.  One minute it works and the next minute it doesn't.  Sometimes the DB will crash Access first withOUT catching the error and reopening with a compact & repair.  These are long-working query expressions so it is not a coding error.  I deal with a LOT of crummy data and use a LOT of string manipulation to help clean it up and retrieve embedded elements.  This problem is killing me.  I've never contacted MS support before but this is happening to at least one DB on a daily basis, sometimes multiple times a day, and I'm out of work-around ideas.

Trivial example:  InStr("ABCDEFG","D") will and should return 4, of course, but even this expression, using constants instead of variables, will throw #ERROR (along with Replace, Left, Right, Mid, etc.) when it breaks.  Oddly, it seems to me, I've only only had the problem with string functions.  I've even tried it on multiple Windows/64 machines with identical results so it doesn't seem to be a corrupted DLL or hard drive.

Could the problem be 64 bit only?  I installed the 32 bit version of Access to test this and things worked but I could only test for a short time since I use additional software to access the Access DBs which is 64 bit and I discovered it will only read from 64 bit Access so 32 bit is not an option.

Thanks

Answer 1

I'm sorry to hear that. I've referred to Helpdesk in our company, maybe when big company will ask them about malfunction - they will not ignore it.

I also use VBA for substituting standard functions, but this is so annoying - my queries refer to one another and it's very time consuming to review all chains in search for string functions. But it seems that there is no alternatives for now.

Ok then, thanks a lot for your reply and let's wait till it becomes a massive problem. Then it'll be solved I guess.

Answer 2

Thanks. I understand.  I don't have a broken one at the moment but I'm sure I'll be able to answer that question before the day is out. Yes - I have Option Explicit throughout. Nothing is compiled by me.  These are not applications and they are not multi-user.  They are all stand-alone data analysis and reporting databases and I am the only user.  I'll get back after one breaks and I can check references after.

Answer 3

Because this is a very unusual case we have to think about uncommon scenarios such as the one I asked about. I understand your app does not have broken references, but after the error occurs, does it THEN have a broken reference?

Do you have Option Explicit in every module, and you deploy as ACCDE (thus proving that it compiles)?

Also, this is a split application with each user having their own FE, connected to a shared BE?

Answer 4

Thanks but that's the first thing I checked and they all look good -- on both machines I've tried it on.  If a reference was broken I would expect the problem to be constant rather than sporadic.

Answer 5

Sounds like a broken reference. Open the VBA editor and select Tools > References and check for one listed as broken.