Excel 2013 VBA ADO Connections to multiple SQl Servers

[Frank Carpenter]

Hi

I am newer to the more advanced functionality of connecting to external data sources using VBA. We are currently on EXCEL 2013 and I have a need to be able to retrieve information from files that are one two separate database servers. One server contains several datafiles and the other contains a calendar file. I need to be able to use the timestamp from one of the data files to determine the weekday and holiday values assigned to the dates in the calendar file. So basically I would be looking to sum the number of occurrences that a date within a date range carries a work value of zero. (Zero indicates Weekend day or Holiday) The date being checked in the data range would come from the timestamp. Any suggested links or code examples would be greatly appreciated.

Example: As a SUBQUERY .

(Select count(*)
From Calendar File
Where timestamp date >= calendardate
And timestamp date <= calendardate)

My current ADO Connections use and ODBC Driver and look like this;

Public Sub OpenODBCConnection()

Dim rs As ADODB.Recordset
Dim mbSuccessfulOpen As Boolean
Dim SQL As String

Call ODBCSettings

blnRouterConnectionOK = False

Set cnS10B4642 = New ADODB.Connection
On Error Resume Next
cnS10B4642.Close

On Error GoTo ConnectionFailed
Set cnS10B4642 = New ADODB.Connection
strOLEDBProvider = "MSDASQL"
strRouterName = "IRDBv5" *** ODBC DRIVER NAME *****
With cnS10B4642
.Provider = strOLEDBProvider '"MSDASQL"
.Properties("Data Source").Value = strRouterName '"S10B4642"
.CursorLocation = adUseClient
.CommandTimeout = 160
End With
cnS10B4642.Open



blnRouterConnectionOK = True

ConnectionSuccessful:
On Error GoTo 0
Exit Sub

ConnectionFailed:
blnRouterConnectionOK = False
'ShowADOError
MsgBox "Error" & CStr(Err.Description)
GoTo ConnectionSuccessful
End Sub