ODBC dsn-less , password issue (1 Viewer)

Andy74 · Oct 5, 2014

Hello,

I am trying to replace my existing connections to an Oracle database from machine DSN to DSN-less.

Connection string is like:

"ODBC;Driver={Oracle in instantclient10_2};Dbq=flex;UID=username;PWD=password"

The connection works but it keeps on asking the password when you open a table, which is not ok for real use. When working with DSN I used to put the password in the registry key: [HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\DNSNAME]
"Password"="xxxxxxx" and no password was requested.

But now that there is no DSN, I was expecting no password prompt(beacuse it's in the connection string) but it keeps on asking.

If anyone has a clue I would be grateful.

Andy74

Rx_ · Oct 5, 2014

These can be tricky. Just wondering if the " ; " might be needed?
See if you can follow the format used here and please post your results.
There will be others searching for this Oracle question later on that your code could be helpful to them.

I used SQL Server Native Client (a free download from Microsoft) used in demos with AZURE. It is ODBC with some enhancements. Sorry, I don't connect directly to Oracle. Ended up using a MS SQL Server Linked Server to Oracle. Gave up on the lightly documented Oracle drivers (in fustration).
reference
http://www.access-programmers.co.uk/forums/showthread.php?t=247756&highlight=odbc

Code:

10    UID = "MyUID"     
20    pwd = "MyPwd"
30    sLocalName = TableName   ' Table name passed in from list into this function
      Dim DataBaseName As String
40    'DataBaseName = "SQLDB"
50    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 10.0;" & _
          "SERVER=mySQLl\myDBInstance;DATABASE=" & DataBaseName & ";" & _
          "UID=" & UID & ";" & _
          "PWD=" & pwd & ";" & _
          "Table=DBO." & sLocalName & ";Option=3;"
60        ModifiedRefreshDNSLess2 = strConnectionString

Andy74 · Oct 5, 2014

Thanks for quick reply RX. I tried with several options with and without the ";". Anyway I found an unusual behaviour: when I set the .connect property it correctly shows all the string (i.e. ODBC;Driver={Oracle in instantclient10_2};Dbq=flex;UID=username;PWD=password

but when I click to open the table I notice that the connection string is changed to:

ODBC;Driver={Oracle in instantclient10_2};Dbq=flex;;;

It looks like for some reasons (maybe safety?) the username and password are not stored in the connection string. At this point I have no clue!

ODBC dsn-less , password issue (1 Viewer)

Andy74

Registered User.

Rx_

Nothing In Moderation

Andy74

Registered User.

Similar threads

Users who are viewing this thread