Connect to Oracle 
Author Message
 Connect to Oracle

I am not well experienced in using Access as a client server app.

I have a form where the user enters in some data in some fields and presses
a button.
The program should then retrieve a value from an Oracle DB and display to
the user.
I am using DAO.
My questions are.
I want to connect to the oracle db and leave the connection open for as long
as the user is in the form.
Where do I put the connection and does anyone have a sample oracle
connection code.

Second, I want to make the data retrieval as quick as possible. I understand
that using a passthrough query is the fastest?
Does anyone have an example of using a passthough query.

Terry



Mon, 29 Aug 2005 05:03:51 GMT  
 Connect to Oracle
The simplest way to go about your problem would be to
create a system DSN to the Oracle database, and use one
generic pass-through query to get the data. Here is how I
would do it:

1. Go to queries and create a new query, without selecting
any tables. Then select Query type - Pass-through. Go to
View -> Properties and in the Connect property click on
the ODBC string builder (once you click on the property a
little button appears on the right). Select there your
System DSN and type in UID and password when prompted.
Answer Yes to whether to save the password.

Type smth like

Select SysDate From dual

in the query itself and save it (with qry_Ora name.)

Then somewhere in your code
...
    On Error GoTo Sub_Err
Dim qd   As QueryDef
Dim rs   As Recordset
Dim sSql As String
Dim sTmp As String
    '    Construct your SQL (in Oracle syntax)
    sSql = "Select * From dual Where RowNum = 1"
    '    Get the data back to Access
    Set qd = Currentdb.QueryDefs("qry_Ora")
    With qd
        sTmp = .SQL
        .SQL = sSql
        Set rs = .OpenRecordset
    End With
    '    Now you could use the recordset anyway you like
Sub_Exit:
    If Not qd Is Nothing Then
        qd.SQL = sTmp
    End If
    Exit Sub
Sub_Err:
    MsgBox Err.Description
    Resume Sub_Exit
End Sub

It may sound a bit complicated, but it pprovides you with
quite some flexibility and performance gains.

Hope this helps.

Quote:
>-----Original Message-----
>I am not well experienced in using Access as a client
server app.

>I have a form where the user enters in some data in some
fields and presses
>a button.
>The program should then retrieve a value from an Oracle
DB and display to
>the user.
>I am using DAO.
>My questions are.
>I want to connect to the oracle db and leave the

connection open for as long
Quote:
>as the user is in the form.
>Where do I put the connection and does anyone have a
sample oracle
>connection code.

>Second, I want to make the data retrieval as quick as

possible. I understand
Quote:
>that using a passthrough query is the fastest?
>Does anyone have an example of using a passthough query.

>Terry

>.



Mon, 29 Aug 2005 13:34:43 GMT  
 Connect to Oracle
Thanks for your help.
What I don't want is to connect for each record the user selects to get the
data from Oracle.
I understand there is a way to open the connection and leave it open until
you close it.
Then as the user selects record, I will go to Oracle to get the information
but I don't want to opne and close the connection each time.
What I want to do is have the user connect to Oracle when they open the form
and disconnect when the form closes.
How do I connect to Oracle and keep the connection open when the form opens?

Terry


Quote:
> The simplest way to go about your problem would be to
> create a system DSN to the Oracle database, and use one
> generic pass-through query to get the data. Here is how I
> would do it:

> 1. Go to queries and create a new query, without selecting
> any tables. Then select Query type - Pass-through. Go to
> View -> Properties and in the Connect property click on
> the ODBC string builder (once you click on the property a
> little button appears on the right). Select there your
> System DSN and type in UID and password when prompted.
> Answer Yes to whether to save the password.

> Type smth like

> Select SysDate From dual

> in the query itself and save it (with qry_Ora name.)

> Then somewhere in your code
> ...
>     On Error GoTo Sub_Err
> Dim qd   As QueryDef
> Dim rs   As Recordset
> Dim sSql As String
> Dim sTmp As String
>     '    Construct your SQL (in Oracle syntax)
>     sSql = "Select * From dual Where RowNum = 1"
>     '    Get the data back to Access
>     Set qd = Currentdb.QueryDefs("qry_Ora")
>     With qd
>         sTmp = .SQL
>         .SQL = sSql
>         Set rs = .OpenRecordset
>     End With
>     '    Now you could use the recordset anyway you like
> Sub_Exit:
>     If Not qd Is Nothing Then
>         qd.SQL = sTmp
>     End If
>     Exit Sub
> Sub_Err:
>     MsgBox Err.Description
>     Resume Sub_Exit
> End Sub

> It may sound a bit complicated, but it pprovides you with
> quite some flexibility and performance gains.

> Hope this helps.

> >-----Original Message-----
> >I am not well experienced in using Access as a client
> server app.

> >I have a form where the user enters in some data in some
> fields and presses
> >a button.
> >The program should then retrieve a value from an Oracle
> DB and display to
> >the user.
> >I am using DAO.
> >My questions are.
> >I want to connect to the oracle db and leave the
> connection open for as long
> >as the user is in the form.
> >Where do I put the connection and does anyone have a
> sample oracle
> >connection code.

> >Second, I want to make the data retrieval as quick as
> possible. I understand
> >that using a passthrough query is the fastest?
> >Does anyone have an example of using a passthough query.

> >Terry

> >.



Mon, 29 Aug 2005 22:44:27 GMT  
 Connect to Oracle
You do not have to keep a connection open, as kind of
Access does it for you - it caches (pools) connections to
ODBC data sources, so that every subsequent time you open
your pass-through query it will take less time than the
very first. As you saw in the code snippet I never
mentioned a connection to be opened or closed.

You can also go with ODBCDirect technology to have a
Connection object which you can have open as you described.

However I find pass-through queries efficient enough for
most of my needs.

Quote:
>-----Original Message-----
>Thanks for your help.
>What I don't want is to connect for each record the user
selects to get the
>data from Oracle.
>I understand there is a way to open the connection and
leave it open until
>you close it.
>Then as the user selects record, I will go to Oracle to
get the information
>but I don't want to opne and close the connection each
time.
>What I want to do is have the user connect to Oracle when
they open the form
>and disconnect when the form closes.
>How do I connect to Oracle and keep the connection open

when the form opens?

- Show quoted text -

Quote:

>Terry


message

>> The simplest way to go about your problem would be to
>> create a system DSN to the Oracle database, and use one
>> generic pass-through query to get the data. Here is how
I
>> would do it:

>> 1. Go to queries and create a new query, without
selecting
>> any tables. Then select Query type - Pass-through. Go to
>> View -> Properties and in the Connect property click on
>> the ODBC string builder (once you click on the property
a
>> little button appears on the right). Select there your
>> System DSN and type in UID and password when prompted.
>> Answer Yes to whether to save the password.

>> Type smth like

>> Select SysDate From dual

>> in the query itself and save it (with qry_Ora name.)

>> Then somewhere in your code
>> ...
>>     On Error GoTo Sub_Err
>> Dim qd   As QueryDef
>> Dim rs   As Recordset
>> Dim sSql As String
>> Dim sTmp As String
>>     '    Construct your SQL (in Oracle syntax)
>>     sSql = "Select * From dual Where RowNum = 1"
>>     '    Get the data back to Access
>>     Set qd = Currentdb.QueryDefs("qry_Ora")
>>     With qd
>>         sTmp = .SQL
>>         .SQL = sSql
>>         Set rs = .OpenRecordset
>>     End With
>>     '    Now you could use the recordset anyway you like
>> Sub_Exit:
>>     If Not qd Is Nothing Then
>>         qd.SQL = sTmp
>>     End If
>>     Exit Sub
>> Sub_Err:
>>     MsgBox Err.Description
>>     Resume Sub_Exit
>> End Sub

>> It may sound a bit complicated, but it pprovides you
with
>> quite some flexibility and performance gains.

>> Hope this helps.

>> >-----Original Message-----
>> >I am not well experienced in using Access as a client
>> server app.

>> >I have a form where the user enters in some data in
some
>> fields and presses
>> >a button.
>> >The program should then retrieve a value from an Oracle
>> DB and display to
>> >the user.
>> >I am using DAO.
>> >My questions are.
>> >I want to connect to the oracle db and leave the
>> connection open for as long
>> >as the user is in the form.
>> >Where do I put the connection and does anyone have a
>> sample oracle
>> >connection code.

>> >Second, I want to make the data retrieval as quick as
>> possible. I understand
>> >that using a passthrough query is the fastest?
>> >Does anyone have an example of using a passthough
query.

>> >Terry

>> >.

>.



Tue, 30 Aug 2005 03:43:29 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Connecting to Oracle

2. Connecting to Oracle within VBA for Excel

3. Connecting to Oracle using Excel

4. Connecting to Oracle with DAO

5. Connecting To Oracle Through Access 97

6. Help: Connect to Oracle database

7. Help: Connect to Oracle database

8. Connecting to Oracle Table

9. connect to Oracle DB

10. Access 2002 , Excel 2002 and ODBC connect to Oracle database


 
Powered by phpBB® Forum Software © phpBB Group