Data access using ODBC datasources
 

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

Data access using ODBC datasources
 

you can use ADO instead of DAO...
also you can define so called DSN-less connection strings.

see
http://www.able-consulting.com/ADO_Conn.htm
http://www.erlandsendata.no/english/...php?t=envbadac

all these connectstrings can be used directly
in excel queries..

it can be done... but you'll spend some time studying.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"PO" <po wrote:

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

Data access using ODBC datasources
 

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

"keepITcool" wrote in message
...

you can use ADO instead of DAO...
also you can define so called DSN-less connection strings.

see
http://www.able-consulting.com/ADO_Conn.htm
http://www.erlandsendata.no/english/...php?t=envbadac

all these connectstrings can be used directly
in excel queries..

it can be done... but you'll spend some time studying.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"PO" <po wrote:

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

Data access using ODBC datasources
 

"PO" <po wrote ...

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?

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.

--