Failure on Querytable refresh when SQL > 1023 chars

[shaunk]

Excel 2003, Access 2003.

From Excel VBA, I am refreshing a querytable with output from an SQL statement via an ODBC DSN connection as follows:

Const Connstring = "ODBC;DSN=Client_Adjunct"
 With ActiveSheet.QueryTables.Add(Connection:=Connstring,  Destination:=Range("A1"), Sql:=Sql_Client_Adjunct)
        .RefreshStyle = xlInsertDeleteCells
        .Name = Client_Audit
        .FieldNames = True
        .Refresh BackgroundQuery = True
    End With

I have left out the sql statement for brevity, however, the query is failing on refresh. I copied the sql to Access and it is always failing on the 1024 character. VBA seems to be placing some character in the string at 1024 characters that Access can't handle. I can fix the query in Access by pressing delete after the 1,023 character. I have rejigged the query by moving field names around but the query always fails at the same number of characters i.e 1024.

The code worked under 2000 for both Access and Excel, but has failed since moving to 2003 for both.

Any help appreciated.



The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[SkipVought]

Hi,

I almost never ADD a QueryTable via code. I do it manually.

But I DO, use code to refresh a QT, redirect to another connection or change the SQL command text.

"I copied the sql to Access and it is always failing on the 1024 character."

1024 of the SQL or 1024 on a row containing a value with 1024 characters???

Please EDIT the query in the QT and COPY the SQL code and paste here.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[shaunk]

I executed a debug.print statement to capture the SQL statement as it is executed in refreshing the QT. No matter how I arrange the fields ordinally, the statement always fails in Access on the 1024th character of the SQL statement. Once I correct the problem SQL in Access, the SQL is executed successfully and there are no fields returned anywhere near 1024 characters in length.
All but the first four fields are of datatype 'byte'.

The SQL statement is:

SELECT LDDI_Description AS [LDDI Collection],SRE.SR AS Ref,OU.abbreviated_name AS Centre,Profession_Description AS Profession,MRN,COB,Lang,Interpreter,GP,Chime_Alerts_Entered AS [Alerts-Chime],HCF_Alerts_Entered AS [Alerts-HCF],SR_Episode_Correctness AS [SR-Correct Episode],SR_Intake_Phase AS [SR-Intake Phase],SR_Assessment_Phase AS [SR-Assessment Phase],SR_Treatment_Phase AS [SR-Treatment Phase],SR_Closure_Phase AS [SR-Closure Phase],Diary_Accuracy AS [Diary-Accuracy],Diary_Activity_Templates AS [Diary-Templates Used],Chime_PP_Home_Safety,Chime_PP_Rights_Resps,Chime_PP_Interpreter_Checklist,Chime_PP_Interpreter_Used,Chime_PP_Release_Of_Information,Chime_MP_Created,Chime_MP_Appropriate,Chime_MP_Use_Of_Templates,Chime_MP_Clinical_Review,Chime_MP_Clinical_Review_Note,Chime_MP_All_Interventions_Entered AS [Chime MP All Interventions],Chime_CN_RIC_Template_Used,Chime_CN_Created_In_Diary,Chime_CN_Opening_Summary,Chime_CN_Closing_Summary,Chime_CN_Minimum_Standards_Met,Chime_DD_Hacc_Patients_Identified AS [HACC Ide
ntified],Chime_DD_Hacc_Data_Accuracy AS [HACC Data Accuracy],Chime_DD_DVA_Data_Accuracy AS [Chime DVA Data Accuracy],HCF_MR_Cover_Name,HCF_MR_Cover_MRN,HCF_MR_Cover_Service_Name,HCF_MR_Cover_Archive_Sticker,HCF_MR_Cover_EHR_Sticker,HCF_Thickness,HCF_Correct_Form_Order,HCF_Additional_Doco,HCF_DVA_Data_Accuracy AS [HCF DVA Data Accuracy],HCF_Clinical_Progress_Notes,HCF_Clinical_Management_Plan,HCF_Clinical_Assessments,HCF_Clinical_Risk_Assessment AS [HCF Home Risk Assessment],HCF_Clinical_Minimum_Standards_Met AS [HCF Minimum Standards Met],HCF_Clinical_MP_All_Interventions_Entered AS [HCF MP All Interventions],iPM_Document_Exists,iPM_Document_Correct_Volumes,iPM_Duplicate_MRN,iPM_Updated_Demographics,iPM_Updated_GP FROM tblLDDI_Type LTY INNER JOIN (((dbo_service_request SR INNER JOIN dbo_organisation_unit OU ON SR.owner_org_unit_id = OU.org_unit_id)  INNER JOIN tblSR_Extra_Items SRE ON SR.service_request_id = SRE.SR) INNER JOIN  tblProfession PRO ON SRE.Profession_Fkey = PRO.Profession_Type)  ON LTY.LDDI_Type
 = SRE.LDDI_Type_Fkey  WHERE LTY.LDDI_Description = 'Carenet Clinical Audit 2007'

The code to create the SQL statement is:

Dim Sql_Client_Adjunct1 As String
Dim Sql_Client_Adjunct2 As String
  
Sql_Client_Adjunct1 = _
        "SELECT LDDI_Description AS [LDDI Collection],SRE.SR AS Ref," & _
        "OU.abbreviated_name AS Centre,Profession_Description AS Profession," & _
        "MRN,COB,Lang,Interpreter,GP,Chime_Alerts_Entered AS [Alerts-Chime]," & _
        "HCF_Alerts_Entered AS [Alerts-HCF],SR_Episode_Correctness AS [SR-Correct Episode]," & _
        "SR_Intake_Phase AS [SR-Intake Phase],SR_Assessment_Phase AS [SR-Assessment Phase]," & _
        "SR_Treatment_Phase AS [SR-Treatment Phase],SR_Closure_Phase AS [SR-Closure Phase]," & _
        "Diary_Accuracy AS [Diary-Accuracy],Diary_Activity_Templates AS [Diary-Templates Used]," & _
        "Chime_PP_Home_Safety,Chime_PP_Rights_Resps,Chime_PP_Interpreter_Checklist," & _
        "Chime_PP_Interpreter_Used,Chime_PP_Release_Of_Information,Chime_MP_Created," & _
        "Chime_MP_Appropriate,Chime_MP_Use_Of_Templates,Chime_MP_Clinical_Review," & _
        "Chime_MP_Clinical_Review_Note,Chime_MP_All_Interventions_Entered AS [Chime MP All Interventions]," & _
        "Chime_CN_RIC_Template_Used,Chime_CN_Created_In_Diary,Chime_CN_Opening_Summary," & _
        "Chime_CN_Closing_Summary,Chime_CN_Minimum_Standards_Met,Chime_DD_Hacc_Patients_Identified AS [HACC Identified]," & _
        "Chime_DD_Hacc_Data_Accuracy AS [HACC Data Accuracy]," & _
        "Chime_DD_DVA_Data_Accuracy AS [Chime DVA Data Accuracy],"
    Sql_Client_Adjunct2 = _
        "HCF_MR_Cover_Name,HCF_MR_Cover_MRN,HCF_MR_Cover_Service_Name," & _
        "HCF_MR_Cover_Archive_Sticker,HCF_MR_Cover_EHR_Sticker,HCF_Thickness,HCF_Correct_Form_Order,HCF_Additional_Doco," & _
        "HCF_DVA_Data_Accuracy AS [HCF DVA Data Accuracy],HCF_Clinical_Progress_Notes,HCF_Clinical_Management_Plan," & _
        "HCF_Clinical_Assessments,HCF_Clinical_Risk_Assessment AS [HCF Home Risk Assessment]," & _
        "HCF_Clinical_Minimum_Standards_Met AS [HCF Minimum Standards Met]," & _
        "HCF_Clinical_MP_All_Interventions_Entered AS [HCF MP All Interventions]," & _
        "iPM_Document_Exists,iPM_Document_Correct_Volumes,iPM_Duplicate_MRN,iPM_Updated_Demographics," & _
        "iPM_Updated_GP" & _
        " FROM tblLDDI_Type LTY INNER JOIN (((dbo_service_request SR INNER JOIN dbo_organisation_unit OU" & _
        " ON SR.owner_org_unit_id = OU.org_unit_id) " & _
        " INNER JOIN tblSR_Extra_Items SRE ON SR.service_request_id = SRE.SR) INNER JOIN " & _
        " tblProfession PRO ON SRE.Profession_Fkey = PRO.Profession_Type) " & _
        " ON LTY.LDDI_Type = SRE.LDDI_Type_Fkey " & _
        " WHERE LTY.LDDI_Description = " & "'" & Project_Name & "'"
    
    Sql_Client_Adjunct = Sql_Client_Adjunct1 & Sql_Client_Adjunct2

Thanks

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[JerryKlmns]

shaunk

The lenght of the SQL statement, without then value of Project_Name varable, is 2100 characters! I think the limit is 1024 which you have exceeded!

If you create a smaller one I guess you would be just fine. How would this be accomplished? Maybe by creating couple of queries to have your fields aliased, and a final query to join them as you do. Then

Sql_Client_Adjunct = "SELECT * FROM FinalQuery WHERE [LTY-LDDI_Description Aliased Field] = '" & Project_Name & "';"