Sub GetConnection(ByVal strSQL As String, ByVal RowNum As Integer)
   Dim conSQL As ADODB.Connection
    
    
    'SQL String will later replace this with a function further Down to
    'Extract Data either from text files or via Excel's cells

    
    'set the properties and connect to the MS SQL Server
    Set conSQL = New ADODB.Connection
    conSQL.Provider = "SQLOLEDB"
    conSQL.Properties("Data Source").Value = "COTSVALESQL" 'SERVER Name
    conSQL.Properties("Initial Catalog").Value = "keyBaseData" 'DatabaseName
    conSQL.Properties("Integrated Security").Value = "SSPI" 'Use Windows Authentication
    
    conSQL.Open
    
    Debug.Print conSQL.Properties("SQL Support").Type
    'Execute the SQL Command Specified Above
    Set cmdCheck = New ADODB.Command
    Set cmdCheck.ActiveConnection = conSQL
    cmdCheck.CommandText = strSQL
    
    'Execute the SQL Command and Trap any errors that Occur
    Set rstCheck = New ADODB.Recordset
    On Error GoTo Err_Command
    cmdCheck.Execute
    On Error GoTo 0
    
        
    'Clean up and leave nothing open
    rstCheck.Close
    conSQL.Close
    Set conSQL = Nothing
    Set rstCheck = Nothing
    
    Exit Sub
    
Err_Command:

    Dim ErrNo As Error

       ' Notify user of any errors that result from executing the query
          MsgBox "Error number: " & Err.Number & vbCr & _
             Err.Description

   
    ' clean up
    
    If Not conSQL Is Nothing Then
        If conSQL.State = adStateOpen Then conSQL.Close
    End If
    Set conSQL = Nothing
    
    Cells(RowNum, 5).Value = "not Processed"
    Cells(RowNum, 6).Value = "Stopped Processing at " & Now() & "due to SQL Error"
    End
End Sub

/* This Query will update the KeyBaseData Sunrise Database */

-- Sunrise
-- [VIEW_SunriseReferrals]
-- [VIEW_Tmp_CMORD]
-- [VIEW_TmpCMTASK]
-- 
-- 

-- Setup the database.
use KeyBaseData
go
set dateformat dmy
go

/* SUNRISE [VIEW_SunriseReferrals] */
-- First remove the existing data.
Print 'Deleting the old year-to-date [VIEW_SunriseReferrals] data...'
go
Delete 
from KeyBaseData..[VIEW_SunriseReferrals]
where AdmitDTM >= '1/04/2003'
go

-- Now to try and up-date the data.
Print 'Copying the new year-to-date [VIEW_SunriseReferrals] data...'
go
Insert into KeyBaseData..[VIEW_SunriseReferrals]
select *
from RIKNT05.PCTDataMart.dbo.[VIEW_SunriseReferrals] S
where S.AdmitDTM >= '1/04/2003'
go


/* SUNRISE [VIEW_Tmp_CMORD] */
-- First remove the existing data.
Print 'Deleting the old year-to-date [VIEW_Tmp_CMORD] data...'
go
Delete 
from KeyBaseData..[VIEW_Tmp_CMORD]
-- No Date field available for part up-date
go

-- Now to try and up-date the data.
Print 'Copying the new year-to-date [VIEW_Tmp_CMORD] data...'
go
Insert into KeyBaseData..[VIEW_Tmp_CMORD]
select *
from RIKNT05.PCTDataMart.dbo.[VIEW_Tmp_CMORD]
-- No Date field available for part up-date
go


/* SUNRISE [VIEW_TmpCMTASK] */
-- First remove the existing data.
Print 'Deleting the old year-to-date [VIEW_TmpCMTASK] data...'
go
Delete 
from KeyBaseData..[VIEW_TmpCMTASK]
where SignificantDTM >= '1/04/2003'
go

-- Now to try and up-date the data.
Print 'Copying the new year-to-date [VIEW_TmpCMTASK] data...'
go
Insert into KeyBaseData..[VIEW_TmpCMTASK]
select *
from RIKNT05.PCTDataMart.dbo.[VIEW_TmpCMTASK]
where SignificantDTM >= '1/04/2003'
go