Numeric value out of range error

[vbajock]

Using Psql 2000i, SP3:

If I create an append query in Access, and append a record from a clone table that exists in MS Access to a target table that exists in PSQL, everything works fine.

If I do the exact same thing using an ADO connection to PSQL using a VB DO LOOP that appends the data from the same exact source table to the same exact target table, line by line, I get a Numeric Value Out Of Range error and the PSQL table will not update. Does anyone know what I am missing? Here's the code:

Public Function Append_MacOEINQORD()

Dim counter As Long
Dim Loc_source As New ADODB.Recordset
Dim mac_target As New ADODB.Recordset

On Error GoTo err_h


Loc_source.Open "tblOEINQORD", Localdb, adOpenForwardOnly, adLockReadOnly, adCmdTable

mac_target.Open "OEINQORD", Macola, adOpenDynamic, adLockOptimistic, adCmdTable


With Loc_source

Do
mac_target.AddNew
mac_target!INQ_ORD_NO = !INQ_ORD_NO
mac_target!INQ_ORD_TYPE = !INQ_ORD_TYPE
mac_target!INQ_ORD_FLAG = !INQ_ORD_FLAG
mac_target!INQ_ORD_DATE = !INQ_ORD_DATE

mac_target!INQ_ORD_CUST_NO = !INQ_ORD_CUST_NO
mac_target!INQ_ORD_CUST_PO = !INQ_ORD_CUST_PO
mac_target!INQ_ORD_CRDT_ORD_NO = !INQ_ORD_CRDT_ORD_NO
mac_target!INQ_ORD_CRDT_ORD_FLG = !INQ_ORD_CRDT_ORD_FLG
mac_target!FILLER_0001 = !FILLER_0001

====>error mac_target.Update


.MoveNext


Loop Until .EOF

End With


'******
Exit_h:
'******
On Error Resume Next
Loc_source.Close
mac_target.Close
Set Loc_source = Nothing
Set mac_target = Nothing


Exit Function


'*********
err_h:
'*********
MsgBox Error$
Stop
Resume


End Function

 

[zemp]

I would start by checking the data types in the PSQL database and ensure that they are what is desired and that they can handle the data that is being passed to them. Specifically numeric data types (decimal values and large numbers).

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."

 

[vbajock]

Already did that, they are an exact match.

 

[zemp]

Is your date format correct? I have found that with Pervasive it often needs to be 'YYYY-MM-DD'. If it is not in that format try,

...
mac_target!INQ_ORD_DATE = format(!INQ_ORD_DATE."YYY=MM-DD")
...

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."

 

[vbajock]

The date is a long integer, for example, 20040119 is todays date.

 

[zemp]

The only I other thing that I can think of is to manually enter the exact values into the prevasive database via the Pervasive control center and see which field(s) causes the error and what the error is.

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."