Multiple DSN's for Excel Macro

[MacroUser]

Hello All,
I've a excel VBA add-in that fetches the data from SQL server based on user input. When the user enters customercode all the customer data is populated in the spreadsheet. I've setup the ODBC connection for this macro in ODBCad32.exe.
Now I need to modify my macro to pull data from multiple database using complex join query. Is it possible to get data from multiple database/DSN in Excel Macro? Thanks.
Regards

[ranman256]

Your addin would need the odbc data link, or
build your new ODBC connection via code.
maybe like:



Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DB
Dim vProvid
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

uid = "john smith"
pwd = ""
DB = "database path"
vProvid = "Microsoft.Jet.OLEDB.4.0" ' "SQLOLEDB"


With con
'.Provider = vProvid
'.Properties("User ID").Value = uid
'.Properties("Password").Value = pwd

.Open "Provider=SQLOLEDB; Server=db.someserver.uk; Database=DB; User ID=" & uid & "; Password=" & pwd & ";"
End With

Set rs = con.Execute("qsNames")