Function is not available in expressions in table-level validation expression

OK, this is NOT a References problem (which is the common answer to posts on this error across many forums.)

I can update the table in question both manually and by query when I work on the back-end db. I get the error when trying to update it as a linked table in the front-end db, both manually and by query.

This is on my own machine so no question of different dlls etc.

I have also used the tables in question when linked into other front-end developments and never had this error.

There are no field or table validation rules anyway, nor is relational integrity enforced. The nearest we come to that is default values using Date() or Now() for date and time stamping records on insert. Again, the table in question has been in use for some time in other applications with no errors. All that I am trying to do is to update one field.

Any suggestions?
wansecretsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

(I have also used the tables in question when linked into other front-end developments and never had this error.)  
are you using the same machine for this ?


if using the same machine and one application works and the other does not,
we can rule that it is the application that does not work is faulty.

it could be corrupted..

have you tried creating a new one and test the same operations?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you have no other items being used as Defaults in Columns?

In the linked scenario - are you directly updating the table (i.e. doubleclick the table and try to add/edit data)?

Sounds like a compact and repair would be the first order of business (make a backup FIRST).

If that doesn't work, you might try a Decompile, although if this is data only it'll do no good.

If nothing else, import everything into a new, blank database.
TextReportCommented:
"OK, this is NOT a References problem" I take it you have double checked the front end database to ensure there are no missing/broken references.

If all you have done is attached to a backend that works with other databases then there is very little it can be apart from the references and corruption.

Cheers, Andrew
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

wansecretsAuthor Commented:
Yes, I've tried Compact/Repair, and tried using Jetcompact as well: no difference.

Yes, the error ocurs when I try to update the raw data in the table manually - but only when it is a linked table. First edit throws an error, after that you can edit any row/any column without any errors. Close the table, reopen and try another edit, first one throws an error and after that it is ok. Go figure!!!

Yes, I develop all applications on this machine so the other apps were developed on this machine.

Yes, of course I checked for missing references - even though there are no validation rule expressions, so no function calls, so no missing libraries not to find them in.

Yes, I have created a new application from scratch and the problem has not recurred; just a lot of wasted time copy/paste code because I don't want to import any problems by importing any forms/queries/modules.

Still does not answer the question: What causes this false error?

Lots of posts in lots of forums from lots of people having the same bug in their Access 2003: no real answers.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<Yes, I have created a new application from scratch and the problem has not recurred>

Then it would seem you have some form of corruption that wasn't fixed with a C&R. That's not all that unusual; the recommended path to really fix corrupted databases is to do a C&R and then immediately import to a new, blank container. This resolves any issues with container corruption, as well as object corruption.

<Lots of posts in lots of forums from lots of people having the same bug in their Access 2003: no real answers.>

I'd say that you've answered it yourself - import to a new, blank database and use that one. It's standard, recommended procedure for any Access environment.

You mention wasted time copying code and such ... why would you need to do that? If you don't want to import forms/queries/modules, then just don't select them when you do the Import ... the import utility even has Select All features that are object-specific ... it takes me all of about a minute to import tables to a new database, regardless of the number ... but perhaps I'm missing something here.
TextReportCommented:
Have tou tyried dropping the attached table and reconnecting it? Cheers, Andrew
TextReportCommented:
sorry about the typos, Have you tried dropping the attached table and reconnecting it? Cheers, Andrew
wansecretsAuthor Commented:
Yes, Andrew, first thing I did after discovering that I could edit the table in the backend but not as a linked table.

This question was asked and answered here in another thread: that persons soution was to drop the use of Date() as a default value for a datestamp field. I'd have to go back and modify the application that creates these records first.

As to not importing objects into a clean db, LSM: we had lots of corrupt codebase following the 2003 SP3 bug. This corrupt codebase was not fixed after the hotfix was applied. Importing anything from that corrupt codebase led to the symptomatic SP3 bug errors occuring even in a new db. So I now copy/paste any code/sql into new queries and forms instead of importing them from dbs that I know have problems. It saves a lot more time in the long run. (importing tables was not an issue - they are linked tables)

Anyway, I've worked around this now, but would like to know the cause just for the knowing of it.
TextReportCommented:
As you are probably aware the DATE() and NOW() functions are both in the VBA Reference, the only other thing other than corruption I can think of is if the database has a compile error for any reason.

Sorry, Andrew

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wansecretsAuthor Commented:
Well, I know now that M$ considers a default value to be a table-level validation rule: the other guy removed them from his column defaults and the problem disapeared. Go figure that one??

Anyway, since the problem occured only when the table was linked into a specific application, I just scrubbed that one and started again. So problem solved and thread closed.

Thanks for all your comments and suggestions.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So, was this a Compile error? That is, you performed a Compile and the error has stopped?
Rey Obrero (Capricorn1)Commented:
?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.