HELP: MS Access-Oracle-MS Excel Integration corrupts date values (1 Viewer)

[tranchemontaigne]

I have a problem where I dynamically write a piece of SQL and then pass that SQL straight through to Oracle using VBA code to open a new Oracle connection. All works fine here.

Oracle then returns a result set that gets written to an ADODB recordset object. All works fine here.

The results of the ADODB recordset object are then written to an array in memory and the array results are posted into cells in MS Excel. When dates originate some time ago (e.g. 1912, 1920) the process of writing those date values into a MS Excel cell causes the century to change (e.g. 1912 becomes 2012, 1920 becomes 2020).

How can I code around this problem? The array storing recordset results is of type variant. The variant value is written to MS Excel. Is there a smarter way to address this problem?

 

[tranchemontaigne]

problem only seems to occur with dates before 1/1/1930

 

[mdlueck]

I have a problem where I dynamically write a piece of SQL and then pass that SQL straight through to Oracle using VBA code to open a new Oracle connection. All works fine here.

Oracle then returns a result set that gets written to an ADODB recordset object. All works fine here.

You have gone into the Watches window with watches placed on these objects and verified the correct date is in the object attribute?

The results of the ADODB recordset object are then written to an array in memory and the array results are posted into cells in MS Excel. When dates originate some time ago (e.g. 1912, 1920) the process of writing those date values into a MS Excel cell causes the century to change (e.g. 1912 becomes 2012, 1920 becomes 2020).

Looks like right there might be a place where data integrity gets lost.

Is there no way to map directly from the ADODB.RecordSet to MS Excel cells? Could you use automation to have Access VBA populate the Excel sheet for you? Via automation, a populated ADODB.RecordSet is able to be sent to Excel in one automation call. And the Exel sheet could be preformatted, such as for the date column, it could be pre-configured as "date" before data gets plugged into the sheet. I do this by editing the template file that Access will use to populate/fill in the sheet. So that is one more way to verify that integrity of date data is preserved.

 

[mdlueck]

problem only seems to occur with dates before 1/1/1930

And your data has four digit years?

 

[tranchemontaigne]

Michael, thanks again for the quick response. I have placed watches in and have learned the following:

(1) Oracle is returning dates in a string format similar to '31-Dec-29'. This is the value being stored in the array.

I suspect that this is where the century is being altered. Is there a way to get Oracle to return dates with full year values? Here's ome core elements of my code

NOTE: strSQL is received as an argument

    'open with a pass through query to the EDW
    strConnection = "Driver={Oracle in OraClient11g_home1};"
    strConnection = strConnection & "Dbq=NHEDWP;"
    strConnection = strConnection & "Uid=" & varEDW_Credentials(0) & ";"
    strConnection = strConnection & "Pwd=" & varEDW_Credentials(1) & ";"

    Set rst1 = New ADODB.Recordset
    rst1.Open strSQL, strConnection, adOpenForwardOnly, adLockOptimistic
 
    'create MS Excel file
    'If WS = Nothing Then
        Set XL = CreateObject("excel.application")
    
        'set number of worksheets in workbook
        XL.SheetsInNewWorkbook = 1
        
        'make MS Excel file visible
        XL.Visible = True
        
        'add defined number of worksheet pages to workbook
        Set WB = XL.Workbooks.Add
        
        Set WS = WB.Worksheets(1)
    'End If
    
strCodeBlock = "determine result set size"
    varA = rst1.GetRows()
    ReDim varC(UBound(varA, 2), UBound(varA, 1))
    
strCodeBlock = "populate an array with cell values"
    For intK = 0 To UBound(varA, 1)
        For intJ = 0 To UBound(varA, 2)
            varC(intJ, intK) = varA(intK, intJ)
        Next intJ
    Next intK
    
strCodeBlock = "matrix transposition"
    lnN = UBound(varA, 2) + 1
    lnM = UBound(varA, 1) + 1
    
    WS.Range(WS.Cells(lnY, lnX), WS.Cells(lnN + lnY - 1, lnM + lnX - 1)) = varC
    
strCodeBlock = "column headers inserted if necessary"
    If blHeaders = True Then
        WS.Range(WS.Cells(lnY, lnX), WS.Cells(lnN + lnY - 1, lnM + lnX - 1)).Rows(1).Insert
        For intJ = 0 To lnM - 1
            WS.Cells(lnY, intJ + lnX).Value = rst1.Fields(intJ).Name
        Next intJ
    
    End If

I'm trying to wrap my head around a dynamic test to see if a value captured by rst1.GetRows() is actually a date and then format it at run time with a 4 digit year. Is this the best approach?

 

[mdlueck]

(1) Oracle is returning dates in a string format similar to '31-Dec-29'. This is the value being stored in the array.

I suspect that this is where the century is being altered. Is there a way to get Oracle to return dates with full year values?

It all depends on what actual datatype is used in the Oracle table. If it is a string with only two digit years, then you are stuck with interpreting the string to a date on your own, since automagic is guessing wrong.

At least you now know where you are getting bit.

 

[tranchemontaigne]

Michael,

Here's the solution:

Within the function that builds the dynamic SQL I added the following line:

"NVL(TO_CHAR(DECD_BRTH_DT,'MM/DD/YYYY'),' ') AS DOB"

Thanks for your help. You've taught me more about working with Oracle than anyone to date. *GRIN*

 

[mdlueck]

You are most welcome, tranchemontaigne. I am glad to hear this error is not history.