ODBC Connection Not Connecting

[jutlaux]

I am using the below code to 1) automatically connect to a Microsoft ODBC for
Oracle connection called "FIN_CUR" and 2) run an query on a linked table in
FIN_CUR

The issue i am having is that the ODBC connect part of the script appears to
be working, but when it goes to try to run the query I still get the popup
prompting for user name and password to make the connection to the ODBC. I
have verify that the user name and password are correct, but don't know what
I am missing.

Here is my code

Public Sub OraConnect()
Dim strErr As String
On Error GoTo connError
Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=MSDAORA.1; User
ID=GENERIC;Password=GENERIC; Data Source=FIN_CUR;"

conn.Open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn

DoCmd.OpenQuery "qappFIN_CUR_Truncate", , acReadOnly

Exit Sub
'
' handle Oracle error
'
connError:
strErr = "Error connecting to Oracle"
strErr = strErr & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strErr = "Reports will NOT be created"
strErr = strErr & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strErr = strErr & Err.Description
MsgBox (strErr)

Exit Sub
End Sub

 

[Alex Dybenko]

Hi,
the problem - that DoCmd.OpenQuery has nothing to do with conn object. If
you are talking about MDB file and qappFIN_CUR_Truncate is pass-through
query - then you have to set connect property of the query:

currentdb.querydefs("qappFIN_CUR_Truncate ").Connect = "Provider=MSDAORA.1;
User ID=GENERIC;Password=GENERIC; Data Source=FIN_CUR;"
DoCmd.OpenQuery "qappFIN_CUR_Truncate"

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Douglas J. Steele]

Don't you need to specify ODBC in that Connect string, Alex:

currentdb.querydefs("qappFIN_CUR_Truncate ").Connect =
"ODBC;Provider=MSDAORA.1;
User ID=GENERIC;Password=GENERIC; Data Source=FIN_CUR;"
DoCmd.OpenQuery "qappFIN_CUR_Truncate"

 

[jutlaux]

I have modified my code to reflect your responses. Now when it gets to the
Docmd.Open query I get a pop up to select data source instead of it running
the query.

Public Sub OraConnect()
Dim strErr As String
On Error GoTo connError
Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=MSDAORA.1; User
ID=GENERIC;Password=GENERIC; Data Source=FIN_CUR;"

conn.Open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn

CurrentDb.QueryDefs("qappFIN_CUR_Truncate").Connect =
"ODBC;Provider=MSDAORA.1;User ID=GENERIC;Password=GENERIC; Data
Source=FIN_CUR;"

DoCmd.OpenQuery "qappFIN_CUR_Truncate"

Exit Sub
'
' handle Oracle error
'
connError:
strErr = "Error connecting to Oracle"
strErr = strErr & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strErr = "Data Will Not Be Updated"
strErr = strErr & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strErr = strErr & Err.Number & " " & Err.Description
MsgBox (strErr)

Exit Sub
End Sub

 

[Douglas J. Steele]

First of all, you don't need the code

Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=MSDAORA.1; User
ID=GENERIC;Password=GENERIC; Data Source=FIN_CUR;"

conn.Open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn

It's doing absolutely nothing!

Does the data source FIN_CUR exist?

 

[jutlaux]

Deleted all code as you suggested. The data source does exist and was setup
under the System DSN and I have verified that supplied user name and password
are valid and work.

 

[Douglas J. Steele]

Did you put in the ODBC at the beginning, as I suggested?

currentdb.querydefs("qappFIN_CUR_Truncate ").Connect =
"ODBC;Provider=MSDAORA.1;User ID=GENERIC;Password=GENERIC; Data
Source=FIN_CUR;"
DoCmd.OpenQuery "qappFIN_CUR_Truncate"

 

[jutlaux]

I did. Here is the script as it stands right now.

Public Sub OraConnect2()
Dim strErr As String
On Error GoTo connError

CurrentDb.QueryDefs("qappFIN_CUR_Truncate").Connect = "ODBC;Data
Source=FIN_CUR;User ID=GENERIC;Password=GENERIC"

DoCmd.OpenQuery "qappFIN_CUR_Truncate" ', , acReadOnly

Exit Sub
'
' handle Oracle error
'
connError:
strErr = "Error connecting to Oracle"
strErr = strErr & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strErr = "Data Will Not Be Updated"
strErr = strErr & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strErr = strErr & Err.Number & " " & Err.Description
MsgBox (strErr)

Exit Sub
End Sub

 

[Douglas J. Steele]

Hmm. Not sure, but try

Public Sub OraConnect2()
Dim qdfPT As QueryDef
Dim strErr As String
On Error GoTo connError

Set qdfPT = CurrentDb.QueryDefs("qappFIN_CUR_Truncate")
qdfPT.Connect = "ODBC;Data Source=FIN_CUR;User
ID=GENERIC;Password=GENERIC"
qdfPT.Close
Set qdfPT = Nothing

DoCmd.OpenQuery "qappFIN_CUR_Truncate" ', , acReadOnly

Exit Sub
'
' handle Oracle error
'
connError:
strErr = "Error connecting to Oracle"
strErr = strErr & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strErr = "Data Will Not Be Updated"
strErr = strErr & Chr(13) & Chr(10) & Chr(13) & Chr(10)
strErr = strErr & Err.Number & " " & Err.Description
MsgBox (strErr)

Exit Sub
End Sub

On the other hand, you're probably better off using the Execute method,
rather than OpenQuery.

 

[Alex Dybenko]

Yes, thanks!

to jutlaux - you can open query in design (SQL) view, open properties, go
to connect property and build connection by selecting DSN, etc. Then look
what kind of connection string access built.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Pope Rudraigh]

Hi there,

Don't know if you've resolved this yet but I thought I'd pop my two
cents worth in here.

I have an Access app that has been running near flawlessly for over
three years. I found the ODBC connection to be problematic. Switched
to OLEDB connection. One thing, it's my understanding that you MUST
have the Oracle client installed in order to connect to an Oracle db.

Below is my simple function for writing a record to the database along
with the connection string (I keep the connection strings in a table
along with some other parameters). Hope it gives you an idea of how to
accomplish what you need to do. As always, watch for line wrap.

Regards,
RD


Connection string:
Provider=OraOLEDB.Oracle.1;Password=carefullprod;Persist Security
Info=True;User ID=carefull;Data Source=carefulldata

Function:
Function fWriteRecord(sSql As String, sConn As String, sDocName As
String) As String
' This function's only job is to take a SQL statement and a connection
' and write a record to the database

On Error GoTo ErrorHandler
Dim cnn As New ADODB.Connection
Dim vRet As Variant

cnn.Open sConn
cnn.Execute sSql
fWriteRecord = "True"

ExitPoint:
Set cnn = Nothing
Exit Function

ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
vRet = fWriteLog(sDocName, Err.Number & ": " & Err.Description, "NA",
"fWriteRecord")
fWriteRecord = "Error: " & Err.Number & " occurred when attempting to
write record."
Resume ExitPoint
End Function

 

[RD]

Sorry about those headers. I sometimes forget to clean up after
myself.