Change Tracking an Access database 
Author Message
 Change Tracking an Access database

Hi,

Sorry for cross posting but I did not get a response in the other group. And
this is kind of urgent.

I have got a simple task at hand - and I know it should not be very hard.
But for some reason I cannot think of an appropriate way to do this.
Please help me out.

I have a small access 2.0 database sitting in Seattle. Its got about 60,000
records in it which are growing at the rate of 500 records a day. Besides
addition of 500 records some 250 records are also updated everyday. My Job
is to set up a SQL Server database at New York and as soon as any insert ot
update happens in the Access database in Seattle I have to send those
records to New York SQL Server. The protocol of choice to transfer the data
is HTTPS and I think I will be using SOAP calls to send the data.
My main problem is how to track changes on the Access database. I can
schedule a process which might look for updates records after every 5
minutes or so, but I am wondering how and what am I going to compare the
recods in Access database to see whether they have been updated or added
since last scheduled SOAP call. I may have added a TimeStamp column and
tracked the changes that way - but unfortunately I cannot modify the Access
database schema. Any help shall be greatly appreciated.

Thanks
Manuj



Mon, 10 Nov 2003 23:24:36 GMT  
 Change Tracking an Access database
Hi,

It doesn't look as simple task to me. If you can't modify the schema then
the only way I see is create a copy of a whole database and then compare
every record in code. Hope tables have primary keys.

Maybe you can modify Seattle app?
--
Regards,
Alexander Shirshov, MCSD


Quote:
> Hi,

> Sorry for cross posting but I did not get a response in the other group.
And
> this is kind of urgent.

> I have got a simple task at hand - and I know it should not be very hard.
> But for some reason I cannot think of an appropriate way to do this.
> Please help me out.

> I have a small access 2.0 database sitting in Seattle. Its got about
60,000
> records in it which are growing at the rate of 500 records a day. Besides
> addition of 500 records some 250 records are also updated everyday. My Job
> is to set up a SQL Server database at New York and as soon as any insert
ot
> update happens in the Access database in Seattle I have to send those
> records to New York SQL Server. The protocol of choice to transfer the
data
> is HTTPS and I think I will be using SOAP calls to send the data.
> My main problem is how to track changes on the Access database. I can
> schedule a process which might look for updates records after every 5
> minutes or so, but I am wondering how and what am I going to compare the
> recods in Access database to see whether they have been updated or added
> since last scheduled SOAP call. I may have added a TimeStamp column and
> tracked the changes that way - but unfortunately I cannot modify the
Access
> database schema. Any help shall be greatly appreciated.

> Thanks
> Manuj



Mon, 10 Nov 2003 23:46:48 GMT  
 Change Tracking an Access database
Hi,

When I said easy - I meant easy for you gurus out there.
Anyway, I cannot modify the application in Seattle either - not the database
schema.
The way we are doing now is to make a copy of Access database and FTP it
over to NY. But the database is becoming bigger now and it takes a long time
to FTP the database and its affecting the performance of the app in Seattle.
Any help shall be greatly appreciated.

Thanks
Manuj


Quote:
> Hi,

> It doesn't look as simple task to me. If you can't modify the schema then
> the only way I see is create a copy of a whole database and then compare
> every record in code. Hope tables have primary keys.

> Maybe you can modify Seattle app?
> --
> Regards,
> Alexander Shirshov, MCSD



> > Hi,

> > Sorry for cross posting but I did not get a response in the other group.
> And
> > this is kind of urgent.

> > I have got a simple task at hand - and I know it should not be very
hard.
> > But for some reason I cannot think of an appropriate way to do this.
> > Please help me out.

> > I have a small access 2.0 database sitting in Seattle. Its got about
> 60,000
> > records in it which are growing at the rate of 500 records a day.
Besides
> > addition of 500 records some 250 records are also updated everyday. My
Job
> > is to set up a SQL Server database at New York and as soon as any insert
> ot
> > update happens in the Access database in Seattle I have to send those
> > records to New York SQL Server. The protocol of choice to transfer the
> data
> > is HTTPS and I think I will be using SOAP calls to send the data.
> > My main problem is how to track changes on the Access database. I can
> > schedule a process which might look for updates records after every 5
> > minutes or so, but I am wondering how and what am I going to compare the
> > recods in Access database to see whether they have been updated or added
> > since last scheduled SOAP call. I may have added a TimeStamp column and
> > tracked the changes that way - but unfortunately I cannot modify the
> Access
> > database schema. Any help shall be greatly appreciated.

> > Thanks
> > Manuj



Tue, 11 Nov 2003 00:02:18 GMT  
 Change Tracking an Access database
Well, here is my scenario for a database with one table called Orders.

1. Make a copy of a database. Let's call it ChangeTrack.
2. Attach Orders table from working db to ChangeTrack db with an alias
WorkOrders.
3. Add a boolean field 'Processed' to Orders table of ChangeTrack db.
4. This SQL compares values of two fields in two tables and if they're
changed then copies new values to Orders table and resets Processed flag:
UPDATE Orders INNER JOIN WorkOrders ON Orders.OrderID = WorkOrders.OrderID
 SET
Orders.OrderDate = WorkOrders.OrderDate,
Orders.CustomerID = WorkOrders.CustomerID
Processed = FALSE
 WHERE
Orders.CustomerID <> WorkOrders.CustomerID OR
Orders.OrderDate<>OrigOrders.OrderDate
5. This SQL adds new records to Orders table :
INSERT INTO Orders SELECT WorkOrders.* FROM WorkOrders LEFT JOIN Orders ON
Orders.OrderID = WorkOrders.OrderID WHERE  Orders.OrderID Is Null
6. Retrieve records with Processed = False, pack them in SOAP and send to
NYC, then set Processed = True.
7. Rinse, repeat.

Some notes:
#4 can put a heavy load on Jet. Probably it's better to retrieve a
disconnected recordset with a whole table and loop through it in VB code.
If you want to distinguish between added and updated records then add IsNew
field and set it's value in #5.
Processed field can hold not just boolean flag but info with date/time of
parcel sent to NYC.
--
HTH,
Alexander Shirshov, MCSD


Quote:
> Hi,

> When I said easy - I meant easy for you gurus out there.
> Anyway, I cannot modify the application in Seattle either - not the
database
> schema.
> The way we are doing now is to make a copy of Access database and FTP it
> over to NY. But the database is becoming bigger now and it takes a long
time
> to FTP the database and its affecting the performance of the app in
Seattle.
> Any help shall be greatly appreciated.

> Thanks
> Manuj



> > Hi,

> > It doesn't look as simple task to me. If you can't modify the schema
then
> > the only way I see is create a copy of a whole database and then compare
> > every record in code. Hope tables have primary keys.

> > Maybe you can modify Seattle app?
> > --
> > Regards,
> > Alexander Shirshov, MCSD



> > > Hi,

> > > Sorry for cross posting but I did not get a response in the other
group.
> > And
> > > this is kind of urgent.

> > > I have got a simple task at hand - and I know it should not be very
> hard.
> > > But for some reason I cannot think of an appropriate way to do this.
> > > Please help me out.

> > > I have a small access 2.0 database sitting in Seattle. Its got about
> > 60,000
> > > records in it which are growing at the rate of 500 records a day.
> Besides
> > > addition of 500 records some 250 records are also updated everyday. My
> Job
> > > is to set up a SQL Server database at New York and as soon as any
insert
> > ot
> > > update happens in the Access database in Seattle I have to send those
> > > records to New York SQL Server. The protocol of choice to transfer the
> > data
> > > is HTTPS and I think I will be using SOAP calls to send the data.
> > > My main problem is how to track changes on the Access database. I can
> > > schedule a process which might look for updates records after every 5
> > > minutes or so, but I am wondering how and what am I going to compare
the
> > > recods in Access database to see whether they have been updated or
added
> > > since last scheduled SOAP call. I may have added a TimeStamp column
and
> > > tracked the changes that way - but unfortunately I cannot modify the
> > Access
> > > database schema. Any help shall be greatly appreciated.

> > > Thanks
> > > Manuj



Tue, 11 Nov 2003 03:18:47 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. JCN - KEEPING TRACKS OF ALL CHANGES TO RECORDS IN AN ACCESS DATABASE

2. Keeping track of Database changes!?

3. changing databases in odbc doesnt change database in report

4. Track changes in db

5. Tracking user changes to records

6. Code needs amended to track data changes

7. Help! --Tracking Changes to Records

8. Tracking record changes

9. Excel and VBA to set up tracking changes

10. Track Changes to a Field

11. Word 2000, VBA and "Track Changes"

12. Field for tracking changes to a task ?

 

 
Powered by phpBB® Forum Software