?
Solved

-2147217887 - Invalid argument.

Posted on 2009-06-30
6
Medium Priority
?
449 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

741 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