AfterUpdate codes run very slow 1

[LittleNick]

Hi,
I have a SQl table named tblTransaction that keep track of the change in value on several of the textboxes. This table has now cumulated about 700,000 records. Below is the codes of one of the textbox After Update Event:

Private Sub ShipDay_AfterUpdate()

Dim StrSQL As String
Dim cnnTest As New ADODB.Connection
Dim rstRS As New ADODB.Recordset

StrSQL = "Select * from tblTransactions"


Set cnnTest = CurrentProject.Connection
Set rstRS = New ADODB.Recordset

rstRS.Open StrSQL, cnnTest, adOpenDynamic, adLockOptimistic

rstRS.MoveLast

rstRS.AddNew
rstRS("patientid") = Me.ID.Value
rstRS("datestamp") = Now()
rstRS("Application") = "Order"
rstRS("ItemChanged") = "ShipDay"
rstRS("ValueBefore") = varShipDay
rstRS("ValueAfter") = Me.ShipDay
rstRS.Update

rstRS.Close

End Sub

The problem I have is it take a while to execute. Can someone tell me if there is a way to make this run faster?
Thanks in advance for helps

 

[CaptainD]

For 1, you are pulling in all of the records from the table. how big is the table?

Next, since you are just adding a new record, why not create an insert statement and then use DoCmd.RunSQL to do the insert?

 

[LittleNick]

Thanks CaptainD for your quick reply.
The table has about 700,000 records
I will try your suggestion tonight. Thanks again.

 

[TheAceMan1]

How are ya LittleNick . . .

Try:

[blue]Public Sub AppendDat()
   
   Dim db As DAO.Database, SQL As String
   
   Set db = CurrentDb
      
   SQL = "INSERT INTO [[purple][B][I]tableName[/I][/B][/purple]] (PatientID, dateStamp, Application " & _
                                  "ItemChanged, ValueBefore, ValueAfter) " & _
         "VALUES (" & Me!ID & ", " & Now() & ", 'ORDER', " & _
                    "'Shipday', " & varShipDay & "' " & Me!ShipDay & ")"
   DoCmd.RunSQL SQL
   
   'Me.Requery
      
End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[LittleNick]

Thanks for your post TheAceMan1.
I tried the codes and got an error:
Run-Time error '3':
Closing delimiter not found for the string beginning at posiotion 160 in the command. The string begins with: '3)}

I am not sure what the error code is telling me. Again Thanks a lot for all your helps

 

[LittleNick]

Sorry I actually forgot to put the table name. I corrected this and I also changed ' to , after & varShipDay & " and I get this error now.
Run-time error '102: Incorrect syntax near 'Itemchanged'.

Thanks

 

[TheAceMan1]

LittleNick . . .

Woooah ... Add the comma in [red]red[/red] where you see it:

[blue]   SQL = "INSERT INTO [tableName] (PatientID, dateStamp, Application[red][b],[/b][/red] " & _
                                  "ItemChanged, ValueBefore, ValueAfter) " & _
         "VALUES (" & Me!ID & ", " & Now() & ", 'ORDER', " & _
                    "'Shipday', " & varShipDay & "' " & Me!ShipDay & ")"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[LittleNick]

Thanks TheAceMan1 for reply and your helps. I added the ' and it is working!

SQL="INSERT INTO..."

     "VALUES ([COLOR=red]'[/color]" & Me!ID & "[COLOR=red]'[/color], [COLOR=red]'[/color]" & Now() & "[COLOR=red]'[/color], 'ORDER', " & _
                    "'Shipday', [COLOR=red]'[/color]" & varShipDay & "[COLOR=red]'[/color], [COLOR=red]'[/color]" & Me!ShipDay & "[COLOR=red]'[/color])"
...

Your help is very appreciated. Thanks