ODBC connection question

[matelot]

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

 

[Dave Patrick]

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/features/mssql/article.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

:
|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