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?
 
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
0
 
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?
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
0
 
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.
0
 
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.
0
 
TextReportCommented:
Have tou tyried dropping the attached table and reconnecting it? Cheers, Andrew
0
 
TextReportCommented:
sorry about the typos, Have you tried dropping the attached table and reconnecting it? Cheers, Andrew
0
 
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.
0
 
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.

0
 
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?
0
 
Rey Obrero (Capricorn1)Commented:
?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.