-2147217887 - Invalid argument.

Posted on 2009-06-30
Last Modified: 2013-12-20
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

Question by:ryguy320
  • 4
  • 2
LVL 57
ID: 24744513
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.

Author Comment

ID: 24745548
Numerics and Strings are the correct data type and length.  Any other suggestions?
LVL 57
ID: 24745933
<<  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.
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails


Accepted Solution

ryguy320 earned 0 total points
ID: 24747488
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.
LVL 57
ID: 24747520
<<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 ;)
LVL 57
ID: 24748027
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.

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now