How to change the data type of an ADO recordset field?

Question

In VBA I have a collection of ADO recordsets that I want to do calculations on and store the values in the recordset in memory while the user is using the form.

My issues: after I do a calculation on, say, and integer, I get a double/float. When I try to store that in the recordset it is cast/changed to an integer. I want the precision of the double/float after the calculation to be preserved.

My idea: change the data type of the field I am calculation and storing. I'm not seeing how to do it. How do I do that? Can I do that?

Ex:

Option Explicit
Sub test()

Dim rs As ADODB.Recordset
Dim sql As String
sql = "select * from test;"
Set rs = frsGetPgRecordset(sql)

Do While rs.EOF = False
    Debug.Print rs!x & "<- original"
    Debug.Print Application.WorksheetFunction.Log(rs!x) & "<- what I want stored"
    rs.Fields("x") = Application.WorksheetFunction.Log(rs!x)
    Debug.Print rs!x & "<- what I get"
    rs.MoveNext
Loop

End Sub

I get:

8<- original
0.903089986991944<- what I want stored
1<- what I get
20<- original
1.30102999566398<- what I want stored
1<- what I get

Note, this is not changing the database because in frsGetPgRecordset (not provide because it's not critical to the question) I have:

rs.CursorType = adOpenKeyset  
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic

You can get more info here on that part, because people will ask: https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/cursortype-property-ado Update recordset without updating database

So you want the logarithm of the value stored? If 8 was the base what was the value? Also, must it be stored in the RS, can you write the results to a double array? — Absinthe
I don't think you can change data types, so the best workaround would be what @RADO suggested. — Brian M Stafford

RADO RADO · Accepted Answer · 2017-06-06T19:26:35

I would just store calculations results in a new field instead of trying to change an existing field:

rs.Fields.Append "New_Field", adDouble

How to change the data type of an ADO recordset field?

1 Answers