Comparing records in 2 databases

  • Thread starter Thread starter JA
  • Start date Start date
J

JA

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
key?

OR, does anyone know of a fairly inexpensive program that will do this? For
Access 2k.

Thanks!
 
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
=Now()
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

SELECT fieldlist
FROM tablename
IN anotherdatabase.mdb
WHERE conditions
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
currentdb.tabledefs.refresh

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.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#


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top