Connecting to Oracle with Access front end via VBA

#1

There are some Oracle tables from a production database that my app
will link to after some enhancements are made. The enhancement
consist of needing to link to two Oracles tables to run a make-table
quiery (which will create another local Access table)

I have heard that to connect to an Oracle table totally via VBA code,
that it is indeed not possible, because the end user's workstation
must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Can Oracle tables be linked to an Access DB (with none
of the three above present) long enough for an extract, then
disconnected? I have experienced many headaches with older apps with
an Oracle back ends, because I learn the user's workstation was re-
imaged and they therefore lost the 3 items I mentioned above.
Reconfiguring ODBC connection services on the client is cumbersome.

If there is a simple solution via VBA that would be a life saver.

Thanks.
-------
RLN

#2

"RLN" <rlntemp-gng (AT) yahoo (DOT) com> wrote

Quote:

There are some Oracle tables from a production database that my app
will link to after some enhancements are made. The enhancement
consist of needing to link to two Oracles tables to run a make-table
quiery (which will create another local Access table)

I have heard that to connect to an Oracle table totally via VBA code,
that it is indeed not possible, because the end user's workstation
must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Can Oracle tables be linked to an Access DB (with none
of the three above present) long enough for an extract, then
disconnected? I have experienced many headaches with older apps with
an Oracle back ends, because I learn the user's workstation was re-
imaged and they therefore lost the 3 items I mentioned above.
Reconfiguring ODBC connection services on the client is cumbersome.

If there is a simple solution via VBA that would be a life saver.

Thanks.
-------
RLN
You need the Oracle client installed on the client to acess Oracle. You

should look at using Apex (application express) by Oracle instead of Access.
Then you don't need to install anything on the client except a web browser.
(which they probably already have) Very primitive to suck down a table to
work on it locally; very 1990ish.
Jim

#3

"RLN" <rlntemp-gng (AT) yahoo (DOT) com> wrote

Quote:

There are some Oracle tables from a production database that my app
will link to after some enhancements are made. The enhancement
consist of needing to link to two Oracles tables to run a make-table
quiery (which will create another local Access table)

I have heard that to connect to an Oracle table totally via VBA code,
that it is indeed not possible, because the end user's workstation
must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Can Oracle tables be linked to an Access DB (with none
of the three above present) long enough for an extract, then
disconnected? I have experienced many headaches with older apps with
an Oracle back ends, because I learn the user's workstation was re-
imaged and they therefore lost the 3 items I mentioned above.
Reconfiguring ODBC connection services on the client is cumbersome.

If there is a simple solution via VBA that would be a life saver.

Thanks.
-------
RLN
You need the Oracle client installed on the client to acess Oracle. You

should look at using Apex (application express) by Oracle instead of Access.
Then you don't need to install anything on the client except a web browser.
(which they probably already have) Very primitive to suck down a table to
work on it locally; very 1990ish.
Jim

#4

"RLN" <rlntemp-gng (AT) yahoo (DOT) com> wrote

Quote:

There are some Oracle tables from a production database that my app
will link to after some enhancements are made. The enhancement
consist of needing to link to two Oracles tables to run a make-table
quiery (which will create another local Access table)

I have heard that to connect to an Oracle table totally via VBA code,
that it is indeed not possible, because the end user's workstation
must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Can Oracle tables be linked to an Access DB (with none
of the three above present) long enough for an extract, then
disconnected? I have experienced many headaches with older apps with
an Oracle back ends, because I learn the user's workstation was re-
imaged and they therefore lost the 3 items I mentioned above.
Reconfiguring ODBC connection services on the client is cumbersome.

If there is a simple solution via VBA that would be a life saver.

Thanks.
-------
RLN
You need the Oracle client installed on the client to acess Oracle. You

should look at using Apex (application express) by Oracle instead of Access.
Then you don't need to install anything on the client except a web browser.
(which they probably already have) Very primitive to suck down a table to
work on it locally; very 1990ish.
Jim

#5

"RLN" <rlntemp-gng (AT) yahoo (DOT) com> wrote

Quote:

There are some Oracle tables from a production database that my app
will link to after some enhancements are made. The enhancement
consist of needing to link to two Oracles tables to run a make-table
quiery (which will create another local Access table)

I have heard that to connect to an Oracle table totally via VBA code,
that it is indeed not possible, because the end user's workstation
must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Can Oracle tables be linked to an Access DB (with none
of the three above present) long enough for an extract, then
disconnected? I have experienced many headaches with older apps with
an Oracle back ends, because I learn the user's workstation was re-
imaged and they therefore lost the 3 items I mentioned above.
Reconfiguring ODBC connection services on the client is cumbersome.

If there is a simple solution via VBA that would be a life saver.

Thanks.
-------
RLN
You need the Oracle client installed on the client to acess Oracle. You

should look at using Apex (application express) by Oracle instead of Access.
Then you don't need to install anything on the client except a web browser.
(which they probably already have) Very primitive to suck down a table to
work on it locally; very 1990ish.
Jim

#6

On Fri, 22 Feb 2008, rlntemp-gng (AT) yahoo (DOT) com wrote:

Quote:

There are some Oracle tables from a production database that my app
will link to after some enhancements are made. The enhancement
consist of needing to link to two Oracles tables to run a make-table
quiery (which will create another local Access table)

I have heard that to connect to an Oracle table totally via VBA code,
that it is indeed not possible, because the end user's workstation
must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Can Oracle tables be linked to an Access DB (with none
of the three above present)

One can link to Oracle without any of the above, but VBA is the one
causing your percieved limitation and needs them.

--
Galen Boyer

#7

On Fri, 22 Feb 2008, rlntemp-gng (AT) yahoo (DOT) com wrote:

Quote:

There are some Oracle tables from a production database that my app
will link to after some enhancements are made. The enhancement
consist of needing to link to two Oracles tables to run a make-table
quiery (which will create another local Access table)

I have heard that to connect to an Oracle table totally via VBA code,
that it is indeed not possible, because the end user's workstation
must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Can Oracle tables be linked to an Access DB (with none
of the three above present)

One can link to Oracle without any of the above, but VBA is the one
causing your percieved limitation and needs them.

--
Galen Boyer

#8

On Fri, 22 Feb 2008, rlntemp-gng (AT) yahoo (DOT) com wrote:

Quote:

There are some Oracle tables from a production database that my app
will link to after some enhancements are made. The enhancement
consist of needing to link to two Oracles tables to run a make-table
quiery (which will create another local Access table)

I have heard that to connect to an Oracle table totally via VBA code,
that it is indeed not possible, because the end user's workstation
must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Can Oracle tables be linked to an Access DB (with none
of the three above present)

One can link to Oracle without any of the above, but VBA is the one
causing your percieved limitation and needs them.

--
Galen Boyer

#9

On Fri, 22 Feb 2008, rlntemp-gng (AT) yahoo (DOT) com wrote:

Quote:

There are some Oracle tables from a production database that my app
will link to after some enhancements are made. The enhancement
consist of needing to link to two Oracles tables to run a make-table
quiery (which will create another local Access table)

I have heard that to connect to an Oracle table totally via VBA code,
that it is indeed not possible, because the end user's workstation
must have 3 things:
1) the Oracle client installed
2) must have a TNSNAMES.ORA file with a reference to the backend db.
3) an ODBC data source must be set up separately on the workstation.

Is this true? Can Oracle tables be linked to an Access DB (with none
of the three above present)

One can link to Oracle without any of the above, but VBA is the one
causing your percieved limitation and needs them.

--
Galen Boyer

#10

After trying different things, I did manage to use VBA exclusively
to:
-Link to Oracle
-Use SQL to retrieve data (then I loaded that data into an ADO
recordset and closed the Oracle connection)
....and no ODBC connection was needed.
I did have to have the Oracle Client and TNSNAMES.ORA file, both of
which are loaded on the user's PC.

RLN