PDA

View Full Version : Solved: Ado connection, SQL String error



White_Nova
01-22-2008, 02:48 AM
Hi all

I have a database in access being updated by Excel.
I would like to specify that decimal numbers (two decimal places) also be taken to the databse, i however get this error:

Scaling of demical value resulted in data truncation...

Please help, i have configured the access DB to allow decimal values...

Thanks for all your help...

Jan Karel Pieterse
01-22-2008, 08:03 AM
Show us the SQL statement as you're trying to send to the ADO connection please.

White_Nova
01-23-2008, 12:56 AM
Hi There

Here is the code:
The First 5 need to be text and the rest decimal...


Sub expenseinput()

Sheets("Expense Input").Unprotect Password:="age"

Range("L4").FormulaR1C1 = ""
If Range("B15") = "" Then
MsgBox "Please check you have assigned VAT"
Else
If Range("G15") = "" Then
MsgBox "Please Check Monthly/Periodic Options"
Else
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & ActiveWorkbook.Path & "\BI Budget.mdb"

Set rs = New ADODB.Recordset
rs.Open "tblexpenses", cn, adOpenKeyset, adLockOptimistic, adCmdTable

r = 15
Do While Len(Range("C" & r).Formula) > 0

With rs
.AddNew

.Fields("entityID") = Range("F2").Value
.Fields("lineno") = Range("A" & r).Value
.Fields("expenseID") = Range("F6").Value
.Fields("vat") = Range("B" & r).Value
.Fields("desc") = Range("C" & r).Value
.Fields("budgettotal") = Range("D" & r).Value
.Fields("actualtotal") = Range("E" & r).Value
.Fields("budget1") = Range("J" & r).Value
.Fields("actual1") = Range("K" & r).Value
.Fields("budget2") = Range("L" & r).Value
.Fields("actual2") = Range("M" & r).Value
.Fields("budget3") = Range("N" & r).Value
.Fields("actual3") = Range("O" & r).Value
.Fields("budget4") = Range("P" & r).Value
.Fields("actual4") = Range("Q" & r).Value
.Fields("budget5") = Range("R" & r).Value
.Fields("actual5") = Range("S" & r).Value
.Fields("budget6") = Range("T" & r).Value
.Fields("actual6") = Range("U" & r).Value
.Fields("budget7") = Range("V" & r).Value
.Fields("actual7") = Range("W" & r).Value
.Fields("budget8") = Range("X" & r).Value
.Fields("actual8") = Range("Y" & r).Value
.Fields("budget9") = Range("Z" & r).Value
.Fields("actual9") = Range("AA" & r).Value
.Fields("budget10") = Range("AB" & r).Value
.Fields("actual10") = Range("AC" & r).Value
.Fields("budget11") = Range("AD" & r).Value
.Fields("actual11") = Range("AE" & r).Value
.Fields("budget12") = Range("AF" & r).Value
.Fields("actual12") = Range("AG" & r).Value
.Fields("check1") = Range("K10").Value
.Fields("check2") = Range("M10").Value
.Fields("check3") = Range("O10").Value
.Fields("check4") = Range("Q10").Value
.Fields("check5") = Range("S10").Value
.Fields("check6") = Range("U10").Value
.Fields("check7") = Range("W10").Value
.Fields("check8") = Range("Y10").Value
.Fields("check9") = Range("AA10").Value
.Fields("check10") = Range("AC10").Value
.Fields("check11") = Range("AE10").Value
.Fields("check12") = Range("AG10").Value

.update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End If
End If

Sheets("Expense Input").Protect Password:="age"

End Sub