ODBC Connection Not Connecting

J

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
 
A

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
 
D

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"
 
J

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
 
D

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?
 
J

JimBurke via AccessMonster.com

Don't you need to specify the fully qualified database name, including the
path? I'm not sure what FIN_CUR is, but it seems that more than that is
needed. Does the code need a DSN value rather than Data Source? Sorry if I'm
off base, I don't use ODBC, but I looked at some examples and they semed to
use 'DSN=someDatabaseName'.
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?
I have modified my code to reflect your responses. Now when it gets to
the
Click to expand...
[quoted text clipped - 108 lines]
Click to expand...
 
A

AccessVandal via AccessMonster.com

Data Source should be the Path to the Access backend like "C:\MyFolder\
MyDatabase.mdb" or the Path to the SQL Server like "\\ServerName\
SQLServerName" or sometimes an IP address.

Is seems that the OP is not using the corrrect Provider "MSDAORA". I'm not
certian this will work with ADO as it is using DAO.

The OP never said any about the connection. Connect to What? A Access file or
SQL Server?

Here's a connection to Access File. It's only one sample, there are more.
Standard security
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User
Id=admin;Password=;

If OP using 2007.
Standard security
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.
accdb;Persist Security Info=False;

Here's the sample for if SQL Server (2005)
Trusted Connection
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
Security=SSPI; Trusted Connection

alternative syntax
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connecting to an SQL Server instance
Server=myServerName\
theInstanceName;Database=myDataBase;Trusted_Connection=True;

And where is "Conn" and "cmd" declared?

And if OP is using ODBC.

Standard security
Driver={SQL Server Native Client 10.0}
;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Trusted Connection

Driver={SQL Server Native Client 10.0}
;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Equivalent
key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"

Connecting to an SQL Server instance
Driver={SQL Server Native Client 10.0};Server=myServerName\theInstanceName;
Database=myDataBase;Trusted_Connection=yes;

If not using Trusted Connection, set it to "No".
Don't you need to specify the fully qualified database name, including the
path? I'm not sure what FIN_CUR is, but it seems that more than that is
needed. Does the code need a DSN value rather than Data Source? Sorry if I'm
Click to expand...

No, DSN refers to the source of the DSN connection where you have create the
DSN in your PC control Panel - Administrative Tools - Data Source. It is the
NAME of that source.
 
J

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.
 
D

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"
 
J

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
 
D

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.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error Not resetting 1
JDBC-ODBC Bridge driver & ODBC driver are two different installati 0
Error connecting to MS Access Database with ODBC 0
Connection with ODBC 1
Oracle Connection String 1
ODBC - Connection String 1
Transfer Database - Import tables fom Oracle 0
Connection to Linked ODBC Tables 2

Top