[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

-2147217887 - Invalid argument.

Posted on 2009-06-30
6
Medium Priority
?
450 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

650 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