SQL Connection Question

jonnyp138

Board Regular
Joined
May 2, 2015
Messages
50
Apologies if this is the wrong place to ask this but I have inherited a spreadsheet which pulls data from a SQL database and all works fine apart from when the query has been ran and the data is visible in Excle one of the columns that contains all details only displays the first 255 characters and I am not sure what I need to do to make sure all the data comes through?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
what version of excel

what is your excel SQL script
 
Upvote 0
Code:
Sub SQL_Updater_Testing()
Call Unhide_Extraction
Sheets("Extraction").Select
Range("A4").Select
ActiveCell.FormulaR1C1 = "=TODAY()-5"
'

ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = True

Calculate
If IsEmpty(Range("Extraction!A4")) Then
    MsgBox "You must enter a start date", , "Error"
    ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False
Else
    
If (Range("Extraction!c3") - Range("Extraction!c4")) < -397 Then
    MsgBox "You cannot search for more than 13 months of data", , "Error"
    ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False
Else

If IsEmpty(Range("Extraction!A2")) And Range("Extraction!c3") < -366 And Range("Extraction!c2") = 0 Then
        MsgBox "You can only search for 6 months across all customers without further criteria", , "Error"
        ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False
Else

If IsEmpty(Range("Extraction!A2")) And (Range("Extraction!c3") - Range("Extraction!c4")) < -366 And Range("Extraction!c2") > 0 Then
        MsgBox "You can only search for 6 months across all customers", , "Error"
        ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False
Else

Dim sqlcmdstring As String
Dim sqlcmdstring1 As String
Dim sqlcmdstring2 As String
Dim sqlcmdstring3 As String
Dim sqlcmdstring4 As String
Dim sqlcmdstring5 As String
Dim sqlcmdstring6 As String
Dim sqlcmdstring7 As String
Dim sqlcmdstring8 As String
Dim sqlcmdstring9 As String
Dim sqlcmdstring10 As String
Dim sqlcmdstring11 As String
Dim sqlcmdstring12 As String
Dim sqlcmdstring13 As String
Dim sqlcmdstring14 As String
Dim sqlcmdstring15 As String
Dim sqlcmdstring16 As String
Dim sqlcmdstring17 As String
Dim sqlcmdstring18 As String
Dim sqlcmdstring19 As String
Dim sqlcmdstring20 As String
Dim sqlcmdstring21 As String
Dim sqlcmdstring22 As String
Dim sqlcmdstring23 As String
Dim sqlcmdstring24 As String
Dim sqlcmdstring25 As String
Dim sqlcmdstring26 As String
Dim sqlcmdstring27 As String
Dim sqlcmdstring28 As String
Dim sqlcmdstring29 As String
Dim sqlcmdstring30 As String
    
sqlcmdstring1 = "declare @Days as int"
sqlcmdstring2 = "declare @EndDay as int"
sqlcmdstring3 = "set @Days = '%2'"
sqlcmdstring4 = "set @EndDay = '%9'"
sqlcmdstring5 = "declare @colselect as varchar"
sqlcmdstring6 = "set @colselect = '%A'"
sqlcmdstring7 = "declare @colselect2 as varchar"
sqlcmdstring8 = "set @colselect2 = '%C'"
sqlcmdstring9 = "SELECT inc_number,inc_affected_user_company,inc_brief_description,inc_description,inc_resolve,inc_affected_user_department,"
sqlcmdstring10 = "inc_resolve_product,inc_affected_user_site,inc_affected_user_forename,inc_affected_user_organisation,"
sqlcmdstring11 = "inc_affected_user_surname,inc_report_date,submit_date,inc_resolve_date,Submit_By,"
sqlcmdstring12 = "inc_resolve_product_category_2,inc_resolve_product_category_3,inc_resolve_category_1,inc_resolve_category_2,inc_resolve_time_onhold,"
sqlcmdstring13 = "inc_resolve_category_3,inc_current_assigned_group,inc_current_assignee,inc_resolve_time_remainder,"
sqlcmdstring14 = "inc_service_type,inc_impact,inc_urgency,inc_status,inc_resolve_cause,inc_report_method_inbound"
sqlcmdstring15 = "FROM SLAM.dbo.incident_information"
sqlcmdstring16 = "WHERE inc_report_date >=(dateadd(d,@Days,convert(VARCHAR(10),getdate(),10))) and inc_report_date <=(dateadd(d,@EndDay,convert(VARCHAR(10),getdate(),10)))"

If IsEmpty(Range("Extraction!A2")) Then
Else
    sqlcmdstring17 = "And (inc_affected_user_company like '%' + '%1' + '%')"
End If



If IsEmpty(Range("Extraction!A7")) Then
Else
    sqlcmdstring18 = "And (inc_brief_description like '%' + '%3' + '%')"
End If



If IsEmpty(Range("Extraction!B7")) Then
Else
    sqlcmdstring19 = "And (inc_description like '%' + '%4' + '%')"
End If



If IsEmpty(Range("Extraction!A10")) Then
Else
    sqlcmdstring20 = "And (inc_resolve like '%' + '%5' + '%')"
End If



If IsEmpty(Range("Extraction!A13")) Then
Else
    sqlcmdstring21 = "And (inc_asset_tags like '%' + '%6' + '%')"
End If



If IsEmpty(Range("Extraction!B13")) Then
Else
    sqlcmdstring22 = "And (inc_affected_user_site like '%' + '%7' + '%')"
End If
    
    
    
If IsEmpty(Range("Extraction!B10")) Then
Else
    sqlcmdstring23 = "And (inc_resolve_product like '%' + '%8' + '%')"
End If



If IsEmpty(Range("Extraction!A16")) Then
Else
        
        sqlcmdstring24 = "And (%A like '%' + '%B' + '%')"
End If



If IsEmpty(Range("Extraction!B16")) Then
Else
        
        sqlcmdstring25 = "And (%C NOT like '%' + '%D' + '%')"
End If


  
    

'Where Inc Status Like New, Assigned, Pending or in progress
sqlcmdstring29 = "And (inc_status IN ('new', 'assigned', 'In Progress', 'Pending', 'Closed', 'Resolved', 'Cancelled'))"
'(inc_status like '%' + '%F' + '%' OR inc_status like '%' + '%G' + '%' OR inc_status like '%' + '%H' + '%' OR inc_status like '%' + '%I' + '%')"
'Where Inc Status Like New, Assigned, Pending, in progress, closed, cancelled or resolved
'sqlcmdstring28 = "And (inc_status like '%' + '%F' + '%' OR inc_status like '%' + '%G' + '%' OR inc_status like '%' + '%H' + '%' OR inc_status like '%' + '%I' + '%' OR inc_status like '%' + '%J' + '%' OR inc_status like '%' + '%K' + '%' OR inc_status like '%' + '%L' + '%')"

'Where Inc Status Like closed, cancelled or resolved
'sqlcmdstring28 = "And (inc_status like '%' + '%J' + '%' OR inc_status like '%' + '%K' + '%' OR inc_status like '%' + '%L' + '%')"




'sqlcmdstring29 = "Or (inc_status like '%' + '%G' + '%')"
'sqlcmdstring30 = "Or (inc_status like '%' + '%H' + '%')"



sqlcmdstring = sqlcmdstring1 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring2 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring3 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring4 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring5 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring6 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring7 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring8 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring9 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring10 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring11 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring12 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring13 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring14 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring15 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring16 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring17 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring18 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring19 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring20 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring21 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring22 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring23 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring24 & Chr(13) & "" & Chr(10) & _
               sqlcmdstring25 & Chr(13) & "" & Chr(10) & sqlcmdstring26 & Chr(13) & "" & Chr(10) & sqlcmdstring27 & Chr(13) & "" & Chr(10) & sqlcmdstring28 & Chr(13) & "" & Chr(10) & sqlcmdstring29 '& Chr(13) & "" & Chr(10) & sqlcmdstring30
               

sqlcmdstring = Replace(sqlcmdstring, "%1", Range("Extraction!A2").Value)
sqlcmdstring = Replace(sqlcmdstring, "%2", Range("Extraction!c3").Value)
sqlcmdstring = Replace(sqlcmdstring, "%3", Range("Extraction!A35").Value)
sqlcmdstring = Replace(sqlcmdstring, "%4", Range("Extraction!A34").Value)
sqlcmdstring = Replace(sqlcmdstring, "%5", Range("Extraction!A32").Value)
sqlcmdstring = Replace(sqlcmdstring, "%6", Range("Extraction!A31").Value)
sqlcmdstring = Replace(sqlcmdstring, "%7", Range("Extraction!A30").Value)
sqlcmdstring = Replace(sqlcmdstring, "%8", Range("Extraction!A37").Value)
sqlcmdstring = Replace(sqlcmdstring, "%9", Range("Extraction!C4").Value)
sqlcmdstring = Replace(sqlcmdstring, "%A", Range("Extraction!A16").Value)
sqlcmdstring = Replace(sqlcmdstring, "%B", Range("Extraction!A17").Value)
sqlcmdstring = Replace(sqlcmdstring, "%C", Range("Extraction!B16").Value)
sqlcmdstring = Replace(sqlcmdstring, "%D", Range("Extraction!A36").Value)
sqlcmdstring = Replace(sqlcmdstring, "%E", Range("Extraction!A19").Value)
sqlcmdstring = Replace(sqlcmdstring, "%F", Range("Data_Validation!G7").Value)
sqlcmdstring = Replace(sqlcmdstring, "%G", Range("Data_Validation!G8").Value)
sqlcmdstring = Replace(sqlcmdstring, "%H", Range("Data_Validation!G9").Value)
sqlcmdstring = Replace(sqlcmdstring, "%I", Range("Data_Validation!G10").Value)
sqlcmdstring = Replace(sqlcmdstring, "%J", Range("Data_Validation!G11").Value)
sqlcmdstring = Replace(sqlcmdstring, "%K", Range("Data_Validation!G11").Value)
sqlcmdstring = Replace(sqlcmdstring, "%L", Range("Data_Validation!G13").Value)
sqlcmdstring = Replace(sqlcmdstring, "%M", Range("Extraction!A18").Value)
sqlcmdstring = Replace(sqlcmdstring, "%N", Range("Extraction!A22").Value)
sqlcmdstring = Replace(sqlcmdstring, "%O", Range("Extraction!A23").Value)
sqlcmdstring = Replace(sqlcmdstring, "%P", Range("Extraction!A24").Value)
sqlcmdstring = Replace(sqlcmdstring, "%Q", Range("Extraction!A25").Value)
sqlcmdstring = Replace(sqlcmdstring, "%R", Range("Extraction!A26").Value)
sqlcmdstring = Replace(sqlcmdstring, "%S", Range("Extraction!A27").Value)
sqlcmdstring = Replace(sqlcmdstring, "%T", Range("Extraction!A28").Value)
sqlcmdstring = Replace(sqlcmdstring, "%U", Range("Extraction!A29").Value)
sqlcmdstring = Replace(sqlcmdstring, "%V", Range("Extraction!A20").Value)
sqlcmdstring = Replace(sqlcmdstring, "%W", Range("Extraction!A40").Value)
sqlcmdstring = Replace(sqlcmdstring, "%AD", Range("Extraction!A38").Value)
sqlcmdstring = Replace(sqlcmdstring, "%Y", Range("Extraction!A33").Value)
sqlcmdstring = Replace(sqlcmdstring, "%Z", Range("Extraction!A21").Value)
sqlcmdstring = Replace(sqlcmdstring, "%AB", Range("Extraction!A41").Value)
sqlcmdstring = Replace(sqlcmdstring, "%AC", Range("Extraction!A39").Value)
sqlcmdstring = Replace(sqlcmdstring, "%AE", Range("Extraction!A42").Value)





    With ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = sqlcmdstring
        .Refresh
 End With
 

End If
End If
End If
End If
End Sub

ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top