Is there a way to determine the date?

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi all,

It was found out that some users might have been entering new records data
in an older mdb. Unfortunately, the new and the old mdb were both resisdeing
in the same directory in the network (not my idea)

I would like to ask if there's a way in looking at the records in the table
of the older mdb and determine if they were added recently. If they were I
could then just extract them out and put them back in the newer mdb's table.

After this run-in, mgmt agrees to relocate the older mdbs to a different
location.

Thanks in advance,

Ben



--
 
In table design (requires users to be out of the database), if you add a
field (such as CreateDate), and set its Default Value to Date(), when new
records are created, Access will automatically put today's date into that
field.

The field doesn't have to show on any user forms.

Then you can simply run a query and find all records added after a certain
date (or a date range, etc).
 
Dave,

Unfortunately, the table did not contain such a field. But I still need to
determine which records were recently created so that I can extract them.

Thanks,

Ben
 
Unfortunately, the table did not contain such a field. But I still need to
determine which records were recently created so that I can extract them.
Click to expand...

Unfortunately, there is no way to do so. Access does not know or care when
records were created, and that information simply does not exist anywhere
within the database.
 
Your only hope is that you can identify the records some other way. Is there
some combination of fields that would let you identify records specifically.
In other words, some combination of fields that could be used as a primary key?

If so, you may be able to identify the "New" records in the old database using
an unmatched query linking the two tables.

Just link (temporarily) the old table into your new database and build an
unmatched query.

For example, the following will detect records in FAQ that are not in FAQ1. I
did have to concatenate a zero-length string onto the fLink field since it can
contain Null values and therefore would cause records to be returned that were
the same when both flink fields were null.

SELECT FAQ.fID, FAQ.fSubject, FAQ.fKeywords, FAQ.fLink
FROM FAQ LEFT JOIN FAQ1
ON FAQ.fLink & "" = FAQ1.fLink & ""
AND FAQ.fSubject = FAQ1.fSubject
WHERE FAQ1.fSubject Is Null

That query identified the records in FAQ that were not in FAQ1 based on the
two fields I specified.

Good Luck. This technique may not find all the records that you want but it
should help.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Ben said:
I would like to ask if there's a way in looking at the records in the
table
of the older mdb and determine if they were added recently. If they were
I
could then just extract them out and put them back in the newer mdb's
table.
Click to expand...


In the absence of a creation-date field, the only thing I can think of is if
the table has an autonumber field, and if that autonumber is sequential
(incremented), not random, and if nothing special is done to insert or
update specific values in that field (so it's just counting up from 1 and
never changed), then the more recent records will have higher autonumbers.
So if you can identify the first record that was mistakenly added, you ought
to be able to pull all records with the autonumber field >= that record's
autonumber.

Or, if you have a backup of the old database that was taken at the time it
was supposed to have been retired from use, you could link to it and use a
find-unmatched query between the table in the backup database and its
corresponding table in the current copy of the "old" database.
 
good idea, thanks for that.

Ben
--



John Spencer said:
Your only hope is that you can identify the records some other way. Is there
some combination of fields that would let you identify records specifically.
In other words, some combination of fields that could be used as a primary key?

If so, you may be able to identify the "New" records in the old database using
an unmatched query linking the two tables.

Just link (temporarily) the old table into your new database and build an
unmatched query.

For example, the following will detect records in FAQ that are not in FAQ1. I
did have to concatenate a zero-length string onto the fLink field since it can
contain Null values and therefore would cause records to be returned that were
the same when both flink fields were null.

SELECT FAQ.fID, FAQ.fSubject, FAQ.fKeywords, FAQ.fLink
FROM FAQ LEFT JOIN FAQ1
ON FAQ.fLink & "" = FAQ1.fLink & ""
AND FAQ.fSubject = FAQ1.fSubject
WHERE FAQ1.fSubject Is Null

That query identified the records in FAQ that were not in FAQ1 based on the
two fields I specified.

Good Luck. This technique may not find all the records that you want but it
should help.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
 
This is a good idea. Thanks for the suggestion.
I just inherited ths db and boy it was definitely snapped together in a
hurry with little forethought.

Ben
 
Thanks Dirk.

Ben

--



Dirk Goldgar said:
In the absence of a creation-date field, the only thing I can think of is if
the table has an autonumber field, and if that autonumber is sequential
(incremented), not random, and if nothing special is done to insert or
update specific values in that field (so it's just counting up from 1 and
never changed), then the more recent records will have higher autonumbers.
So if you can identify the first record that was mistakenly added, you ought
to be able to pull all records with the autonumber field >= that record's
autonumber.

Or, if you have a backup of the old database that was taken at the time it
was supposed to have been retired from use, you could link to it and use a
find-unmatched query between the table in the backup database and its
corresponding table in the current copy of the "old" database.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top