Tracking date record was created or updated, SQL Syntax
It is a good idea to add these 2 fields to all your tables (except
lookups). Let them be the last 2 fields.
DateCreated, date, DefaultValue = Now()
DateModified, date � set on the form BeforeUpdate event
the best way to use the DateCreated field is to set a default value of
in the table design.
For DateModified, make sure it is on your form (I put it in the form
footer and LOCK it. Then, use the Form BeforeUpdate event to set the value
me.DateModified = now()
once these fields are there, make sure you use the form to change values
so that DateModified gets changed.
Then, you can use these fields to add or update whatever is necessary
based on when you last did it. For changing a table in another
database, just link to it
~~~~~~~~~~~ SELECT ~~~~~~~~~~~
BASIC SQL SYNTAX
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;
~~~~~~~~~~~ APPEND ~~~~~~~~~~~
An Append Query is a select query preceded by
INSERT INTO Tablename (field1, field2, etc )
'~~~~~~~~~~~~~~~~~ for instance (and this does not use a table as it
supplies actual values)
dim strSQL as string
strSQL = "INSERT INTO Tablename " _
& " (TextField, NumField, DateField ) " _
& " SELECT '" & strValue & "', " _
& numValue & ", " _
& "#" & datValue & "#" _
currentdb.execute strSQL, dbFailOnError
~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used
UPDATE table1 INNER JOIN table2 ON table1.keyfield =
Then identifies what to change
SET table1.fieldtochange = expression
Then, if you have criteria...
WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#
(: have an awesome day :)
Remote Programming and Training
strive4peace2006 at yahoo.com
> I need to update an access database each day, with stock and price info, and
> then update it online.
> Is there a way to compare the before and after tables, and make a new table
> with only the changed records (only the changed fields!) plus the primary
> OR, does anyone know of a fairly inexpensive program that will do this? For
> Access 2k.