Solved MS Access Forms cannot allow edit and shows #deleted record in Link tables in MySQL

[nector]

This is very strange indeed I have never seen this before:
(1) Table with data type BIT(Yes/No) in MYSQL will not allow editing , but if I delete that field then all is fine. What do we do here we need those fields
(2) When inserting the data in MYSQL the update shows horrible #Delete error values all the fields , but if I refresh the form all is back to normal , kindly assist

I was think its ODBC 8.0.11 connector for MYSQL , I tried 9.0 its is the same, if there is nothing that can be done is there a way to requery the subform every after completing a line because this occurs when you move into the second line.

 

[theDBguy]

Not sure if this is relevant, but the same thing could happen with a linked SQL Server table if the bit field doesn't have a default value assigned since Access cannot contain Null values in bit fields.

 

[tvanstiphout]

Not sure if this is relevant, but the same thing could happen with a linked SQL Server table if the bit field doesn't have a default value assigned since Access cannot contain Null values in bit fields.

Another possibility is that the table does not have a PK. That is a really bad idea on its own, and with linked tables can lead to updates failing.

 

[nector]

The PK is there, I remove the field with a BIT datatype all is fine, that is where I am getting puzzled.

I use requery on the add or save button the #delete error also goes away on the form , now on the subform where do I put the Requery?????????/

 

[theDBguy]

The PK is there, I remove the field with a BIT datatype all is fine, that is where I am getting puzzled.

I use requery on the add or save button the #delete error also goes away on the form , now on the subform where do I put the Requery?????????/

Did you see my post? Was it applicable?

 

[Josef P.]

What is the data type of the bit data field in the linked table?

 

[nector]

@theDBguy
Not sure if this is relevant, but the same thing could happen with a linked SQL Server table if the bit field doesn't have a default value assigned since Access cannot contain Null values in bit fields.

Actually I followed your advise and it has sorted out 90% what happens if the BIT is still required? also the TimeStamp is behaving in smilar fashion If do not put the timestamp field all is fine , but the moment I use the TimeStamp datatype again I cannot insert anything or update , now the problem comes in when two people are editing the same record it says data cannot be saved because its being edited by another person, that is why I needed TimeStamp.

In SQL Server where MS Access is a front end it does happen , but goes when all the tables have Timestamp field.

 

[nector]

Finally the Timestamp is solved by using Now() function

 

[ComputerVersteher]

Hi,

for Yes/No-Fields use TinyInt type (0 is true <>0 is false). Also read the docs, especially the examples and notes/tips section.

This will also reveal the correct usage of timestamps. They need Current_Timestamp as default and on update.

 

[nector]

@ComputerVersteher
Mnay thanks.

I can tell you after implimenting the following all is working great!

(1) #Deleted data , here I'm using Me.requery to clear that
(2) Timestamp = require current time on both Default and atribute Plus a null tick
(3) BIT data type I have gotten lid of it altogether.

Everything is great and the speed six times faster than MS SQL Server and the cost is almost seven times lower. No need for views MS Access handle all the report as if I'm using a local database