Tierd structure advice

[Doug Bell]

Hi I am putting together a functional specification in preparation for
writing a VB Dot Net upgrade an existing Access (VBA) and VB system.

It is currently a tiered system with an IBM AS400 providing a DB2 Database.
There are 5 or 6 of sites and each site has 2 to 6 PCs (involved in this
system). One PC on each site acts as an application server and sub database
server, ie it is an MS Access Application that is scheduled to run approx
every hour and when it runs, it downloads several files from the AS400 and
deletes old records, updates existing records and appends new records into
and Access MDB database file. It then downloads certain data to Access files
on the other PCs on the site. Each PC must be capable of operating even when
the local site server is unavailable so each PC has its own database.

The site server is required because when the PCs process records, a copy of
the record is saved in the Site server database and some transactions
require access to previously saved records (possibly from a different PC).
Also some of the processing to and from the AS400 is not fast and rather
than letting every PC call for a download of data each hour we are using the
site servers to disseminate the data.

OK you can see it is fairly complex and it is even more so at the PC level.
We are hoping to simplify some of this by replacing the access databases at
PC level with DataSets and XML files.

My question refers to structure at the server level. Initially I thought
that I should retain the Access MDB file at the site server to accomadate
the multiple PCs writing records to it and using the Jet Engine to take care
of record locking (there should not be much chance of record being changed
by more than one PC).

This morning I was thinking that possibly this could all be achieved with
DataSets and XML? Could the AS400 downloaded data be held in an XML file and
when new records are posted the XML file is re-written? I know it could but
how do I handle multiple PCs that may try to write records simultaneously?
Am I better sticking with Access MDB at this level?

Thanks for any advice.

Doug

The other

 

[Cor Ligthert]

Doug,

This looks like a disconnected PDA application.

For that are often used datasets on every PDA.
(That is exactly one of the reasons the dataset is designed for)

Important for the complexity is where is taken the action to delete
datarows.

A delete from the central database is normally not registered and therefore
you have to do a complete refresh from your dataset everytime.

I read in your message that you ask if a dataset is updatatable.
Yes in memory, however not on disk or whatever media.
There it has to be written completly new everytime again.

Just some thoughts (not an advice) I had reading your message.

Cor

 

[Andy O'Neill]

OK you can see it is fairly complex and it is even more so at the PC
level.
We are hoping to simplify some of this by replacing the access databases
at
PC level with DataSets and XML files.

This morning I was thinking that possibly this could all be achieved with
DataSets and XML?

Seems to me that the access database holds data and is used for some
processing.
The datasets and xml would be used to....
Hold some data and carry out some processing.
Ummmm.... so that'd be better because??

On the face of it, you're talking of swopping an efficient dbms (Access) for
a less efficient file system.
So I would think that there's at least the potential this xml and dataset
solution will offer no benefit and some negatives.

 

[Doug Bell]

Thanks for your comments Andy.

We have found loading data from DB2 to Access (Server) quite slow accross
the network, ie Delete Old records, Update Existing and Append new.
Performance was improved when we used an intermediatory temporary table to
dump all current records into and then use it to do the Delete/Update/Append
and we also found that using a Make Table Query faster than Appending to an
empty Table but one problem with Access is that it doesn't clean up very
efficiently so we started using compact on close. This causes a problem
about 1 in 1000 closes in that it can not rename the compacted DB and then
requires some manual support.

Streaming to a DataSet seems faster so I will keep the Access MDB at Server
level and fill it by streaming to a DataSet and then (in the background)
itterating through it to delete old records, update current and append new
records to the Access Tables.

Doug