0

I have an Access application for tracking students and study programs, financial aid, etc. This is an application which has been running many years.

Recently we moved the back end from Access to a cloud-based MySQL database on an AWS server. We are having various problems with it. There were several instances where records were not being saved and we were being told that the record was being edited by someone else. We made the following changes to the MySQL connector settings and it seemed to work ok for a while.

enter image description here

Now we are having issues when we change information on a sub-form then click into the main form (thus saving the sub-form data by default).

enter image description here

The main form above is for the Student Profile table, which contains information about the study program the student is taking, and the financial aid being granted. The bit on the left is a sub-form to the Student table.

When we go into the notes field on the lower left (doesn't have to be notes field, that's just where we make most of the changes) and make a change, then click on an area or field in the main form, the student record saves... sometimes. Quite often we are getting an "ODBC--call failed" error, and when we click OK on that, immediately get an "Object invalid or no longer set" error.

When we click OK on both of these, the changes are still there usually, although sometimes all fields on the sub-form must be refreshed.

Does anybody have any experience with this type if issue?

Thanks...

RMittelman
  • 319
  • 3
  • 16
  • You are probably loosing connection to the server. Try ping server_ip -t and see if you are loosing/dropping connection. 2> try DSN less connectionstring – Krish Dec 12 '16 at 23:17
  • Thanks @krish. Not sure it's losing connection, because immediately after issue occurs, I can click on another student in list and all of his/her data comes up. If I am losing connection, then it must be randomly and for a very short time. Also, issue doesn't occur if table is open in data sheet mode and I make changes there. Only happens when using bound form/sub-form. – RMittelman Dec 13 '16 at 16:33
  • "ODBC--call failed" (without server error) happens when you lose/no connection to the table. As I said try DSN less connectionstring to your tables which allows you to programmatically refresh the link to the backend server – Krish Dec 13 '16 at 16:38
  • Thanks, I will try this. I guess I can find a connection string on connectionstrings.com. I'm not sure how to establish dsn-less connection because when I go to link tables to odbc data source, first thing that pops up is the ODBC dialog where I choose the DSN. Could you advise how to do that without DSN? Also, I guess I have to modify all pass-through queries, right? Thanks... – RMittelman Dec 13 '16 at 16:58
  • search for linked tables vba or dsn less ms access: or check this, http://stackoverflow.com/questions/26923379/keeping-uid-and-pwd-out-of-an-ado-connection-string-in-an-odbc-dsn-less-database/27041299#27041299 – Krish Dec 13 '16 at 17:02
  • Ok, I read the article and it seems very simple. However, I'm having trouble understanding how this will help me. The normal case is I can make changes in the sub-form 4 out of 5 times with no issues, then I get the error, then the next few times there is no error. It is very intermittent. Is there something inherently different about dsn-less connections that makes them more stable? Thanks... – RMittelman Dec 13 '16 at 17:35

0 Answers0