Not sure if there is a definite answer for this, but I have an Ms Access application with SQL Server 2008 as data store all tables are linked to the SQL Server from the Access application, the application is hosted on a Citrix server. All the forms in the application are bound forms. There is one particular form that is lately causing issues when I try to edit or save the data the form hangs for ever and I have to kill the session. The form is linked to a table on SQL server and the table has Identity column as primary key, what I find weird is that this form is not even as heavily used as any other forms and the table does not even have half the records as any other heavily utilized tables. There are only 4 fields on the form with only one feild being Nvarchar, other forms have way more Nvarchar fields with way more utilization and data and they do not seem to cause any issues. I am ruling out Network issues as that would affect the entire application and not just one form also the indexes on the SQL server are build daily so I don't think it is an indexing issue either. Does any one know why this would happen.
-
It happens even when I run the app locally from my PC. – Irfarino Aug 03 '12 at 02:46
2 Answers
Try rebuilding your Access front end. Create a new database, set your database-specific options to match (field behavior, auto name tracking off or on, SQL syntax level, etc.), then import all forms, reports, macros, vb code, and data from the old Access database into the new one. Set startup options properly, and see if anything is better.
Access databases can get subtly corrupted and a total rebuild is sometimes the only way to fix them.
Make sure you keep a backup.
- 48,881
- 23
- 151
- 196
-
I have rewritten the form that is causing the issue, instead of having it bound to the table, I am pulling the data via code and also updating it via code. The form is only meant to edit one record at a time based on a filter from another form, so binding it to the table and having access pull down the entire table behind the scene is unnecessary. I think that will solve the issue, but I was just curios why that would happen as this form never caused issue in years,maybe your right the form might be corrupted for some reason. – Irfarino Aug 03 '12 at 02:51
If you suspect the form is corrupted you could use the SaveAsText and LoadFromText methods to save the form definition as a text file, then reload it with a different name.
Application.SaveAsText acForm, "frmMainMenu", "frmMainMenu.txt"
Application.LoadFromText acForm, "frmMainMenu_new", "frmMainMenu.txt"
Those methods aren't a comprehensive as Erik's suggestion, but they are quick and easy. You can also try decompile to fix corruption. See the 2 answers at this link: HOW TO decompile and recompile
If corruption is not the cause of the problem, you can try stepping through your form's code in break (debug) mode. Place a temporary break point on the first executable line in your form's open procedure. If you don't have a procedure for Form Open, add one. If you're unfamiliar with setting temporary break points, you can just add the word Stop:
Private Sub Form_Open(Cancel As Integer)
Stop
Then after you open the form, you can step through the code one line at a time with the F8 key. Hopefully you will be able to identify the line which causes the code to hang.
-
I stepped through the code quite a few times, I even have my registry key enabled for ODBC trace [link](http://msdn.microsoft.com/en-us/library/bb188204%28v=SQL.90%29.aspx). When the form opens it is filtered to a record, but when I look at the ODBC trace it loads 10 records behind the scene every minute until I guess all the records are pulled down from the table, but same is true for all forms that are bound they have way more records all records get pulled down behind the scene .like I said it loads additional data which is not needed – Irfarino Aug 03 '12 at 03:40
-
So then your form's record source is a large set of records, but you filter that set down to one? It might help to show us the form's record source property. – HansUp Aug 03 '12 at 03:42
-
The form is bound to a table, the record source is a table (table name) in the form properties. Sorry forgot to mention the compact on close property of the db is also enabled so every time anyone closes the application the db is compacted. – Irfarino Aug 03 '12 at 03:48
-
I will try and decompile/recompile the db to get rid of the unwanted code. – Irfarino Aug 03 '12 at 03:52
-
For all tables, you need a pk, and you should ALSO include a timestamp column for all tables. Access uses this information to help it "figure" out how + when to update the table. – Albert D. Kallal Aug 04 '12 at 16:35