Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Location
    Colorado
    Posts
    6

    Unanswered: Invalid Argument

    When I do the following actions, I seem to put myself into an error condition that I cannot figure out how to recover from.

    I create a rather simple table with 1,250,000 rows with 9 fields.
    Run a VB program reads different data within a record and writes some specific data to field in that same record(row).
    At the same record or point in the table around 300,000 rows I get an Invalid Argument and debug points to a .update each time.

    When I try to recover from the error, by reseting the error or make change in the VBA code, I cannot seem to be able to make changes or save the changes in VB editor with getting the invalid argument error. Frequently in the scenario, I get this table is open by another user or locked etc.

    Joe

    Here is the VB code

    Sub OpenAPDB()
    Dim cnn1 As New Connection
    Dim rst1 As Recordset

    'Create the connection.
    cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=D:\AP_ADO.mdb;"

    'Create recordset reference, and set its properties.
    Set rst1 = New ADODB.Recordset
    rst1.CursorType = adOpenKeyset
    rst1.LockType = adLockOptimistic

    'Open recordset, and print a test record.
    rst1.Open "AP_all", cnn1
    Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value

    With rst1

    Do Until .EOF


    Debug.Print "Row " & .Fields("ID")

    .Fields("FileType") = Right(.Fields("FileName"), 4)
    .Update

    If InStr(1, .Fields("Filename"), "dat") > 0 Then
    .Fields("FileType") = ".dat"
    .Update
    GoTo SkipOver
    End If
    If InStr(1, .Fields("Filename"), ".html") > 0 Then
    .Fields("FileType") = ".html"
    .Update
    GoTo SkipOver
    End If
    If InStr(1, .Fields("FileName"), "packet.z") > 0 Then
    .Fields("FileType") = ".patch"
    .Update
    GoTo SkipOver
    End If
    If InStr(1, .Fields("path"), "spool") > 0 Then
    .Fields("FileType") = ".spool"
    .Update
    GoTo SkipOver
    End If
    If InStr(1, .Fields("Filename"), ".gz") > 0 Then
    .Fields("FileType") = ".gzip"
    .Update
    GoTo SkipOver
    End If
    If InStr(1, .Fields("Filename"), ".log") > 0 Then
    .Fields("FileType") = ".log"
    .Update
    GoTo SkipOver
    End If
    If InStr(1, .Fields("Filename"), "*") > 0 Then
    .Fields("FileType") = ".*"
    .Update
    GoTo SkipOver
    End If



    If InStr(1, .Fields("Filename"), ".lib") > 0 Then
    .Fields("FileType") = ".txt"
    .Update
    GoTo SkipOver
    End If
    If InStr(1, .Fields("Filename"), ".txt") > 0 Then
    .Fields("FileType") = ".txt"
    .Update
    GoTo SkipOver
    End If
    If InStr(1, .Fields("Group"), "mail") > 0 Then
    .Fields("FileType") = ".mail"
    .Update
    GoTo SkipOver
    End If
    If InStr(1, .Fields("Filename"), "mail") > 0 Then
    .Fields("FileType") = ".mail"
    .Update
    GoTo SkipOver
    End If

    If InStr(1, .Fields("Path"), "mail") > 0 Then
    .Fields("FileType") = ".mail"
    .Update
    GoTo SkipOver
    End If

    If Mid(.Fields("FileType"), 1, 1) <> "." Then
    .Fields("FileType") = "Unknown"
    .Update
    GoTo SkipOver

    End If

    SkipOver:

    .MoveNext ' read next record
    Loop
    End With


    'Clean up objects.
    rst1.Close
    cnn1.Close
    Set rst1 = Nothing
    Set cnn1 = Nothing

    End Sub

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    try this.

    First, declare your recordset and connection as ADODB objects.

    Code:
    Dim cnn1 As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    
    Set cnn1 = New ADODB.Connection
    Second, try issuing SQL UPDATE statements, rather than updating the recordset. (See below.) With large recordsets, updating the rs is bound to be memory intensive.

    Third, all those goto's give me a headache!

    Why not simplify things with an if-then-elseif structure for the bulk of the code.

    Code:
    If Instr(If InStr(1, .Fields("Filename"), "dat") > 0 Then
      ' issue sql update
      cnn1.Execute "Update AP_all Set FileType = '.dat' Where FileName = '" & .Fields!FileName & "'"
    
    ElseIf Instr(If InStr(1, .Fields("Filename"), ".html") > 0 Then
      cnn1.Execute "Update AP_all Set FileType = '.html'  Where FileName = '" & .Fields!FileName & "'"
    
    ElseIf Instr(If InStr(1, .Fields("Filename"), "packet.z") > 0 Then ...
     '...
    Else
    
    End If
    better yet, why not break up the recordset into smaller chunks, or avoid them altogether. SQL is perfectly capable of updating the records without loading the data into a recordset.

    You say it's an access database, so the instr function works just fine within SQL.

    Code:
    ' SQL to update all rows in the table with 'dat' in the file name field
    cnn1.Execute  "Update AP_all Set FileType = '.dat' Where Instr(FileName, ""dat"") > 0"
    
    ' SQL to update all rows in the table with 'html' in the file name field
    cnn1.Execute  "Update AP_all Set FileType = '.html' Where Instr(FileName, ""html"") > 0"
    
    ' and so on...
    It's LOTS faster than loading a recordset & iterating through the rs.
    Last edited by loquin; 08-26-07 at 03:49.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Aug 2007
    Location
    Colorado
    Posts
    6

    Being Bone Headed

    loquin, thanks for your response, and now I must apologize for being dense and bone headed stupid. Two basic simple questions and one more request.

    1. How do I run a SQL request, edit in in the VBA editor and run?
    2. What would a complete and simple SQL program look like?

    I'd appreciate a ready to run piece of code that I can expand and learn from.

    As before, Thank you very much.

    Joe




    Quote Originally Posted by loquin
    try this.

    First, declare your recordset and connection as ADODB objects.

    Code:
    Dim cnn1 As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    
    Set cnn1 = New ADODB.Connection
    Second, try issuing SQL UPDATE statements, rather than updating the recordset. (See below.) With large recordsets, updating the rs is bound to be memory intensive.

    Third, all those goto's give me a headache!

    Why not simplify things with an if-then-elseif structure for the bulk of the code.

    Code:
    If Instr(If InStr(1, .Fields("Filename"), "dat") > 0 Then
      ' issue sql update
      cnn1.Execute "Update AP_all Set FileType = '.dat' Where FileName = '" & .Fields!FileName & "'"
    
    ElseIf Instr(If InStr(1, .Fields("Filename"), ".html") > 0 Then
      cnn1.Execute "Update AP_all Set FileType = '.html'  Where FileName = '" & .Fields!FileName & "'"
    
    ElseIf Instr(If InStr(1, .Fields("Filename"), "packet.z") > 0 Then ...
     '...
    Else
    
    End If
    better yet, why not break up the recordset into smaller chunks, or avoid them altogether. SQL is perfectly capable of updating the records without loading the data into a recordset.

    You say it's an access database, so the instr function works just fine within SQL.

    Code:
    ' SQL to update all rows in the table with 'dat' in the file name field
    cnn1.Execute  "Update AP_all Set FileType = '.dat' Where Instr(FileName, ""dat"") > 0"
    
    ' SQL to update all rows in the table with 'html' in the file name field
    cnn1.Execute  "Update AP_all Set FileType = '.html' Where Instr(FileName, ""html"") > 0"
    
    ' and so on...
    It's LOTS faster than loading a recordset & iterating through the rs.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Joe,

    You have to admit your code is a hornet's nest, with repetetive ifs, updates, and gotos; you have to clean it up.

    First of all, as loquin hints, all mutually exclusive cases should be in one if-elseif construct. If possible (in this case it isn't, but something to keep in mind) streamline the code by using a Select Case construct instead.

    You only need one .update command per record, no matter how many fields there are in the record.

    Save the goto; it's unnecesasry altogether in this case.

    Don't use the .Fields("SomeName") construct; it's a waste of Access' valuable time (Access has to solve for the array value each time it encounters this contruct) when you can simply say !SomeName instead.

    Similarly, the first argument in the InStr() function is only necessary if it isn't 1. 1 is the default, it's optional, and again a waste of Access' time. Get rid of it.

    I'm not trying to criticize, Joe, only trying to help you get your code to look professional. If you clean up the code, chances are the problem will go away as well.

    Sam

  5. #5
    Join Date
    Aug 2007
    Location
    Colorado
    Posts
    6
    Quote Originally Posted by Sam Landy
    Joe,

    You have to admit your code is a hornet's nest, with repetetive ifs, updates, and gotos; you have to clean it up.

    I'm not trying to criticize, Joe, only trying to help you get your code to look professional. If you clean up the code, chances are the problem will go away as well.

    Sam
    No Sam, the problem did not go away

    And I don't appreciate your offensive and condescending post where you only repeated what Loquin had already pointed out.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Joe_Daddy,

    One thing I noticed with your routine (and correct me if I'm wrong) but it looks like you're instr functions are all either looking for either keywords in the string or the file extension. Have you considered having another field added to the table which designates what procedure you run/goto or is that not an option?

    Disregard - it looks like that is kind of what you're doing with the routine (am I correct that you're searching through looking for keywords in certain fields and designating the FileType field based on those keywords?) I guess it wouldn't help to have another table with just the keywords and what FileType to designate it as and in your routine loop through the table/string looking for instr(MyString, MyKeyword). Something like a "lookup" type table with the fields: FieldToLookAt, StringToLookFor, UpdateWith/ActionToTake. But that might just complicate things a little more.
    Last edited by pkstormy; 08-27-07 at 15:39.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •