Best to just use a DSN-less connection string;

Dim cnn As ADODB.Connection
Dim strConn

strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;Persist Security
Info=False

Or for system DSNs you can use RegWrite method to create the keys/ values.

HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

and also the subkey for each DSN
HKLM\SOFTWARE\ODBC\ODBC.INI\


This link may help.
http://www.databasejournal.com/featu...le.php/2238221


If you're using file DSN's, then you can copy them from/to;
%ProgramFiles%\Common Files\ODBC\Data Sources

If you use system DSN's then you can also create a *.reg file from;

You'll need to get the subkey with the DSN names
HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

and also the subkey for each DSN
HKLM\SOFTWARE\ODBC\ODBC.INI\

Also look for user DSN's in;

HKCU\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

and also the subkey for each DSN
HKCU\SOFTWARE\ODBC\ODBC.INI\


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"matelot" wrote:
|I have 2 questions on ODBC connection.
| First, is it possible to use VBA to setup the ODBC connection to link to
an
| access db located on each person local directory (same directory for all
| users). If so, can I get some example.
| Second, assuming that it is not possible, I setup a File DSN called
"mydsn"
| and use the following code to connect.
|
| connstring = "ODBC;DSN=mydsn;UID=;PWD=;Database=mydatabase"
| With ActiveSheet.QueryTables.Add(Connection:=connstring ,
| Destination:=Range("A1"), Sql:=sqlstring)
| .Refresh
| End With
|
| Everytime I run the macro, a dialog box appears asking me to "Select a
data
| source", then I select the "File data Source" tab to find my "mydsn". Then
| another screen appears with ODBC Microsoft Access setup which shows the
path
| to the local directory.
| Anyway to bypass these screens? I just want the user to click on "Import"
| and the macro will connect to Access and import the data into cell A1.
|
| Thanks in advance
| Mat