By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 828 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

automating oracle ODBC driver connect login

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.Connection, i As Long, j As Long
Dim Rst As New ADODB.Recordset, RS As DAO.Recordset

conn.Provider = "OraOLEDB.Oracle"
conn.ConnectionString = "Data Source=ccc" & _
";User ID=Steve;Password=tiger"
conn.Open
Rst.CursorLocation = 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.OracleTable is an arbitrary table name) you need to set a
tableDef object variable to the ODBC linked table and do a debug.Print
tdf.SourceTableName to see what the table name actually is (usually
OracleServiceName.tablename)

Dim tdf As TableDef
Set tdf = CurrentDB.TableDefs("yourODBCoracleTable")
Debug.Print tdf.SourceTableName

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!
Nov 12 '05 #2

P: n/a
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 <rp*****@aol.com> wrote in message news:<40*********************@news.frii.net>...
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.Connection, i As Long, j As Long
Dim Rst As New ADODB.Recordset, RS As DAO.Recordset

conn.Provider = "OraOLEDB.Oracle"
conn.ConnectionString = "Data Source=ccc" & _
";User ID=Steve;Password=tiger"
conn.Open
Rst.CursorLocation = 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.OracleTable is an arbitrary table name) you need to set a
tableDef object variable to the ODBC linked table and do a debug.Print
tdf.SourceTableName to see what the table name actually is (usually
OracleServiceName.tablename)

Dim tdf As TableDef
Set tdf = CurrentDB.TableDefs("yourODBCoracleTable")
Debug.Print tdf.SourceTableName

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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.