Corrupt db? - it has 'forgotten' some functions

[HelsByTheDock]

Hi all,

This is my first visit, and I have to say I'm impressed by the content here - nice to find a forum that gets to the point, and so shall I...

I have an MS Access db (2000 format, but built in v2003). After making a number of unrelated form changes 1 of my queries now fails with the following error:

"Compile error. in query expression '(((Date()) BETWEEN [Valid_From] AND [Valid_To]))'"

The query is actually correct, but Access no longer recognizes the Date() function.

I've tested this by creating a new query in the database, here's the SQL:

SELECT tblLookupParent.Parent_Code, Date() AS Expr1
FROM tblLookupParent;

This query comes up with a similar error:

"Compile error. in query expression 'Date()"

If I create a new database and import the table, the query will run perfectly as expected, however the same problem soon reappears.

I have also repaired and compacted successfully - but no difference. Also tried on a different PC - same errors.

I've trawled the internet for weeks with no luck, any ideas will be greatly appreciated.

Thanks,
Hels

 

[belovedcej]

Sounds like a references issue. I can't remember exactly what the solution is, but that might help you in your search. It's in the code, under Tools/References. You could try looking at what references are checked versus which ones are checked in a db that works. If they are difference, just select the one that is missing. If they aren't, write down what they are, uncheck them, save, and then recheck them.
(They will be in a different order)

If that still doesn't work, it may mean that it can't find a reference. That happened to me when I took a db from one computer to another. Look for a reference that says it is missing and see where it is located. It probably needs to be in the system folder for it to find it.

There is stuff out there on the web if you look for "missing references" and such. My guess is that is your problem.

 

[HelsByTheDock]

Thanks belovedcej - I've had a look at the references and they appear to be the same. I have the following selected:

Visual Basic For Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
OLE Automation

Am I right in thinking the only access to 'References' is if you open a VBA code window and goto Tools - References?

I have also found another issue - when I import all the queries/forms etc into a new database, my joins are missing from all my queries.

The Date() function is built into Access so I guess this is in the MS Access 11.0 Object Library?

Still really stuck!

 

[HelsByTheDock]

Great news this Saturday afternoon!

I think I have fixed this bizarre error...

I found that in 1 of my modules I'd left some unused code at the bottom which would not compile as it was partially commented out. I commented out the remaining Public Declare Function statements and hey presto the errors have stopped.

The DB is undergoing some enhancements at the mo - hence pieces of code left in - the lesson is to make sure that any code that is not running is at least commented out in full during development and removed from live versions!

I live, I learn! ;-)