Another ODBC Connection Thread

[cyborg]

I have been searching for the whole day for the answer to this issue but I can't seem to figure it out

Here is my code:

Sub autoUpdate()

'Connection Variables

'Dim strConn As String
Dim strConnection, conn
'Set conn = Server.CreateObject("ADODB.Connection")
Set conn = New ADODB.Connection

strConnection = "Driver={Microsoft ODBC for Oracle};DSN=InfoCtr;Uid=username;Pwd=password;"

conn.Open strConnection
DoCmd.RunMacro "Updatetest", 1
'Call Form_dashboardv2_0.transfer

conn.Close
Set conn = Nothing

End Sub

This database connects from Access 2002/2003 to an Oracle database. The address is saved in the System DSN. I have 'Set conn = Server.CreateObject("ADODB.Connection") commented out because that caused an issue "Object Expected." This is for a System DSN setup where I for some odd reason CANNOT save the password like how everyone can when they link their tables (and check the save password)

Everytime I change the connection string format, it gives me some odd error message. The current one right now is:

ORA-12560: TNS: protocol adapter error

Any gurus out there can offer some help? I would really appreciate it!

-Allen

 

[georgedwilkinson]

Can you log onto SQLPlus, TOAD, or Oracle Enterprise Manager using those credentials ("username/password")? Most Oracle DBAs don't allow a schema called "username" in their database.

Also, check asktom.oracle.com (or google) for the error message you got. Frequently, a TNS error means your computer cannot find the database in question. Again, try connecting with an Oracle tool before you jump all the way to a much less compatible MS tool.

 

[cyborg]

Hmm, well put it this way. If I change the DSN to the actual server address in the connection string:

strConnection = "Driver={Microsoft ODBC for Oracle}; SERVER=P007.ORACLEDB.UPS.COM;Uid=username;Pwd=password;"

It pretty much runs the code fine but asks me for a username and password to the server. Is there something wrong in the syntax? The username and password are real username and passwords I normally use if I enter it when the access database asks for it.

 

[georgedwilkinson]

Dang, I'm at a loss then. I haven't ever tried to use ODBC to connect to Oracle, only Oracle Objects for Ole.