Solved Need help optimizing my query after SQL Server migration

[renQ]

The normal route would be a pop up single form that is opened modally/dialog on top the of the main list, and then returns you to where you where after is opened.
I have lots of them but they are are proprietary to the clients app, and difficult to take pictures of without munging a ton of the data.

I normally use a double click event on the Job_Number/Contract/Client_ID and pass it to the pop up with the OpenArgs property, then set the pop form to the appropriate single record.

Something like this:
View attachment 116209

Thats all fine thanks for the help, I think I can find a way to implement this. But I was wondering after updating that single record have you been able to reflect that change on the original table instantly. Or does the original table have the old info?

 

[GPGeorge]

Thats all fine thanks for the help, I think I can find a way to implement this. But I was wondering after updating that single record have you been able to reflect that change on the original table instantly. Or does the original table have the old info?

Once you have saved the change(s) to a record, they are in the table. To see the change(s) in a form, though, you need to requery or refresh that form's recordsource.

 

[renQ]

Once you have saved the change(s) to a record, they are in the table. To see the change(s) in a form, though, you need to requery or refresh that form's recordsource.

Is there a way to do this without losing your position in the table/form?

 

[GPGeorge]

Is there a way to do this without losing your position in the table/form?

Yes, but it is VBA that I don't have at hand at the moment. I'm sure one of the less forgetful members here will have a suggestion handy, though.

 

[Minty]

If you requery the Recordset not the form it will normally stay put where it was. (An often overlooked method)

If not simply store the unique ID before opening the edit form, then requery, then use the rs.bookmark and find first method to get you back where you were.

 

[GPGeorge]

If you requery the Recordset not the form it will normally stay put where it was. (An often overlooked method)

If not simply store the unique ID before opening the edit form, then requery, then use the rs.bookmark and find first method to get you back where you were.

I've not had success requerying the form's recordset directly. Can you provide a working example for me to learn from?

 

[GPGeorge]

Well, that's frustrating. The first form i tested raised an error, but others didn't. Now I have to figure out what's different about that form.

 

[Minty]

@GPGeorge I'll have to find an example. I'm sure I've made it work somewhere.
Probably be tomorrow as I need to sign off shortly

 

[Gasman]

@GPGeorge
Where you have a Me.Requery or any form requery, just insert Recordset eg: Me.Recordset.Requery

Hard to give you a working example, but here is code from my old SSAFA DB
In fact I just added the recordset, as it was not there before.

Private Sub cmdRequery_Click()
    Dim lngID As Long
    Dim rst As DAO.Recordset
    Dim strCriteria As String
   
    ' Save record so requery on subform will pick up all last record written
    If Me.Dirty Then Me.Dirty = False
    lngID = Me.ID
    Me.Recordset.Requery
    strCriteria = "ID=" & lngID
    Set rst = Me.sfrmEmails.Form.Recordset

    ' Requery the subform
    Me.sfrmEmails.Form.Recordset.Requery
    ' Go back to record we were on
    Me.Recordset.FindFirst strCriteria
    ' Now the sub form
    rst.FindFirst strCriteria
    Set rst = Nothing
End Sub

so really the FindFirst is no longer needed.

 ' Go back to record we were on
    'Me.Recordset.FindFirst strCriteria
    ' Now the sub form
    'rst.FindFirst strCriteria
    Set rst = Nothing

 

[NauticalGent]

Requery an MS Access Form While Remaining on the Same Record

Have you ever wanted to requery a form after a user inserts a new record or modifies an existing record and wanted to stay on the same record?

www.devhut.net

 

[GPGeorge]

@GPGeorge
Where you have a Me.Requery or any form requery, just insert RecordSouce eg: Me.Recordset.Requery

Hard to give you a working example, but here is code from my old SSAFA DB
In fact I just added the recordset, as it was not there before.

Private Sub cmdRequery_Click()
    Dim lngID As Long
    Dim rst As DAO.Recordset
    Dim strCriteria As String
  
    ' Save record so requery on subform will pick up all last record written
    If Me.Dirty Then Me.Dirty = False
    lngID = Me.ID
    Me.Recordset.Requery
    strCriteria = "ID=" & lngID
    Set rst = Me.sfrmEmails.Form.Recordset

    ' Requery the subform
    Me.sfrmEmails.Form.Recordset.Requery
    ' Go back to record we were on
    Me.Recordset.FindFirst strCriteria
    ' Now the sub form
    rst.FindFirst strCriteria
    Set rst = Nothing
End Sub

so really the FindFirst is no longer needed.

 ' Go back to record we were on
    'Me.Recordset.FindFirst strCriteria
    ' Now the sub form
    'rst.FindFirst strCriteria
    Set rst = Nothing

Thanks, as noted, the problem only occurred in one subform, for reasons.

I hope to go back at some point and try to identify what's different about that subform.

Of course, it was the first one I tried....

 

[GPGeorge]

Thanks, as noted, the problem only occurred in one subform, for reasons.

I hope to go back at some point and try to identify what's different about that subform.

Of course, it was the first one I tried....

The subform's recordsource is a passthru query, not a local query.

 

[Gasman]

Only ever used Access tables/queries, sorry

 

[GPGeorge]

Only ever used Access tables/queries, sorry

That was my bad. I just randomly picked one subform to try it on; turns out I made a bad choice.

 

[Pat Hartman]

It's unlikely that any of those Primary or Foreign Keys are not indexed in Access, and the same should be true when they are Migrated.

I don't have a link to the thread but I discovered a while back that hidden indexes created by Access on FK's do not get upsized. Only indexes that are manually created get upsized. No one was ever able to tell me whether or not having two indexes on the same field cause a problem for ACE, so since Access always creates hidden indexes and those don't get upsized, I'm not sure I want to create duplicate indexes in the ACE BE's. So, I don't crete the manual indexes until I'm ready to convert the BE. Then I create them in ACE and SSMA upsizes my indexes correctly.

 

[GPGeorge]

I don't have a link to the thread but I discovered a while back that hidden indexes created by Access on FK's do not get upsized. Only indexes that are manually created get upsized. No one was ever able to tell me whether or not having two indexes on the same field cause a problem for ACE, so since Access always creates hidden indexes and those don't get upsized, I'm not sure I want to create duplicate indexes in the ACE BE's. So, I don't crete the manual indexes until I'm ready to convert the BE. Then I create them in ACE and SSMA upsizes my indexes correctly.

I think that SSMA probably doesn't upsize the indexes created on foreign key fields when referential integrity is enforced because they are not required in SQL Server or created by default for Foreign Key fields in SQL Server tables.

I think SSMA should have an option to include them anyway, or omit them at the preference of the user.

 

[Pat Hartman]

I don't know why SSMA doesn't upsize the indexes. RI is ALWAYS enforced in my schemas. That is what makes Access generate the index on the FK. We went through this in the original thread. You proved to yourself they were there and saw when they were created. I agree, SSMA should not simply ignore the indexes. An option would be a good solution but first MS needs to see this as a problem and someone made the decision to not upsize those particular indexes or maybe it is a bug. I have no way to pursue this.

My problem is that I discovered the hidden index more than 20 years ago and so never manually added an index on an FK after that. I'm also pretty sure that the upsizing wizard used to upsize these indexes but once we were forced into using SSMA, I think that is where the issue started. I have no way to contact old customers to see if my applications are still in service and if the indexes were upsized. I am mostly retired so am not generating new apps very frequently and only discovered the problem on a client installation because I had to make a change to it.

 

[renQ]

I am having trouble keeping the position I was at after requerying the form. I feel this is because I am using a pass through query, it does not allow me to access the record set property of the form.

 

[Pat Hartman]

Did you try making the view? The view should be more efficient since it is "compiled" and an exectution plan has already been created. With the pass through query, SQL Server has to generate the execution plan on the fly.

 

[renQ]

The view isn't very efficient when it comes to navigating my form. I'm not entirely sure why but it might be because of how Access is handling the view. Specifically for the main use that my company has which is using the find feature to better hop around the different records a passthrough query works much better.