Data access using ODBC datasources

  • Thread starter Thread starter PO
  • Start date Start date
P

PO

Hi

I'm using VBA in Excel 2000 to generate reports. Each report accesses data
in various databases (currently 4 different DB) using ODBC datasources. At
least 50 persons at my company use the reports. On each PC-client I have set
up the ODBC datasources.

There are two problems:
1. Each time I make an excel-report which needs to access a new database,
all of the client machines have to be updated with new datasources. Or each
time I get a new user his or her client machine needs to have the ODBC
sources set up.

2. When accessing Oracle databases each client also needs to have Oracle
Client installed.

My questions:
1. Is it possible to use a ODBC datasource located on a server to access the
data, that is, can I in VBA code tell Excel to look for the data source on a
server?

2. and, if so, do the clients still need Oracle Client or can all the
clients use a server version?

Regards
PO
 
I use ADO.
My biggest problem are the number of client machines involved. Typically
they just have Office Professional installed (no MDAC or Oracle Client).

Preferably I would like to install:

1. MDAC
2. Oracle Client (I also have a server version)
3. ODBC datasource

on a server so I don't have to install the drivers on all the client
machines. The excel-reports I send to the users "know" which server to
contact to access the data through the servers datasources.

I don't know if this is possible and perhaps there are other solutions(?).

I want to use Excel as a reporting tool but I don't want to have to update
all client machines each time I get a new user. Perhaps there is a way to
make use of our intranet somehow to get the data to the client machine?

Regards
PO
 
PO said:
I use ADO.
My biggest problem are the number of client machines involved. Typically
they just have Office Professional installed (no MDAC or Oracle Client).

Preferably I would like to install:

1. MDAC
2. Oracle Client (I also have a server version)
3. ODBC datasource

on a server so I don't have to install the drivers on all the client
machines. The excel-reports I send to the users "know" which server to
contact to access the data through the servers datasources.

I don't know if this is possible and perhaps there are other solutions(?).

I want to use Excel as a reporting tool but I don't want to have to update
all client machines each time I get a new user. Perhaps there is a way to
make use of our intranet somehow to get the data to the client machine?
Click to expand...

If you want the client machines to use ADO to connect to the
databases, they need to have MDAC and appropriate drivers/providers
locally (I think there was a recent post that suggested odbc may be
used for machines without the Oracle client). Alternatives include
'pushing' data to Excel clients using server side processes or
'pulling' data into Excel using other technologies e.g. Office SOAP
toolkit using a web service (however, a client without MDAC is
unlikely to be running OfficeXP or above <g>).

Jamie.

--
 
Back
Top