Comparing records in 2 databases

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! 


0
JA
3/19/2007 10:30:37 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1173 Views

Similar Articles

[PageSpeed] 50

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
   *



JA wrote:
> 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! 
> 
> 
0
strive4peace
3/20/2007 5:08:02 AM
Reply:

Similar Artilces: