CODEWITHSUNDEEP
vbaexcelado.netado
mountainclimber11
mountainclimber11

Reputation: 1400

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

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://learn.microsoft.com/en-us/sql/ado/reference/ado-api/cursortype-property-ado Update recordset without updating database

Upvotes: 3

Views: 4612

Answers (1)

RADO
RADO

Reputation: 8148

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

rs.Fields.Append "New_Field", adDouble

Upvotes: 3

Related Questions