Create ODBC link Using VBA
 

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


--
pspyve
------------------------------------------------------------------------
pspyve's Profile: http://www.excelforum.com/member.php...o&userid=30656
View this thread: http://www.excelforum.com/showthread...hreadid=561885

Create ODBC link Using VBA
 

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" wrote in
message ...

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


--
pspyve
------------------------------------------------------------------------
pspyve's Profile:
http://www.excelforum.com/member.php...o&userid=30656
View this thread: http://www.excelforum.com/showthread...hreadid=561885

Create ODBC link Using VBA
 

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


--
pspyve
------------------------------------------------------------------------
pspyve's Profile: http://www.excelforum.com/member.php...o&userid=30656
View this thread: http://www.excelforum.com/showthread...hreadid=561885

Create ODBC link Using VBA
 

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

"pspyve" wrote in
message ...

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


--
pspyve
------------------------------------------------------------------------
pspyve's Profile:
http://www.excelforum.com/member.php...o&userid=30656
View this thread: http://www.excelforum.com/showthread...hreadid=561885