Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC connection question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC connection question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODBC connection failed. | Excel Discussion (Misc queries) | |||
ODBC connection failed. | Excel Discussion (Misc queries) | |||
ODBC Connection | Excel Programming | |||
ODBC connection by udl | Excel Programming | |||
ODBC System DSN Connection | Excel Programming |