Query Crisis! Please help me...

[Benjamin Piercy]

Dear all,
I am creating a univeristy application database for an
ICT A-level project.
As students leave school they need to be removed from
student table and archived in a exact replica of this
table. I have produced such a table and called it
TblArchiveStudents.
An Append query was created, which appended students
who's leaving date had passed. The criteria used in the
query was <=Date(), which meant that all students whos
leaving date had passed todays current date were
archived, as the query was run.
A delete query was then used alongside this with the same
criteria to delete these student's from the main table,
as they had now all been archvied, thus could be deleted.
I have produced my database at both home and school
transferring the data via CD at the end of every day or
school week. Both versions of Access that i am using at
home and school are identical yet the queries wont work!
The message i get is: Undefined function 'Date' in
expression.
I couldnt get round this problem so i changed the
criteria to <now() and this worked for the Append Query,
however the Delete query was still not working.
A different error was now reported which stated that key
and lock violations were occuring. I know that the
criteria is correct and have been told by technicians
that it may be down to compatability, although the
programs match....
Can anyone shine some light on this situation, which is
ruining my life....?
Any help would be VERY MUCH APPRECIATED.
Best regards to all readers
Benjamin Piercy

 

[fin]

How are you transfering from home to school? More detail.
Entire database, just the tables? Do you overwrite each
database with the new one from home or school?

 

[Rolls]

I can think of several possibilities but it would be hard to fix it based on
the description. First, you may have a datatype problem with a date field
that is causing part of the problem. Second, "key" violations could
indicate datatype or indexing incompatibilities. "Locking" might indicate a
denormalized schema, multiple front-end users trying to access a back-end at
one time, or something else. You have compacted & repaired regularly, of
course...

I'd question your use of physically moving records and deleting them. This
is a SQL database, not an old flat-file DB. Suggest that you use a status
flag instead (T/F field) and then if there's a performance problem "archive"
records only once per year.

It's my practice NEVER to users to physically delete records, but to use a
two-stage Flag - Delete process. Users can only flag, but not physically
erase. Saves lots of unnecessary debate. The physical delete task becomes
an administrator function, not a user discretion.

 

[Van T. Dinh]

There are errors in the References Collection.

See Douglas J. Steele's article:

http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[Benjamin Piercy]

At home I have been burning the complete database to a CD
RW using Nero-Burning Rom. Then at school, the technician
transfers it back onto my disk when I've finished. At
home I overwrite each database with a new one, but not
sure what happens at school.