I wrote triggers in PostgreSQL to update a new table that I created to
indicate when a local table has changed (insert/update/delete). This new
table contains a row for each local table and the date/time this table was
last updated. I also stored this table locally in Access. I then wrote a
procedure in Access97 that would compare the local and server copy of this
table and look for any table that changed. For each changed table, the
Access procedure would link to the server table and copy the data from the
server table to the local table. I then called this Access procedure every
time a user opened up the Access application and whenever a user opened any
significant form. You could do it for all forms, but I had many forms in
my application so I chose to implement this only in the most important
ones. This was not necessarily easy to do but it worked great.
-----Original Message-----
From: DI Hasenohrl [SMTP:i.hasenoehrl@aon.at]
Sent: Wednesday, July 04, 2001 1:32 AM
To: mdavis@sevainc.com
Subject: Re: [INTERFACES] RE: Question
<< File: ATT00014.htm >> Hello Michael,
1. Yes 2. Using ODBC. 3. In some cases, yes. In particular, if you use a lot of combo boxes on your Access forms and
queries,they will be slow. I took any tables that were accessed a lot and updated a little and copied them into
Access. I wrote a script that would refresh the local Access copy of these tables whenever the server copy of these
tableswere updated. This was not an easy thing to do but it really helped the performance of many of my
Access forms.
I hope this helps, Michael
It seems, that I have the some problem of performance now, you had some
time ago. So I made the same decision like you and work with local Access
tables and Server tables. In order to synchronise the server tables with
the Access tables, I asked the odbc-list, if there is a possibility, to
send updates or inserts from postgresql-tables to local MsAccess-tables.
I got the following answer: Don't think you can do this via ODBC. Have a "change" table and update a timestamp in it
whenevera target
table is updated. Then clients can poll once a minute or whatever and see if
their local timestamp is older than the one in the change table.
Now, I'm developing this solution and have some problems, how to manage
the error-handling, realize transactions and so on.... Please can you show me your way to synchronise server tables and
Access
tables
Many thanks in advance Irina