Re: Creating Access DB

From: PO (po)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 14:56:08 +0200

Frank,

My english is not so good but I'll try to explain the best I can:

1. Since I get the data from 2 db's I have to use 2 separate
connections/recordsets (or is it possible to query both db's in one SELECT
joining the two tables? How?). And as far as I know there is no way creating
a third recordset based on a join between the other two recordsets.

I have tried to use an array as an intermediary to piece the two recordsets
together, but since there is a great amount of data involved, I quickly get
out of memory.

2. The Access db. About 30 persons use this xl-report and it is sent out by
mail. Usually they just place the xlt-file on their desktop and double-click
the icon when they want to run the report. I have previously used Access as
an intermediary for creating reports. Every time I made changes to the
report, an extra column of data for instance, I had to resend both the xlt
and the access db. Some of the users replaced the xlt-file but not de Access
db, and then, of course the application failed. Therefore, just to keep
things simple, I just want to send out a xlt-file, any changes to the report
(and the db) is done in the xlt-files code.

3. Linking data. The problem is that some of the rows have to go into
separate sheets. I have a procedure which loops through the records, and be
a certain logic, rows are moved to other sheets, which in turn makes the
links useless.

I'd still like to try creating an access db from within the VBE. If it turns
out to be to time-consuming I'll try something else.

Regards
PO

"Frank Stone" <anonymous@discussions.microsoft.com> wrote in message
news:c1c601c489c9$8c60c340$a401280a@phx.gbl...
> i keep reading what you are trying to do and can see no
> real advantage to doing it that way. If you are going to
> do a record set, you can get that direct from the oracle
> db and sql db (access not needed).
> how are you planning to get the data in the access db for
> temporary storage?
> why temporary? linked, it would give you permanent on
> demand real time data.
> once created, are you going to delete the access db?
> sounds to me like you fixing to write a whole lot of code
> that is really not needed.
> with what you have stated and with the features in access
> and excel, you can point and click through the whole
> process.
> >-----Original Message-----
> >Hi!
> >
> >I'm creating a report in Excel. The report is derived
> from several thousands
> >rows of data (typically 40-50.000). The data comes from 2
> different
> >databases (Oracle and SQL-server).
> >
> >I would like to set up an Access db for temporary data
> storage. With the
> >data in the same db I can create a recordset which joins
> the 2 tables.
> >Ideally the db is created (with two tables) every time
> the Excel-report is
> >executed, and thereafter removed (I could of course make
> it permanent but
> >the users have 15-20 other reports (all in Excel) and I
> don't want to
> >trouble them with an Access DB). I think that creating
> the DB from code
> >within Excel doesn't take to much extra time(?).
> >
> >I need some example code for setting up the db with 2
> tables. I don't know
> >if I need to set up the tables, perhaps I could just
> append the Excel
> >recordset, containing the data, directly to the db??
> >
> >I run Excel 2000 and Access 2000.
> >
> >TIA
> >PO
> >
> >
> >.
> >