Turning off Name AutoCorrect Options - Do I need to clear out NameMap now?

[adhoustonj]

Hello AWF,
Trying to optimize some db's that have been in service for years, and have been struggling with speed, program not responding for 2-20 minutes, etc.
Typically if speed is bad I will close sessions of the backend, compact & repair, and release it back to production. This usually speeds things up, but then may need to be done again in 2 weeks.

I recently imported all objects from my front end to a new db, and then when trying to run was getting compile errors immediately. Member or data member not found.
The old db still compiles perfectly fine, decompile, recompile, all is okay.

So I started hunting and saw name autocorrect options were on in my current db, and came across post 13 in this thread, with documentation about exporting forms to text, remove namemap, and then reimport. https://www.access-programmers.co.uk/forums/threads/form-saving-issue.326265/#post-1861547

Should i do this to all forms if name autocorrect options has been on all this time? Most forms were 25-30 lines in the namemap details.

 

[arnelgp]

you can also fine-tune your db by following this:
Significantly Improve the Performance of Microsoft Access Databases with Linked Tables (fmsinc.com)

 

[ebs17]

backend compact & repair

Normal processes don't break a backend, why fix it?

Compression has an effect on performance when there have been very large changes to the database, i.e. something like multiple deletions and new additions to data sets. So examine your procedures and processes to find out where such actions take place. You don't have to fix problems you don't create yourself.

A large proportion of performance problems lie in queries, where large amounts of data are combined with suboptimal to very poor processing. So you would analyze your queries to find potential and use it.

The automatic object name correction is a performance brake and should be switched off.

 

[adhoustonj]

you can also fine-tune your db by following this:
Significantly Improve the Performance of Microsoft Access Databases with Linked Tables (fmsinc.com)

I will check that persistent connection procedure out.. I do something similar already with a kickout procedure that has a tblclosedatabase flag to shut down the db's.

 

[adhoustonj]

Normal processes don't break a backend, why fix it?

Compression has an effect on performance when there have been very large changes to the database, i.e. something like multiple deletions and new additions to data sets. So examine your procedures and processes to find out where such actions take place. You don't have to fix problems you don't create yourself.

A large proportion of performance problems lie in queries, where large amounts of data are combined with suboptimal to very poor processing. So you would analyze your queries to find potential and use it.

The automatic object name correction is a performance brake and should be switched off.

Some bloat - sometimes small or large, and also because it seems to improve performance.

I do think some attention needs to be paid to my queries.. Some days a query will run instantly and others the db will freeze for up to 20 minutes sometimes, and the underlying data is not changing that much. Just locks up when stepping through the procedure and opening a recordset.

After turning off auto name correct - should I remove all mapname information from the exported text forms?

 

[arnelgp]

persistent connection means you have the backend db open all the time (as long as the fe is open).
this means faster accessing of linked tables and bound forms.

 

[adhoustonj]

persistent connection means you have the backend db open all the time (as long as the fe is open).
this means faster accessing of linked tables and bound forms.

Understood. The procedure looks different than what I do, and I will test it out today - but I think I'm already doing this. frmCloseDatabase launches in the background when db is opened, and is bound to backend tables, but I use a 60000 timer to check if my tblCloseDatabase is set to true

Option Compare Database
Option Explicit
Private Sub Form_Load()
If gcfHandleErrors Then On Error GoTo Err_frmCloseDatabase1          ' Initialize error handling.
If Me.CloseDatabase = -1 Or Me.ojt = -1 Then

MsgBox ("This database is currently being revised and will be closed")
Application.Quit
End If

Exit_frmCloseDatabase1:               ' Label to resume after error.
Exit Sub                             ' Exit before error handler.
Err_frmCloseDatabase1:                ' Label to jump to on error.
Call LogError(err.Number, err.Description, "frmCloseDatabase1()")
Resume Exit_frmCloseDatabase1         ' Pick up again and quit.

End Sub

Private Sub Form_Timer()
If gcfHandleErrors Then On Error GoTo Err_frmCloseDatabase2          ' Initialize error handling.
If Me.CloseDatabase = -1 Or Me.ojt = -1 Then
Application.Quit
End If

Exit_frmCloseDatabase2:               ' Label to resume after error.
Exit Sub                             ' Exit before error handler.
Err_frmCloseDatabase2:                ' Label to jump to on error.
Call LogError(err.Number, err.Description, "frmCloseDatabase2()")
Resume Exit_frmCloseDatabase2         ' Pick up again and quit.

End Sub

 

[Pat Hartman]

Compile errors when running on one PC but not on another is extremely odd. Did you fix the compile errors? Can you post the errors with before and after code? Usually the different PC issue causes runtime errors due to missing libraries. Are you sure that isn't what is happening?

Unless you are having a problem with a specific form, I would not bother with the export to text, cleanup, and reimport. The decompile generally gets rid of all the p-code which is what gets corrupted rather than your source code.

 

[arnelgp]

but I think I'm already doing this. frmCloseDatabase launches in the background when db is opened, and is bound to backend tables, but I use a 60000 timer to check if my tblCloseDatabase is set to true

it is not the same as your "Kicking out users" code.

 

[adhoustonj]

Compile errors when running on one PC but not on another is extremely odd. Did you fix the compile errors? Can you post the errors with before and after code? Usually the different PC issue causes runtime errors due to missing libraries. Are you sure that isn't what is happening?

Unless you are having a problem with a specific form, I would not bother with the export to text, cleanup, and reimport. The decompile generally gets rid of all the p-code which is what gets corrupted rather than your source code.

The compile errors were on the same PC. I decompile/compact&repair/compile this db occasionally with no problem. In troubleshooting I made a fresh accdb, imported all objects from db, and that is when compile errors start. One example is this code with compile error "Method or data member not found".

Private Sub lbl_pdca_Click()
If gcfHandleErrors Then On Error GoTo Err_FrmHS_ChangePoint_9          ' Initialize error handling.
DoCmd.OpenForm "frmHS_pdca", , , , , , OpenArgs:=Me.hsf_id & "|" & Me.hsaf_id & "|" & Me.hsf_txt
Exit_FrmHS_ChangePoint_9:               ' Label to resume after error.
Exit Sub                             ' Exit before error handler.
Err_FrmHS_ChangePoint_9:                ' Label to jump to on error.
Call LogError(err.Number, err.Description, "FrmHS_ChangePoint_9()")
Resume Exit_FrmHS_ChangePoint_9         ' Pick up again and quit.
End Sub

It highlights the last openarg Me.hsf_txt which is a field in my record source. In the current db there are none of these compile errors, even after decompile/compact&repair/compile. Sounded like a corruption problem and that was how i found the export forms as text & remove mapname.

SELECT tblHS_area_fields.hsaf_id, tblHS_area_fields.hs_area_id, tblHS_area.hs_area, tblHS_area_fields.hsf_id,
tblHS_fields.hs_fields, tblHS_fields.hsf_txt, tblHS_area_fields.trn_date, tblHS_area_fields.aud_date, tblHS_area_fields.rev
FROM (tblHS_area_fields INNER JOIN tblHS_area ON tblHS_area_fields.hs_area_id = tblHS_area.hs_area_id)
INNER JOIN tblHS_fields ON tblHS_area_fields.hsf_id = tblHS_fields.hsf_id

The only change with different PC's is speed, but network speed seems to be relatively the same by speed test on google, and from monitoring task manager -> performance -> ethernet while using the db.

 

[adhoustonj]

it is not the same as your "Kicking out users" code.

Thanks for confirming. Just wanted to make sure!

 

[Pat Hartman]

Do you have a control on the form that is bound to Me.hsf_txt? If you are referencing a field in the form's RecordSource but that field is NOT bound to a control (hidden or visible) on the form, this can cause an error. I haven't run into this enough times to be able to nail down when/ the form breaks but I'll explain what I do know.

Since I started using Access in the early 90's, Reports have had this issue. You bind a report to a table or query. Some of the fields are bound to controls. But I also occasionally have other fields that are not displayed and so are not bound to controls but they are used in calculations. When I develop the report, everything works fine. I save it, and it works fine. At some point Access rewrites the RecordSource so that it selects ONLY columns which are bound to controls so the columns I am using in calculations are no longer available and the next time I run the report it fails because it can't find one of the fields used in the calculations. After a while I figured out what was happening and so created tiny hidden controls to hold the fields that I needed for calculation but not for display.

Scroll forward 20 or so years and now forms are exhibiting a similar problem but I don't know at what point the form breaks. I haven't been able to make it happen. All I know is it does.

If this is what has happened to you, add a new control to the form bound to this field. Make it tiny and set its visible property to No. I set the background to bright yellow so I can easily pick out these controls in design view.

 

[adhoustonj]

Do you have a control on the form that is bound to Me.hsf_txt? If you are referencing a field in the form's RecordSource but that field is NOT bound to a control (hidden or visible) on the form, this can cause an error. I haven't run into this enough times to be able to nail down when/ the form breaks but I'll explain what I do know.

Since I started using Access in the early 90's, Reports have had this issue. You bind a report to a table or query. Some of the fields are bound to controls. But I also occasionally have other fields that are not displayed and so are not bound to controls but they are used in calculations. When I develop the report, everything works fine. I save it, and it works fine. At some point Access rewrites the RecordSource so that it selects ONLY columns which are bound to controls so the columns I am using in calculations are no longer available and the next time I run the report it fails because it can't find one of the fields used in the calculations. After a while I figured out what was happening and so created tiny hidden controls to hold the fields that I needed for calculation but not for display.

Scroll forward 20 or so years and now forms are exhibiting a similar problem but I don't know at what point the form breaks. I haven't been able to make it happen. All I know is it does.

If this is what has happened to you, add a new control to the form bound to this field. Make it tiny and set its visible property to No. I set the background to bright yellow so I can easily pick out these controls in design view.

Pat - thank you for sharing your previous experience with me.. I will test that out when I get back in the office on Monday. I thought I'd hear it was corruption issues, but the fact that you have dealt with this previously and were able to work around it gives me hope. That was my first thought to look for an unbound text box on my form call hsf_txt, and then noticed it was in my recordsource, so assumed it would pick it up also. Oh well, I won't whine about it.. I'll test this out Monday and follow up. Thank you!!

 

[arnelgp]

your DoCmd.OpenForm has Error, did you noticed it:

DoCmd.OpenForm "frmHS_pdca", , , , , , OpenArgs:=Me.hsf_id & "|" & Me.hsaf_id & "|" & Me.hsf_txt

it should be:

DoCmd.OpenForm FormName:= "frmHS_pdca", OpenArgs:=Me.hsf_id & "|" & Me.hsaf_id & "|" & Me.hsf_txt

 

[adhoustonj]

your DoCmd.OpenForm has Error, did you noticed it:

DoCmd.OpenForm "frmHS_pdca", , , , , , OpenArgs:=Me.hsf_id & "|" & Me.hsaf_id & "|" & Me.hsf_txt

it should be:

DoCmd.OpenForm FormName:= "frmHS_pdca", OpenArgs:=Me.hsf_id & "|" & Me.hsaf_id & "|" & Me.hsf_txt

Arnel, I did not, but appreciate your suggestion. Can you explain the difference between?
DoCmd.OpenForm FormName:="frmHS_pdca"

and
DoCmd.OpenForm "frmHS_pdca"


I will implement in a test db and see if it produces any effect. Looking at microsoft docs I do not see the FormName included in the openform method, but I am curious.

DoCmd.OpenForm method (Access)

Office VBA reference topic

learn.microsoft.com

 

[arnelgp]

this is the "new" one i made on post #14:

DoCmd.OpenForm FormName:="frmHS_pdca", OpenArgs:=Me.hsf_id & "|" & Me.hsaf_id & "|" & Me.hsf_txt

 

[ebs17]

If code works on most machines but not on some, it can also be because there is a data error.
Does hsf_txt have unusual content or NULL, is it a memo field (long text)? Is the text box in a special situation?