automating oracle ODBC driver connect login

[jmev7]

Any way to avoid having to manually enter my user name & password for
the Oracle login box? I normally run a query on an attached Oracle
table and have to enter the un/pw before the query will run. There's
no place to enter this information in. Later, if I run subsequent
queries on the table in question, I don't need to enter un/pw again. I
can leave my PC on 24x7, but that's not really a solution. I need to
know if I can log in to the database by opening the table via an ADO
recordset, passing the user/pw and then closing it, and having the
current file session maintain that info so I can automate subsequent
queries.


That was a mouthful, so please let me know if I confused anyone
besides myself.

Thanks

[Rich P]

Re: automating oracle ODBC driver connect login

If you do an ODBC link, there should be a checkbox that asks if you want
the link to remember your login so that you don't get prompted all the
time.

As for ADO, here is some code you could try:

Dim conn As New ADODB.Connectio n, i As Long, j As Long
Dim Rst As New ADODB.Recordset , RS As DAO.Recordset

conn.Provider = "OraOLEDB.Oracl e"
conn.Connection String = "Data Source=ccc" & _
";User ID=Steve;Passwo rd=tiger"
conn.Open
Rst.CursorLocat ion = adUseClient
Rst.Open "SELECT someDatefld FROM dbo.OracleTable WHERE someDatefld >=
TO_DATE('01-01-02', 'DD-MM-YY')", conn
'copy data from Rst to RS
...
Rst.Close
conn.close

The interesting thing about using Ado with Oracle (com ADO) is that you
reference the ODBC dsn (ccc in this example) as your datasource instead
of the server. If you have an ODBC dsn then the Oracle Client service
is obviously installed on your computer (which is the only way to get
the working Oracle ODBC driver). And to reference the Oracle table
correctly (dbo.OracleTabl e is an arbitrary table name) you need to set a
tableDef object variable to the ODBC linked table and do a debug.Print
tdf.SourceTable Name to see what the table name actually is (usually
OracleServiceNa me.tablename)

Dim tdf As TableDef
Set tdf = CurrentDB.Table Defs("yourODBCo racleTable")
Debug.Print tdf.SourceTable Name

One last note, pl Sql (oracle sql) uses slightly different syntax than
jet sql (or Tsql - sqlserver sql). You have to cast stuff (like
datefields - using To_Date function in PL sql).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[jmev7]

Re: automating oracle ODBC driver connect login

Rich, thanks for the detailed post. I finally got caught up enough to
even read this. I will hopefully have time this week to try your
suggestions.

Thanks again.

JV

Rich P <rpng123@aol.co m> wrote in message news:<407ecf57$ 0$199$75868355@ news.frii.net>. ..[color=blue]
> If you do an ODBC link, there should be a checkbox that asks if you want
> the link to remember your login so that you don't get prompted all the
> time.
>
> As for ADO, here is some code you could try:
>
> Dim conn As New ADODB.Connectio n, i As Long, j As Long
> Dim Rst As New ADODB.Recordset , RS As DAO.Recordset
>
> conn.Provider = "OraOLEDB.Oracl e"
> conn.Connection String = "Data Source=ccc" & _
> ";User ID=Steve;Passwo rd=tiger"
> conn.Open
> Rst.CursorLocat ion = adUseClient
> Rst.Open "SELECT someDatefld FROM dbo.OracleTable WHERE someDatefld >=
> TO_DATE('01-01-02', 'DD-MM-YY')", conn
> 'copy data from Rst to RS
> ..
> Rst.Close
> conn.close
>
> The interesting thing about using Ado with Oracle (com ADO) is that you
> reference the ODBC dsn (ccc in this example) as your datasource instead
> of the server. If you have an ODBC dsn then the Oracle Client service
> is obviously installed on your computer (which is the only way to get
> the working Oracle ODBC driver). And to reference the Oracle table
> correctly (dbo.OracleTabl e is an arbitrary table name) you need to set a
> tableDef object variable to the ODBC linked table and do a debug.Print
> tdf.SourceTable Name to see what the table name actually is (usually
> OracleServiceNa me.tablename)
>
> Dim tdf As TableDef
> Set tdf = CurrentDB.Table Defs("yourODBCo racleTable")
> Debug.Print tdf.SourceTable Name
>
> One last note, pl Sql (oracle sql) uses slightly different syntax than
> jet sql (or Tsql - sqlserver sql). You have to cast stuff (like
> datefields - using To_Date function in PL sql).
>
> Rich[/color]