Sql Query Output In Vba Is Different Than In Sql Oracle

Having a problem with VBA. I have written sql code (300 lines) which works perfect in SQL giving output: Line Number Date Employee PN Tax 1 1111 2015-10-

Solution 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 the range?

You should loop through your recordset, in VBA, and check the offending field in VBA 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.

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.

Next up:

A VBA Substitute for CopyFromRecordset

There are three tasks here:

1. Populate a VBA array variant with the data using Recordset.GetRows();
2. Transpose the array, because GetRows is the wrong way 'round for Excel;
3. Size up a target range and write the array as Range.Value = Array, a repetitive task which should be automated in an ArrayToRange() routine.

...And maybe some ancillary work with writing the field names, but I'm ignoring that in a short answer.

The end result is that you run this code:

    ArrayToRange rngTarget, ArrayTranspose(rst.GetRows)

Transposing the array is trivial, but here it is anyway:

PublicFunctionArrayTranspose(InputArray As Variant) AsVariantApplication.VolatileFalseDimarrOutputAsVariantDimiAsLongDimjAsLongDimiMinAsLongDimiMaxAsLongDimjMinAsLongDimjMaxAsLongiMin = LBound(InputArray, 1)
iMax = UBound(InputArray, 1)
jMin = LBound(InputArray, 2)
jMax = UBound(InputArray, 2)
ReDimarrOutput(jMin To jMax, iMin To iMax)
Fori = iMinToiMaxForj = jMinTojMaxarrOutput(j, i) = InputArray(i, j)
    NextjNextiArrayTranspose = arrOutputEndFunction

...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:

Public Sub ArrayToRange(rngTarget As Excel.Range, InputArray As Variant)
' Write an array to an Excel range in a single 'hit' to the sheet
' InputArray should be a 2-Dimensional structure of the form Variant(Rows, Columns)
' The target range is resized automatically to the dimensions of the array, with
' the top left cell used as the start point.
' This subroutine saves repetitive coding for a common VBA and Excel task.
' Author: Nigel Heffernan  http://Excellerando.blogspot.com
On Error Resume Next
Dim rngOutput As Excel.Range
Dim iRowCount   As Long
Dim iColCount   As Long
iRowCount = UBound(InputArray, 1) - LBound(InputArray, 1)
iColCount = UBound(InputArray, 2) - LBound(InputArray, 2)
With rngTarget.Worksheet
    Set rngOutput = .Range(rngTarget.Cells(1, 1), _
                           rngTarget.Cells(iRowCount + 1, iColCount + 1))
    Application.EnableEvents = False
    rngOutput.Value2 = InputArray
    Application.EnableEvents = True
    Set rngTarget = rngOutput   ' resizes the range This is useful, most of the time
End With  '  rngTarget.Worksheet
End Sub

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.

Solution 2:

The problem is that the two queries are not identical.

You have the following in your VBA version, but not in your Oracle version:

WHERE inh.invoice_date >= to_date('" & dat1 & "','YYYY.MM.DD') 
AND inh.invoice_date <= to_date('" & dat2 & "','YYYY.MM.DD' ) ) XX   

There may be other differences as well, but that jumped out to me immediately.