Hi All
Everytime i try to run the following code i get the error 'general ODBC error'
I cannot see why this is happening, the error is very vaugue...has anyone got any idea why this occurs. (some values have been *'ed out)
Thanks
Everytime i try to run the following code i get the error 'general ODBC error'
I cannot see why this is happening, the error is very vaugue...has anyone got any idea why this occurs. (some values have been *'ed out)
Thanks
Code:
Sub Remedy_CR_ODBC()
Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable
Dim Pwd As String
Dim connection As String
Dim Usr As String
Const RemedySelect1 = "SELECT " _
& "GIT_Change_Request.Assignee_group, " _
& "GIT_Change_Request.Tracker_name, " _
& "GIT_Change_Request.Ticket_id, " _
& "GIT_Change_Request.Configuration_ID, " _
& "GIT_Change_Request.Short_description, " _
& "GIT_Change_Request.Status, " _
& "GIT_Change_Request.Approval_status, " _
& "GIT_Change_Request.Priority, " _
& "GIT_Change_Request.Custom_Field_Two, " _
& "GIT_Change_Request.Change_Type_Classification, " _
& "GIT_Change_Request.Date_time_entered, " _
& "GIT_Change_Request.Date_time_needed, " _
& "GIT_Change_Request.Date_time_completed, " _
& "GIT_Change_Request.Date_time_last_updated, " _
& "GIT_Change_Request.Request_type, " _
& "GIT_Change_Request.Platform, " _
& "GIT_Change_Request.Change_Impact_Classification, " _
& "GIT_Change_Request.Emergency_Change, " _
& "GIT_Change_Request.Benefit, " _
& "GIT_Change_Request.Risk, " _
& "GIT_Change_Request.Impact, " _
& "GIT_Change_Request.Impact_GxP_, " _
& "GIT_Change_Request.Impact_service_, " _
& "GIT_Change_Request.Key_item_affected, "
Const RemedySelect = RemedySelect1 _
& "GIT_Change_Request.Bug_Fix_Enhancement, " _
& "GIT_Change_Request.A_Complete, " _
& "GIT_Change_Request.Completion_code, " _
& "GIT_Change_Request.Planned_start__date_time__, " _
& "GIT_Change_Request.Planned_end__date_time__ , " _
& "GIT_Change_Request.Planned_duration__hours__, " _
& "GIT_Change_Request.Actual_start__date_time__, " _
& "GIT_Change_Request.Actual_end__date_time__, " _
& "GIT_Change_Request.Actual_duration__hours__, " _
& "GIT_Change_Request.Hours_of_effort__planned_, " _
& "GIT_Change_Request.Hours_of_effort__actual_, " _
& "GIT_Change_Request.Profile_ID_Used, " _
& "GIT_Change_Request.Pre_Approval, " _
& "GIT_Change_Request.Requester_name, " _
& "GIT_Change_Request.Customer_location, " _
& "GIT_Change_Request.Test_Backoff_plan, " _
& "GIT_Change_Request.Date_Time_Approved_Rejected " _
& "FROM GIT_Change_Request GIT_Change_Request "
Const RemedyWhere = "WHERE (GIT_Change_Request.Assignee_group='GLO/****-COE')" & " "
Const RemedyConnection1 = "ODBC;DSN=RAZOR;ARServer="
Const RemedyConnection2 = ";ARAuthentication=;ARUseUnderscores=1;ARNameReplace=1;SERVER=NotTheServer"
ODBCServer = "yo**02.d49.****.com"
Dim AssigneeGroupODBC As String
AssigneeGroupODBC = "GLO/****-COE"
Pwd = "USER"
Usr = "USER"
sSql = RemedySelect & RemedyWhere & AssigneeGroupODBC
connection = RemedyConnection1 & ODBCServer & ";UID=" & Usr & ";PWD=" & Pwd & RemedyConnection2
Set oQt = ActiveSheet.QueryTables.Add( _
connection:=connection, _
Destination:=Range("a1"), _
Sql:=sSql)
oQt.Refresh
End Sub