• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

-2147217887 - Invalid argument.

Having a problem with VBA code.  The code is failing on the .Update recordset command.  It updates the first record but then give the error -2147217887 Microsoft JET Database engine - Invalid argument.  No code has been changed or anything redesigned.  It simply stopped working.  Thank you in advance.

There are no apostrophes and all the data types appear to be aligned with the database structure.
Dim iRow As Long
Dim iTest As Long
Dim lFirst As Boolean
iRow = 0
Do While Not EOF(1)
iTest = iTest + 1
    iRow = iRow + 1
    Line Input #1, s
     If iRow <= 5 Then
        'Debug.Print s
        GoTo NextLine
    End If
    If Len(s) > 0 Then
        s = Replace(s, Chr(34), "")
        'Debug.Print s
        vArr = Split(s, "|")
        Debug.Print iTest
        If iTest = 60003 Then
            Debug.Print "Break"
        End If
       If Trim(vArr(0)) <> "" Then
             GoTo NextLine
       End If
        If Left(Trim(vArr(1)), 1) = "*" Or IsNumeric(Trim(vArr(1))) = False Then
            GoTo NextLine
        End If
        'What is the period/year
        If lFirst = False Then
             cnn.Execute "DELETE FROM tblSAPGLSALR WHERE (Prd = " & Trim(vArr(1)) & ") AND (Yr = " & Trim(vArr(2)) & ") "
            lFirst = True
        End If
        With rs
            !Prd = Trim(vArr(1))
            !Yr = Trim(vArr(2))
            !Created_on = Trim(vArr(3))
            !CCtr = Trim(vArr(4))
            !Co_Object_Name = Trim(vArr(5))
            !Value = Trim(vArr(6))
            !Cost_Elem = Trim(vArr(7))
            !Offset_Acct_Name = Trim(vArr(8))
            !PO_Text = Trim(vArr(9))
            !Doc_No = Trim(vArr(10))
            !Cost_Elem_Name = Trim(vArr(11))
            !Doc_Date = Trim(vArr(12))
            !Doc_Header = Trim(vArr(13))
            !Doc_No_2 = Trim(vArr(14))
            !frm = Trim(vArr(15))
            !Name = Trim(vArr(16))
            !Offst_Acct = Trim(vArr(17))
            !Post_Date = Trim(vArr(18))
            !Purch_Doc = Trim(vArr(19))
            !Ref_Doc_No = Trim(vArr(20))
            !to = Trim(vArr(21))
            !Offact = Trim(vArr(22))
            !Offact_Name = Trim(vArr(23))
            !Cost_Elem_Desc = Trim(vArr(24))
            !Reference = Trim(vArr(25))
            If Trim(vArr(26)) = "" Then
            !Qty = 0
            !Qty = Trim(vArr(26))
            End If
        End With
    End If
Set cnn = Nothing
lSuccess = True
    Close #1
    ImpTextFileADO = lSuccess
    Exit Function
    lSuccess = False
    MsgBox Err.Description, vbOKOnly + vbExclamation, Err.Source
    Resume ErrCleanup
End Function

Open in new window

  • 4
  • 2
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Check your field lengths.  2147217887  is a generic error meaning there is a connection problem (not your problem since the first record works) or that an overflow has occured while trying to write data into a field.
Make sure all your numerics will fit into the field (ie. an integer value < 32768) and that all text strings are shorter then the defined field length.
ryguy320Author Commented:
Numerics and Strings are the correct data type and length.  Any other suggestions?
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<  Any other suggestions?>>
  Since your first record saves OK, then it has to be something with the data in the second record.  Did you check for NULLS?
  Only other thing I would suggest is the process of elimination.  Remove everything but the first field; does the record save?  then put back a few more fields and try again.  Keep doing that (or the reverse - remove fields until the record saves) until you find which field is causing hte problem.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

ryguy320Author Commented:
My DB was almost 2GB.  I deleted outdated and obsolete data.  I compacted and repaired.  This brought it down to 1 GB.  I ran the import procedure and it now functions properly.  Thanks for you help though.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<My DB was almost 2GB.  I deleted outdated and obsolete data.  I compacted and repaired.  This brought it down to 1 GB.  I ran the import procedure and it now functions properly.  Thanks for you help though.>>
  Wow.  Would not have thought of that. I assumed you repeated the process a couple of times and if you did, your first save would have started to fail then to.
 Just goes to show you should never assume anything, which I should have learned by now ;)
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
In this case, please accept your own comment as the solution so this question can become a PAQ (Previosuly Asked Question) and go into the database.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now