In a message dated 96-02-17 20:23:59 EST, [log in to unmask] (Steve Grose)
writes:
 
>
>I have a question that I'm hopeing someone else has faced.
>
>I have 56 Image/SQL databases in different HP accounts. All have the same
>schema. I would like to
>use Microsoft Access to do some reporting but I'm not crazy about the idea
of
>creating 56 Access
>datbases then attaching then to the Image databases.
>
>Is there a way to create one Access database then use it for all reporting?
>
>
 
 
From a reporting point of view, I would do the following:
(Assumption 1: you have a USER.ACCOUNT which has access to all the
appropriate accounts and/or databases.   Assumption 2: All the databases have
the same name.)
 
In an account which has the access mentioned above, I would create an
ALLBASE/SQL DBE.  I would create it through ISQL, as it is going to require
quite a bit of space for the table definitions coming up.
 
Next, I would start attaching the various databases to the newly created DBE
specifying an OWNER, thusly:
 
SET SQLDBE MAINDBE
SET TURBODB DBSAME.DATA.ACCOUNT1
ATTACH WITH OWNER=ACCOUNT1
SET TURBODB DBSAME.DATA.ACCOUNT2
ATTACH WITH OWNER=ACCOUNT2
SET TURBODB DBSAME.DATA.ACCOUNT3
ATTACH WITH OWNER=ACCOUNT3
.
.
SET TURBODB DBSAME.DATA.ACCNT56
ATTACH WITH OWNER=ACCNT56
 
Now you can access all the tables from all the IMAGE databases by simply
specifying the owner's name and the table name:
 
ACCOUNT9.TABLE2   specifies table2 from the database in the account ACCOUNT9.
 
I would then create an MS Access table on the client and start attaching the
required tables from the various IMAGE/SQL objects, whilst keeping the same
nomenclature going.
 
A big problem is going to be with the definition of the rows in the various
table.  You will need to do some fine tuning to ensure that MS Access gets
exactly what you think it is going to get.  This problem comes into being
with zoned decimals and packed fields and compound items.  It can be
exacerbated with renames of columns and so on.  You basically want to do all
this work at the DBE level, for all the tables, so that MS Access would not
have to be fine tuned at all.
 
To assist you with this tedious, yet exacting job, I enjoin you to look at
our product DBTune/SQL which will make life a lot easier for you.  Call us at
(800) 323-8863 or (713) 288-7438 for more details.
 
Kind regards,
 
Denys. . .
http://www.hicomp.com/hicomp