Can I create an Excel macro that adds a system DSN data source to the user's
PC so that the database can be used for subsequent querying of external
data?

Can this reliably work even if repeated on a PC that already has this data
source set up?

Can it reliably work if different versions of Windows XP?

Any thoughts or advise would be appreciated!

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 Lon
Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" ( ByVal hwndParent as Long, ByVal fRequest as Long, ByVal lpszDriver as String, ByVal lpszAttributes as String ) as Lon

The available DSNs are listed at these nodes in the registry

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

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?

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?

DSN-less literally means that the connection string does not have the sub string "DSN=". Here is an example

Sub DSNless(
Cnn = "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\nwind.mdb;
' Or you can use the one belo
' Cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\nwind.mdb;
Sql = "Select * From customers
Set ADORS = CreateObject("ADODB.RecordSet"
ADORS.Open Sql, Cn
End Su

A DSN-less connection may be quicker too: a DSN connection looks up the registry whereas a DSN-less connection does not

Database connections may be
based on a DSN,
DSN-less
based on a file DS
based on OLEDB,
based on UD

If you want to create a connection on your PC and distribute the method of connection to others, use a FILE DSN. Create one on your PC using the ODBC manager: this creates a file. You can distribute this file with your application. You can also choose the location of this file on target PCs as our connection can specify the full path of the File DSN

Search "File DSN" using Google for several examples.

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?