I can't answer your question but can rephrase it for you ('cos I'd
like to know the answer):

'Can I use a DSN-less connection in MS Query?'

or perhaps

'Ping MSQuery expert, Dick Kusleika!'

--

"Hall" wrote in message ...
Thanks for your help!

I'm a real novice in this. I'm not sure how to do this with a DSN-less
connection.

We have a SQL Server database on the network and the Excel file will be a
reporting tool that people will use by populating dynamically from that
database.

I've made it work by manually setting up the database as a DSN in my PC and
then creating a Get External Data which can refresh whenever I want. The
intent is that I can now add a macro (or some kind of automation) so that
anyone who uses this tool can simply run the macro at any time and refresh
the data in the same way.

Do you have any suggestions on how to do this cleanly?

Thanks again!

"AA2e72E" wrote in message
...
Yes you can create DSN pogramatically. Research the two functions in the
following declarations:

Declare Function SQLCreateDataSource Lib "odbccp32.dll" (ByVal hwndParent
As Long,ByVal lpszDS As String) As Long
Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" ( ByVal hwndParent
as Long, ByVal fRequest as Long, ByVal lpszDriver as String, ByVal
lpszAttributes as String ) as Long

The available DSNs are listed at these nodes in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\odbc.ini ' System DSNs
HKEY_CURRENT_USER\SOFTWARE\ODBC\odbc.ini ' User DSNs

So, in order to add a DSN programatically, you need to:

1. ensure that it does not exist already (by reading the registry)
2. that the name you are choosing is unique (on all target PCs so that
your application has a single unique name to refer to)

Neither of these are straightforward.

Why not use DSN-less connections?