Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing ODBC link in a query | Excel Discussion (Misc queries) | |||
link ODBC connection to an Oracle database | Excel Discussion (Misc queries) | |||
Creating ODBC link within SELECT statement | Charts and Charting in Excel | |||
paste special keep odbc link | Excel Discussion (Misc queries) | |||
ODBC Link to db with custom modules | Excel Discussion (Misc queries) |