DSN / Connection string for Oracle database access

[syswizard]

I get confused about establishing an ODBC or OLE DB connection to a foreign data source.
First I must have the necessary drivers installed, right ? These originate from the host package...in this case the Oracle client, right ?
Once I do that, I need to create a USER or SYSTEM DSN......or can I just patch the connection string and credentials into the Connection property of the connecton within Excel ?
I've always been confused on the best way to handle this. In a multiple developer environment, it would seem stupid for me for everyone to create their own DSN and then have to change all of them when any data source changes are made.

[HansV]

I prefer to work with DSN-less connections. Assembling the connection string in VBA isn't that hard.
The Connection Strings Reference has examples of connection strings for all kinds of data sources.

[syswizard]

Hans - Thanks for that reminder about the DSN vs. DSN-less technique.....now it's coming back to me.

However, in the case of Oracle specifically, isn't there a "native" ODBC driver they provide as part of their client installation vs. the one that Microsoft supplies as part of the Office installation ? I believe one should use the Oracle-supplied driver....no ?

[HansV]

Microsoft will phase out its ODBC driver for Oracle, it recommends using the driver provided by Oracle.
From ODBC Driver for Oracle:

This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use the ODBC driver provided by Oracle.

[syswizard]

Thanks Hans....but how to distinguish between the two ?
Also, to get the connection string specific to the Oracle ODBC driver, isn't it best just to set-up the DSN one-time and then use the connection attributes that are exposed when the connection is completed ?

[HansV]

Yes, that's a valid way of obtaining a connection string!