Solved

-2147217887 - Invalid argument.

Posted on 2009-06-30
6
447 Views
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
            .AddNew
            
            
            
            !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
            Else
            !Qty = Trim(vArr(26))
            End If
            
            
            .Update
        End With
        
        
    End If
NextLine:
Loop
 
Set cnn = Nothing
 
lSuccess = True
 
ErrCleanup:
    Close #1
    ImpTextFileADO = lSuccess
    Exit Function
Error_Handler:
    lSuccess = False
    MsgBox Err.Description, vbOKOnly + vbExclamation, Err.Source
    Resume ErrCleanup
End Function

Open in new window

0
Comment
Question by:ryguy320
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 58
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.
JimD.
0
 

Author Comment

by:ryguy320
ID: 24745548
Numerics and Strings are the correct data type and length.  Any other suggestions?
0
 
LVL 58
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.
JimD.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Accepted Solution

by:
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.
0
 
LVL 58
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 ;)
JimD.
0
 
LVL 58
ID: 24748027
ryguy320,
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.
JimD.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
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…

688 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