Create ODBC link Using VBA

[pspyve]

Hi All,
I do a lot of linking access db to excel. I do it via the ODBC link on
the computer(ODBC Data Source Administrator). I have to manually set
this up for each user.
Just wondering if you can, or if it is possible to create that link
programmaticly using VBA or maybe a batch file or something??

Thanks in advance.

Phil

 

[Dove]

Phil,

Here is how I do it for an ODBC connection to an Oracle database. I only
use these connections for retrieving values as that is all I ever need to do
on my ODBC connections:

' Replace anything in quotes with your information, which can be collected
and stored via string variables. Use an InputBox or other means to get info
from users to validate.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

cnn.Open "YourDSN", "YourUsername", "YourPassword"
rst.ActiveConnection = cnn
rst.CursorLocation = adUseServer

rst.Source = "SELECT * FROM TABLE WHERE FIELD='data'"
rst.Open
variable = rst.Fields("FIELD") ' Do whatever you need to the data
rst.Close

Set rst = Nothing
Set cnn = Nothing

 

[pspyve]

Thanks Dove for your reply. I really appreciate it.
Using this set up, Do you need to set up a system DSN in the computers
ODBC settings(ODBC Data Source Administrator), as this is my issue?

Phil S

 

[Dove]

Phil,

Yes you do, or if you have an oracle database I think there is a way to do
it in the tnsnames.ora file but I don't know it off the top of my head. The
setup for a System DSN through the ODBC settings will vary depending upon
the database management system used and the driver version...