-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.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 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

8 Experts available now in Live!

Get 1:1 Help Now