ADODB Excel to Access Push loosing decimals

[Turbo613]

Hi, I have data in an Excel 03 worksheet that I export to an Access 03 table using an ADODB.Connection. When the data is in Excel it has a decimal (e.g 100.4). But when it gets to Access, it is not showing the decimal. I have the Access data type set to Number, the field Size set to formatted as Long Integer, the format set to standard, and the decimal places set to 1.

Any ideas on the issue? I'll post this in the access discussion group as well. PLEASE help... Here is my code in Excel...

Sub ADOFromExcelToAccess()
'Select the sheet you want to push data from
Sheets("Data").Select
Range("A1").Select
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=I:\Operations and Support\Rpt & Analysis\Automation\Claims\Ben 501\Ben 501 Pivot\Ben501Office.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Tbl_Ben501OfficeData", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("OfcNumb") = Range("A" & r).Value
.Fields("OfcName") = Range("B" & r).Value
.Fields("DateRng") = Range("C" & r).Value
.Fields("DateOfRpt_F") = Range("D" & r).Value
.Fields("Category") = Range("E" & r).Value
.Fields("Measure") = Range("F" & r).Value
.Fields("0_3") = Range("G" & r).Value
.Fields("4_14") = Range("H" & r).Value
.Fields("15") = Range("I" & r).Value
.Fields("16_21") = Range("J" & r).Value
.Fields("22_30") = Range("K" & r).Value
.Fields("31_45") = Range("L" & r).Value
.Fields("45Plus") = Range("M" & r).Value
.Fields("Total") = Range("N" & r).Value
.Fields("N_AA") = Range("O" & r).Value
.Fields("N_NonAA") = Range("P" & r).Value
.Fields("N_PendResp") = Range("Q" & r).Value
.Fields("N_Total") = Range("R" & r).Value
.Fields("MA_Inv") = Range("S" & r).Value
.Fields("MA_InvClms") = Range("T" & r).Value
.Fields("M_NonAA") = Range("U" & r).Value
.Fields("M_PendResp") = Range("V" & r).Value
.Fields("M_Total") = Range("W" & r).Value
.Fields("M_AA") = Range("X" & r).Value

' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[pgc01]

Hi Turbo613

I have the Access data type set to Number, the field Size set to formatted as Long Integer, the format set to standard, and the decimal places set to 1.

What do you mean by this? If you are defining the type as Integer how can it have decimals?

I think you should define the field with a type that allows decimals (ex. single, double).

Hope this helps
PGC

 

[Turbo613]

Thanks very much, I'll give it a try. I missed that day in algebra and should have read the Access definitions better, I assumed, since it gave a .0 that it was in decimal format, but Duh! Integer = whole number, now I remember. Thx again...