{"id":18470,"date":"2022-10-31T23:41:26","date_gmt":"2022-10-31T18:11:26","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/"},"modified":"2022-10-31T23:41:26","modified_gmt":"2022-10-31T18:11:26","slug":"solved-sql-query-output-in-vba-is-different-than-in-sql-oracle","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/","title":{"rendered":"[Solved] SQL Query output in VBA is different than in SQL Oracle"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

The problem is CopyFromRecordset<\/code> – it’s truncating at 255 chars, and it’s not the only Excel.Range method that does that.<\/p>\n

The question is: do I have a method that doesn’t? And do you have an an OLEDB driver that’s doing it to your Recordset before you even get to the stage of writing to the range?<\/p>\n

You should loop through your recordset, in VBA, and check the offending field in VBA<\/strong> for a value exceeding 255 chars in length. If the fields are truncated already, try using the native Oracle Client drivers in your connection string, instead of the Microsoft Oracle OLEDB provider – Connections.com will have the information.<\/p>\n

Once you know that the recordset actually contains your data, without truncation, try CopyFromRecordset again. I don’t actually expect it to write a field exceeding 255 chars in length, but it’s been a while since I encountered the error, it might have been fixed, and it’s always nice to give a pessimist a pleasant surprise.<\/p>\n

Next up: <\/p>\n

A VBA Substitute for CopyFromRecordset<\/h2>\n

There are three tasks here: <\/p>\n

    \n
  1. Populate a VBA array variant with the data using
    \nRecordset.GetRows()<\/code>; <\/li>\n
  2. Transpose the array, because GetRows is the wrong way ’round for
    \nExcel;<\/li>\n
  3. Size up a target range and write the array as
    \nRange.Value\u00a0=\u00a0Array<\/code>, a repetitive task which
    \nshould be automated in an ArrayToRange() routine.<\/li>\n<\/ol>\n

    …And maybe some ancillary work with writing the field names, but I’m ignoring that in a short answer.<\/p>\n

    The end result is that you run this code:<\/p>\n<\/p>\n

    \n    ArrayToRange rngTarget, ArrayTranspose(rst.GetRows)
    <\/code><\/pre>\n
    Transposing the array is trivial, but here it is anyway:\n<\/p>\n
    \nPublic Function ArrayTranspose(InputArray As Variant) As Variant\nApplication.Volatile False
    \nDim arrOutput As Variant
    \nDim i As Long\nDim j As Long\nDim iMin As Long\nDim iMax As Long\nDim jMin As Long\nDim jMax As Long
    \niMin = LBound(InputArray, 1)\niMax = UBound(InputArray, 1)\njMin = LBound(InputArray, 2)\njMax = UBound(InputArray, 2)
    \nReDim arrOutput(jMin To jMax, iMin To iMax)
    \nFor i = iMin To iMax\n    For j = jMin To jMax\n        arrOutput(j, i) = InputArray(i, j)\n    Next j\nNext i
    \nArrayTranspose = arrOutput
    \nEnd Function
    <\/code><\/pre>\n
    …And ArrayToRange is trivial if you don’t add checks for array dimensions and preserving formulas in the target cells: the essential point is that you can write your data in a single ‘hit’ if the dimensions of the range exactly match the dimensions of the array:<\/p>\n
    \nPublic Sub ArrayToRange(rngTarget As Excel.Range, InputArray As Variant)\n' Write an array to an Excel range in a single 'hit' to the sheet\n' InputArray should be a 2-Dimensional structure of the form Variant(Rows, Columns)
    \n' The target range is resized automatically to the dimensions of the array, with\n' the top left cell used as the start point.
    \n' This subroutine saves repetitive coding for a common VBA and Excel task.
    \n' Author: Nigel Heffernan  http:\/\/Excellerando.blogspot.com<\/a>

    \nOn Error Resume Next
    \nDim rngOutput As Excel.Range
    \nDim iRowCount   As Long\nDim iColCount   As Long
    \niRowCount = UBound(InputArray, 1) - LBound(InputArray, 1)\niColCount = UBound(InputArray, 2) - LBound(InputArray, 2)
    \nWith rngTarget.Worksheet
    \n    Set rngOutput = .Range(rngTarget.Cells(1, 1), _\n                           rngTarget.Cells(iRowCount + 1, iColCount + 1))
    \n    Application.EnableEvents = False

    \n    rngOutput.Value2 = InputArray
    \n    Application.EnableEvents = True
    \n    Set rngTarget = rngOutput   ' resizes the range This is useful, most<\/em> of the time
    \nEnd With  '  rngTarget.Worksheet
    \nEnd Sub\n<\/pre>\n
    A note of caution: in older versions of Excel (Office 2000, if I recall) the array ‘write’ still truncated to 255 chars. This is no longer a problem; and if you’re still using XL2000, cells containing a string exceeding 255 chars are enough of a problem that you might be glad of the truncation.<\/p>\n<\/p><\/div>\n
    <\/div>\n<\/div>\n
                2<\/span> <\/p><\/div>\n<\/div>\n
    [ad_2]<\/p>\n
    solved SQL Query output in VBA is different than in SQL Oracle <\/p>\n","protected":false},"excerpt":{"rendered":"
    [ad_1] The problem is CopyFromRecordset – it’s truncating at 255 chars, and it’s not the only Excel.Range method that does that. The question is: do I have a method that doesn’t? And do you have an an OLEDB driver that’s doing it to your Recordset before you even get to the stage of writing to … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,838,4468,341,401],"class_list":["post-18470","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-oracle","tag-recordset","tag-sql","tag-vba"],"yoast_head":"\n[Solved] SQL Query output in VBA is different than in SQL Oracle - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL Query output in VBA is different than in SQL Oracle - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] The problem is CopyFromRecordset – it’s truncating at 255 chars, and it’s not the only Excel.Range method that does that. The question is: do I have a method that doesn’t? And do you have an an OLEDB driver that’s doing it to your Recordset before you even get to the stage of writing to ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-31T18:11:26+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL Query output in VBA is different than in SQL Oracle\",\"datePublished\":\"2022-10-31T18:11:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/\"},\"wordCount\":386,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"oracle\",\"recordset\",\"sql\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/\",\"name\":\"[Solved] SQL Query output in VBA is different than in SQL Oracle - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-31T18:11:26+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL Query output in VBA is different than in SQL Oracle\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] SQL Query output in VBA is different than in SQL Oracle - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL Query output in VBA is different than in SQL Oracle - JassWeb","og_description":"[ad_1] The problem is CopyFromRecordset – it’s truncating at 255 chars, and it’s not the only Excel.Range method that does that. The question is: do I have a method that doesn’t? And do you have an an OLEDB driver that’s doing it to your Recordset before you even get to the stage of writing to ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/","og_site_name":"JassWeb","article_published_time":"2022-10-31T18:11:26+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL Query output in VBA is different than in SQL Oracle","datePublished":"2022-10-31T18:11:26+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/"},"wordCount":386,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","oracle","recordset","sql","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/","name":"[Solved] SQL Query output in VBA is different than in SQL Oracle - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-31T18:11:26+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-query-output-in-vba-is-different-than-in-sql-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL Query output in VBA is different than in SQL Oracle"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/18470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=18470"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/18470\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=18470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=18470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=18470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}