Faster method to UPDATE records in table than .Edit and .Update 1

[waubain]

I am trying to UPDATE records in an Access table as I screen scrap a string from our legacy system. This code works, but takes 20 minutes to update 460 records.

Sub RPHI_Retrieve()

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim dbPath As String
Dim strReadline As String
Dim arrData() As String


    dbPath = "S:\Pharmacy General\Databases Automation\Databases\Test\Test.accdb"
    Set ws = DBEngine.Workspaces(0)     
    Set db = ws.OpenDatabase(dbPath)
    Set rs = db.OpenRecordset("tblTemp", dbOpenTable)
    'Set rs = db.OpenRecordset("SELECT * FROM tblTemp")

    'strReadline from screen scraping delimited by the pipe character

    arrData = Split(strReadline, "|")
                
       rs.MoveFirst
       Do Until rs.EOF
          If rs!Number = arrData(0) Then
          rs.Edit
          rs!Resources1 = arrData(1)
          rs!Resources2 = arrData(2)
          rs!Resources3 = arrData(3)
          rs!Resources4 = arrData(4)
          rs!Resources5 = arrData(5)
          rs.Update
          End If
          rs.MoveNext
       Loop
   
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    Set ws = Nothing

End Sub

I am not very good at SQL, but made this atempt at writing to the first field. I get an error 3085 Undefined funciton 'arrData' in expression.

    ' same dims as first example

    arrData = Split(strReadline, "|")

                db.Execute "UPDATE tblRPHITemp SET Resources1 = arrData(1) WHERE Number = arrData(0);" 
                
		'& "SET tblTemp.Resources1 = arrData(1) " _
                '& "SET tblemp.Resources2 = arrData(2) " _
                '& "SET tblTemp.Resources3 = arrData(3) " _
                '& "SET tblTemp.Resources4 = arrData(4) " _
                '& "SET tblTemp.Resources5 = arrData(5) " _
                '& "WHERE tblTemp.Number = ArrData(0);"

Thank you in advance for any suggestions.

You don't know what you don't know...

 

[jrbarnett]

You only need the SET keyword once, separate fields must be comma separated. Eg.

' same dims
Currentdb.Execute "UPDATE table Set field1='x', field2=123 Where id=10", dbFailOnError

I am sure you can figure out the rest.
John

 

[lameid]

In addition to only needing one set, you may need to concatenate values from your loop instead of expressions that are out of scope to the query...

Incorrect

db.Execute "UPDATE tblRPHITemp SET Resources1 = arrData(1) WHERE Number = arrData(0);"

Correct

db.Execute "UPDATE tblRPHITemp SET Resources1 = " & arrData(1) & " WHERE Number = arrData(0);"

Unless you need delimiters as in it is text then the below is Correct

db.Execute "UPDATE tblRPHITemp SET Resources1 = """ & arrData(1) & """ WHERE Number = arrData(0);"

I embedded double quotes in the string, some people prefer single quotes (they work and are used in my SQL variants).

 

[lameid]

oops, last line... "my SQL variants" should be "MORE SQL Variants

 

[waubain]

Thank you both. It provided direction.

My syntax was definitely incorrect, but I did not know how to fix until I saw your posts. I will give it a try. I do not use SQL enough to remember where to place quotes and ampersands. Thank you for the examples.

In the mean time I did find this to be exponentially faster. Not sure if this will fail at some point. In this case NUMBER is a primary key, so there should be only one match.

                With rs
                    .FindFirst "Number = " & arrData(0)
                    If Not .NoMatch Then
                        .Edit
                        !resources1 = arrData(1)
                        !resources2 = arrData(2)
                        !resources3 = arrData(3)
                        !resources4 = arrData(4)
                        !resources5 = arrData(5)
                        .Update
                    End If
                End With

Thanks again

You don't know what you don't know...

 

[lameid]

What your missing in understanding is that the code you have is running a SQL statement as a String. My example is to build/concatenate together a string the makes a valid SQL statment. In your original code, SQL sees arrData(1) as a function... If you instead get the value from the array as an expression by not putting it inside the string it will return a value such as 10 or Fred or whatever. Then that value gets put in the SQL statement.

That said, looks like in my haste to post I overlooked a second value in the Where clause that has to be pulled out similar to what I did for the first value.

That aside, taking a closer look at the situation, it may be fastest to create a more ideal table to use as a source instead of "strReadline" by parsing out the columns and then running a query that joins rather than looping all the cases and running multiple queries each of which has to be compiled.

The simplist thing may be to dump the table/column to a text file and then reimport it. Although it may be quicker to make a UDF to return each column and use that to insert / append records to a table. My hunch is that smaller files would work faster with the UDF and larger files would be better with the export and import. What I have no idea on is what "small" and "large" are for this scenario.

 

[waubain]

Thank you for the detailed explanation. I did consider creating multiple tables and a join query to do the Update.

I am getting a runtime error 462, so something is not closing properly. I believe it is the recordset, but I have started a new thread in the AttachMate forum, which is where I do my screen scraping.

Thanks again.


You don't know what you don't know...