Microsoft ODBC Drive Manager: Data Source Name not found and No Default Driver Specified

[Ross Culver]

I've been meaning to ask this question for years (literally).

In the Excel reports that I create for others in the company, I almost exclusively use external data via MS Query from SQL databases. I always use a system ODBC connection created not within Excel, but within the ODBC administrator. In many cases I'll use the SA login and password for the connection and in some cases, I'll use a trusted connection/integrated security; but I always check the 'Save Password' box in the Data Range Properties.

Here's the problem: sometimes, in fact more often than not, when one of these spreadsheets is opened by someone else on another computer with different credentials, I get the error: Microsoft ODBC Drive Manager: Data Source Name not found and No Default Driver Specified; even though I've manually created an ODBC connection on that computer identical to the one on my computer.

Can someone tell me why? Given this scenario, is there a difference between using a file, user or system odbc connection? Does the connection have to be created from within Excel during the Get External Data process?

One other note. With the example I encountered today, I did not get the error when I opened the same file on a server logged in as the domain administrator, but I did get the error on every other computer I tried to refresh from, except, of course, my own.

Thanks,

Ross

 

[iain_macaulay]

I've been meaning to ask this question for years (literally).

In the Excel reports that I create for others in the company, I almost exclusively use external data via MS Query from SQL databases.  I always use a system ODBC connection created not within Excel, but within the ODBC administrator.  In many cases I'll use the SA login and password for the connection and in some cases, I'll use a trusted connection/integrated security; but I always check the 'Save Password' box in the Data Range Properties.

Here's the problem:  sometimes, in fact more often than not, when one ofthese spreadsheets is opened by someone else on another computer with different credentials, I get the error: Microsoft ODBC Drive Manager: Data SourceName not found and No Default Driver Specified; even though I've manually created an ODBC connection on that computer identical to the one on my computer.

Can someone tell me why?  Given this scenario, is there a difference between using a file, user or system odbc connection?  Does the connection have to be created from within Excel during the Get External Data process?

One other note.  With the example I encountered today, I did not get theerror when I opened the same file on a server logged in as the domain administrator, but I did get the error on every other computer I tried to refreshfrom, except, of course, my own.

Thanks,

Ross

Did anyone ever answer this?

I connect to an oracle db on my machine, when i open up the
spreadsheet on another machine i want to connect to the db on that
machine, on other spreadsheets it gived me teh opportunity to change
the datasource name to the machine i am on, and connect fine, but on 1
particular machine I get the error message mentioned above. Does
anybody have any suggestions? Thanks.

 

[Ron Coderre]

In all likelihood, the problem is a missing entry in the TNSNAMES.ORA file
on the computer that cannot connect. While most ODBC connections directly
access the database, Oracle ODBCs must read connection information from that
TEXT file. Each entry is effectively a nickname for the database instance.
Associated with that nickname are the server containing the instance, port
number, etc.

Here's a typical entry:
ProdInstance.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TCPCOM.world)
(PROTOCOL = TCP)
(Host = MyServerName)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ProdInstance)
)
)

Consequently, if your computer has an entry named: ProdInstance.world
that is referenced by your data source,
and the other PC names that database entry: ProdInstance.Servers
The connection will fail.

Generally the TNSNAMES.ORA file is located at:
C:\oracle\ora81\network\ADMIN\TNSNAMES.ORA

But it may be in other folders. Also, after verifying that
both computers have the same entry, but one still cannot connect
....and just before smashing the PC to bits....
search it for multiple TNSNAMES.ORA files. The PC may be set up
to read it from a non-standard location.

Typical procedural workarounds are:
1)Standardize on TNSNames entries
and make the text file universally available
or
2)Create multiple entries for the same database whenever you need them.

Either way, A TEXT FILE TO CONNECT TO ORACLE!...Ridiculous!

Does that help?
(post back if you have more questions)
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

is to create multiple

I've been meaning to ask this question for years (literally).

In the Excel reports that I create for others in the company, I almost
exclusively use external data via MS Query from SQL databases. I always use
a system ODBC connection created not within Excel, but within the ODBC
administrator. In many cases I'll use the SA login and password for the
connection and in some cases, I'll use a trusted connection/integrated
security; but I always check the 'Save Password' box in the Data Range
Properties.

Here's the problem: sometimes, in fact more often than not, when one of
these spreadsheets is opened by someone else on another computer with
different credentials, I get the error: Microsoft ODBC Drive Manager: Data
Source Name not found and No Default Driver Specified; even though I've
manually created an ODBC connection on that computer identical to the one on
my computer.

Can someone tell me why? Given this scenario, is there a difference between
using a file, user or system odbc connection? Does the connection have to
be created from within Excel during the Get External Data process?

One other note. With the example I encountered today, I did not get the
error when I opened the same file on a server logged in as the domain
administrator, but I did get the error on every other computer I tried to
refresh from, except, of course, my own.

Thanks,

Ross

 

[Ron Coderre]

DARN!....I mis-read the post and thought you were trying to connect to
Oracle...triggering a mini-rant.

Please disregard.

(I haven't worked with SQL Server in quite a while)