Access VBA linked to SQL table .findfirst, Bookmark & Recordset Clone speed Issue

[jennifers]

I am looking for a quicker workaround for .findfirst when using Recordset Clone. The current code is:

Set rstClone = Me.RecordsetClone
    Set dbs = CurrentDb
    strFind = "MonthFrom = #" & Me.Go_To_Month & "# AND DiversionPointNum = " & Me.Go_To_DiversionPointNum
    
    With rstClone
    .FindFirst strFind
     If .NoMatch Then
        .AddNew
        !MonthFrom = Me.Go_To_Month
        !DiversionPointNum = Me.Go_To_DiversionPointNum
        .Update
        Me.Bookmark = .LastModified
    Else
        Me.Bookmark = .Bookmark
    End If
    End With

Just to give more context, the code worked fine with local access tables. But, when tables were moved to SQL and were linked in that's when the .FindFirst started to run super slow (2 seconds vs. 40 seconds) I tried to convert this process into a SQL query using the code below, but since the previous code was using recordsetClone and .Bookmark, doing the same thing with the SQL query did not give the same result.

strFind2 = "SELECT MonthFrom, DiversionPointNum FROM " & rstClone.Name & " where MonthFrom = #" & Me.Go_To_Month & "# And DiversionPointNum = " & Me.Go_To_DiversionPointNum
 Set qdf1 = dbs.CreateQueryDef("", strFind2)
 Set xyz = qdf1.OpenRecordset(dbOpenDynaset, dbSeeChanges)

Any information on .findfirst being an issue with linked tables or using a bookmark with recordsetClone would be helpful. Or if there's another workaround possible to do the same process without using .findfirst. I've already tried using .findnext, .filter and the results have been same as using .findfirst.

[ranman256]

Stop using this code and run an update query.
clone takes time,
findfirst takes time.

run query.

[jennifers]

I am curious, how would you go about bookmarking if you use an update query instead? The problem is, if we don’t bookmark, the form doesn’t get populated. (I am working with a very old complicated system that we are just trying to get to work with SQL tables for now before we completely overhaul.)

[Gicu]

Where is this code located? AfterUpdate of a search control? Try to set the record source of the form to a saved query similar to what you show in your post then use the Requery method:

Cheers,

[jennifers]

You're right the code is located in afterupdate. The recordsource of the form is a query i have in Access but even after using requery i have to do something to make it point to the current record (something similar to what the bookmark is doing). Any suggestions on that?

[Gicu]

Not really without knowing a bit more about your setup. AfterUpdate of what? Looks like the record source is dependent on two fields, are they both available at the tie of the requery? Are they bound? Can you upload a small sample of the db, just the form in question, don't worry about the table(s).

Cheers,

[ssanfu]

Without the full code it is hard go give a focused answer.
But you could try adding a line to the code you posted to see if the SQL is properly formed.

strFind2 = "SELECT MonthFrom, DiversionPointNum FROM " & rstClone.Name & " where MonthFrom = #" & Me.Go_To_Month & "# And DiversionPointNum = " & Me.Go_To_DiversionPointNum
 
Debug.Print strFind2

Set qdf1 = dbs.CreateQueryDef("", strFind2)
 Set xyz = qdf1.OpenRecordset(dbOpenDynaset, dbSeeChanges)

Set a break point so you can see the SQL in the Immediate window..
Copy the SQL and paste it into a new query (SQL view), then execute the query to see the re3sults.

[jennifers]

Thank you for all of your help. It is a very complicated set of VBA that we are working with and we have been able to speed it up a little bit. It will have to be completely updated and recoded. Thanks again!