# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  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" <pspyve.2b2d46_1153096209.2413@excelforum-nospam.com> wrote in
message news:pspyve.2b2d46_1153096209.2413@excelforum-nospam.com...
>
> 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
>

----------


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

"pspyve" <pspyve.2b2g4i_1153100112.4803@excelforum-nospam.com> wrote in
message news:pspyve.2b2g4i_1153100112.4803@excelforum-nospam.com...
>
> 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
>

----------

