Access Form can't edit MYSQL Fields

[hmanvel]

Using Access 2000 as a front end to MySQL database on a web server via ODBC. I stumbled thru the design etc and now have everything working very well such that remote employees can access database without VPN or anything. But I have one big problem and cannot seem to solve it. The main customer info form which accesses name address etc along with notes, will not allow me to edit data. Most of the other forms in the database, as well as some subforms to this main form, DO allow edits. Also I can go directly to the linked table in access which the main form is based on, and I can edit fields there. Just not via the form itself. I have looked at all the settings in the form design and cannot see anything unusual or different from my other forms that work. Anybody have any advice?

 

[georgedwilkinson]

It sounds like you covered all your bases. Are you absolutley certain you've checked every property of the form/controls? Sounds odd you would have a problem.

Not sure about MySQL, but on occasion, I'll change my SQL Server design and forget to change user access on the server. That causes this problem for me.

Regarding:

now have everything working very well such that remote employees can access database without VPN or anything

That's quite an accomplishment. Can you share how you did that with us?

 

[Banana]

Some ideas off the top of head-

1) Are you using column-level security? Any alteration to table may delete the privilege and thus lock users out.

2) Is the form based on a table or a query? If table, I would strongly advise that you use a query, even if it's just all fields from a table.

3) If you open the form in design view, and edit the recordsource in Query builder, can you edit data within QBE's datasheet view?


What error do you get if you try to edit those data in forms?


George-

MySQL doesn't require VPN or anything because it uses TCP/IP connection, which the ODBC Driver comes with a TCP client to handle the connection, so it work pretty much like requesting a web page.

Of course, there's that security issue, but in my case, I threw in a SSH client library to encrypt the connection with the MySQL server in conjecture with chroot to corral the users into a lonely little jail I've made for them. MySQL also supports SSL natively, but I eschewed them because of certificate PITA.

 

[hmanvel]

Banana - 1) I dont "think" I am using column level security, haven't been able to locate anything within MYSQL (I use Navicat to access database directly when troubleshooting etc.) or within Access. 2)the form recordsource is a select query of a single linked table, however there is no access query per se. Maybe there should be? why? 3)yes I can edit fields fine when in the datasheet view of QBE. Thoughts? Many Thanks.

 

[Banana]

So, if you can edit the fields from QBE for the same SQL statement as the form, then I am quite convinced the problem is with the form.

Take a look at its properties... Are AllowEdits, AllowDeletions, AllowInserts all set to Yes?

Is form being opened from VBA? If so, in read only mode?

If nothing leaps out to you, try creating a new blank form and copy all controls & code to the new form, and see how it work.

Does it have subforms or is it a subform?