Solved

-2147217887 - Invalid argument.

Posted on 2009-06-30
6
444 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
  • 4
  • 2
6 Comments
 
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.
JimD.
0
 

Author Comment

by:ryguy320
ID: 24745548
Numerics and Strings are the correct data type and length.  Any other suggestions?
0
 
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.
JimD.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 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 ;)
JimD.
0
 
LVL 57
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

749 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