Hi Everyone,

I am trying to decide how to approach a design problem. The scenario
is that there is a data warehouse using Oracle that contains 'real-
time' data that is needed for a report. The source data in the
warehouse is messy to say the least, but with enough time wasted I
have managed to get a clean 'capture' of the data, and am currently
using a passthrough query to get to that data. There are approximately
600k rows of data, and it takes about 13.5 minutes to receive all that
from the passthrough. So far so good.

My design issue is that the desired report is needed with comparison
over time. This 'real-time' system only shows what the world 'looks
like' right now. My thinking to achieve a time comparitive based
report is to 'capture' the data from the passthrough query and dump it
into a separate MDB (ie/ generate a new one each time a capture is
done), and timestamp it.

I am wondering if anyone has done this and can let me know the pro's
and cons of this approach. Is there a better way to handle this? And
while we're at it is there a simple way to perform this capture
outside of tons of code? - I can achieve a capture but I am not sure
that my approach is a good one.

Any feedback or shared wisdom greatly appreciated :-)

Cheers

The Frog

On Mar 12, 1:37*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
instead of capturing / timestamping into an MDB, can you not do that
within Oracle
and report from that ?

if you are going to collect data into an MDB, why would you need 'tons
of code', isn't it a simple insert query from Oracle to an mdb, where
the MDB table's timestamp field defaults to the current date ?

Hi Roger,

The reason for the capture is that comparison over time needs to be
done, but of course the 'live' data will change, making the report
impossible, so the data needs to be saved at a point in time, hence
the capture.

My tons of code was based around using recordsets to receive the data
so that I had some form of error control and feedback over the
process. I have also done the make-table query approach (much faster)
but that method lacks any feedback, and for such a (relatively) long
process feedback for the user is a good idea (IMO).

I had forgotten about the timestamp field default value of now()
trick! Thankyou for the reminder. Simple things you sometimes
forget.......

After doing a little testing it looks like an individual data capture
will contain about 50Mb of data. Should be able to dump many captures
into a single MDB.

My thinking at the moment is to re-link the tables from the FE to user
designated BE and dump the data into that. My concern is I suppose
centered around providing sufficient feedback to the user during the
capture process. I am yet to find a 'minimalist' approach that gets
the job done. At the moment the best I can do is to run a passthrough
that tells me how many rows to expect (pretty quick), then use a
recordset and page through keeping count.......

Open to suggestions.

Cheers

The Frog

On Mar 12, 8:26*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
I assume you have a form with a command button to start the capture
you could have a couple of textboxes on the form, one to display the
step currently being processed

the other, driven by the timer event, to display the number of records
in the table(s) being built...

Hi Roger,

I do have a button that starts the process, and I can place a 'hamster
in a wheel' so to speak based on the forms timer event to show
activity is taking place. I know how many records need to be brought
back (separate count query), but using an SELECT INTO statement for
the table, which is by far the quickest method gives me no feedback on
how far through it is during the process (there is only one table /
view to capture) - hence using the recordset.

I was just hoping that someone might know a trick with access to show
that kind of data without using a recordset. It looks like I might
just have to stay with the existing method so far. It does work, just
takes a long time......

Thanks for the help, I appreciate your time and interest.

Cheers

The Frog

The Frog wrote:
I'm not fully understanding the thread but I'll toss some mud at the
wall and maybe something sticks.

If you had a count of 50K records, you could divide that by 10 to get
percentages complete. Then select the TOP 5000 records. Get the last
record value, and select the next TOP 5000 greater than the last record.
When the counter is 10, select all greater than the last record as you
might have 5001 records. Then use Syscmd to show a progress meter.

On Mar 15, 2:09*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
so you have a button that does one 'select into' statement to do all
the work
what you can do is open another form to be the hamster, with a timer
event, to display the current status
and then close the hamster form when the select into is done

Thanks for the ideas guys, I appreciate it.

What I am currently doing to try and solve the issue is to open and
ADO connection to the Oracle server, then follow that up with an ADO
recordset, using a client side cursor, and forward only, lock read
only. I then create a collection object, and use the getrows function
to return me an array of 1000 records, and cycle through the entire
recordset till eof. Surprisingly this 'capture' takes only just over a
minute to actually run - I have no idea why it is so much faster than
running the query direct or even using it in a maketable type
operation, but the time difference is huge. I am guessing it is
probably a quirk of our network, but I will ue it for now.

So now I have the known number of records, as well as a 'capture' that
collects the data in an orderly fashion I can show progress with. The
next step is to pump the collection of arrays into (user selected) a
new database (mdb) or append the data to an existing one. I do this
with another ADO recordset and batch updates. Again I can now show
progress to the user. I am also using a chunk of code that takes the
ado recordset and feeds it to a routine to build via ADOX a new table
and if needed database. I am hoping that if I need to do a different
capture process in the future from this data warehouse I can re-use
the code. Still not 100% happy with the ADOX table building routine
but it is stable for now. I will need to spend some more time on it to
make it robust enough for the general case rather than just my
specific case for now.

If anyone wants the code I am happy to post it. Bare in mind that it
is still not production level yet, but does give you the
functionality.

It is curious the speed with which the different operations actually
run. I would have expected that an Access Passthrough query being used
as the basis of a Select Into would have been far quicker than an ADO
approach, epsecially since the ADO 'Source' is using exactly the same
query as the Passthrough. One of the infrastructure guys here
suggested that it could be an issue between Access (2003) and the
AntiVirus/Firewall/Packet Filter that the machine has installed
(corporate standard issue). I am unable to prove this, but watching
the network throughput of the two approaches I am getting a lot more
bandwidth utilisation with ADO (and hence throughput) than with Access
and a Passthrough. Odd, but there it is. At least this way I have a
reliable progress display, and can also squeeze in some 'cleanup' code
if I need to change data types or similar.

Does anyone have any suggestions on optimizing this further? It is
running pretty well so far but I am open to suggestions :-) I would
also love to hear suggestions as to why the speed differences in the
approaches. And also if anyone has a robust 'production level' code
for taking an ado recordset and building a table from it.

As always a big thankyou for your help

Cheers

The Frog