I hope someone else has had this problem and can help with this.
I recently gave a user an update to the DB I developed for them. It is a split DB. He has been testing the New Interface DB against a copy of the Orginal Data DB (like our standard process entails). In the process of his testing when he tried running a process that had an Update SQl statement the following error was generated
"Function is not available in expression in table - level validations expression"
The error message only affect this ONE linked table (all other tables are fine) and I have no problem running the statement against the Actual table in the Data DB. It only booms out when ran in the Interface. I checked the rights and nothing is out a place. I can't change any data in this linked table, but can change anything I want in the actual table.
In the process of trying to troubleshoot this problem I stumbled across a equally baffling problem. None of the queries that use the built in functions work. They all error out, as if a library was missing. I checked the libraries and the are all present (none are missing).
The Orginal program, which uses the same libaries is stilling working fine, so I don't believe the actual dlls got corrupted. The program works fines for at my location, and appears to work fine everywhere else they have tested. They are going to test it on a few more workstations, but I anticipate the same results, it will work.
Is it possible that there is a version issue with your references? I imagine it has to be something in the environment of the workstation. What references do you have checked? Is it possible the workstation might have an older version of ADO or DAO? What version of Access is on your machine? On theirs? It has been my experience that when Left, Right, Mid, etc. don't work it is something to do with references or the environment the db is running in.
We are both running 2000, except I'm running XP Pro OS where he is running Win 2000. So we know that we have a problem with the ADOx reference, which we fix. However all the other machines in his office, that are setup similarly, are not having any problems. Plus the Older version of the Interface Program runs fine, and there is only minimal differences between the two Interfaces.
I had them reinstall Access, just incase something got screwed up, but as I expected, that didn't help.
I removed all the libraries and then re-added them back and found that even though All libraries were shown as "Not Missing" the Microsoft Windows Common Controls as completely missing when I went to add it back. After I browsed out to it and located it everything worked fine