How to minimize data transfer between local and remote database?

[juvi]

Hello,

I have got database which imports data from csv every night. So all data are
up to date next day when the clients are "downloading" the data to their
local mdb through sql query.

But their are over 100.000 records and this takes a while to download. I
found this in the d.g.:

// SELECT col1, col2, ..., colN, MIN(version) AS version
// FROM (SELECT col1, col2, ..., colN, 'Yesterday' AS version
// FROM YesterdaysVersion
// UNION ALL
// SELECT col1, col2, ..., colN, 'Today' AS version
// FROM YesterdaysVersion)
// GROUP BY col1, col2, ..., colN
// WHERE COUNT(*) = 1

How could I use this to look for changes made in today's version and how can
I tell my client mdbs only to "sync/update" the changed fields/columns?? I
have no idea how I could manage this now and it would be very important for
me as soon as possible - maybe someone can help me

thx
juvi

 

[Alex Dybenko]

Hi,
you can add a new field to store current date/time to your table where you
import csv, then for download you can prepare a table with records added
since last download

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[juvi]

hello,

thanks for your reply. ok this would minimize the data transfer - but how
can I check or compare field updates. To check for new records is I think
easier than for checking field for field for update. How could I compare all
fields for changes?

thx

 

[Alex Dybenko]

You can store somewhere a date of last time you prepared data from transfer,
and next time you prepare it - you just leave there records with time stamp
later then this date

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[juvi]

ok, I understand. The time stamp is an indicator which data to download for
clients. But I am still not sure how to compare these to tables (all columns)
for inconsistence and how I can mark the correct records in the syncTable
with time stamp.

 

[juvi]

I have found a solution for getting differences between two tables. This is
the query for it which I use to write the differences into tbl_diff:

SELECT ID, COL1, COL2, COL3 ... INTO tbl_diff
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID

This works really good. But it would be great to have only the changed
records into the tbl_diff. How can I bypass the records from table A if I now
that only changes in table B can happen. So I would only need the records
from table B in tbl_diff.

How could I manage this?

thx
juvi

 

[Alex Dybenko]

Well, again, my idea was a following.
say you have table1 where you download CSV. you add then new field DateAdd,
defaulted to Now()
you also add one more table tblSetup with field DatePrepared


docmd.runsql "SELECT ID, COL1, COL2, COL3 ... INTO tbl_diff
FROM Table1
Where DateAdd > dfirst('DatePrepared','tblSetup')"
'now set 'DatePrepared'
docmd.runsql "update tblSetup Set DatePrepared=Now()"

HTH

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com