Write Conflict - Run-time error '3157’ (1 Viewer)

Ashfaque

Student
Local time
Tomorrow, 01:59
Joined
Sep 6, 2004
Messages
894
Hi,

1. Linked tbls from Sql Server in my Access FE (tbles: T_ProjectBaseContracts and T_ProjAssigneeDetails)
2. Main form with subform - where main form is unbound and subform is linked with tbl and it bound with tbl fields directly.
3. Main form is in relationship is one-to-many. Master and Child field of forms with each other is ProjNum

When adding new record I have no issue. But when I m editing exiting record, write conflict is always knowing and I cant update the record in sub-form.

Run-time error ‘3157’
ODBC – Update on linked table ‘T_ProjectBaseContracts’ failed

Both form on linked on ProjNum field.

The strange thing is when I update record directly in tbl T_ProjAssigneeDetails ...then have no issue. But thru form it is not possible...displaying above error.

Can anyone help me out
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:29
Joined
May 7, 2009
Messages
19,169
do you have an Unbound textbox (ProjNum) on the main form?
if not, how did you established the Master/Child link fields?
 

Ashfaque

Student
Local time
Tomorrow, 01:59
Joined
Sep 6, 2004
Messages
894
do you have an Unbound textbox (ProjNum) on the main form?
if not, how did you established the Master/Child link fields?
yes..... I have unbound textbox (ProjNum) on main form and same textbox (ProjNum) on subform. But on subform it is bound with field.
 

isladogs

MVP / VIP
Local time
Today, 20:29
Joined
Jan 14, 2017
Messages
18,186
If you have any bit fields in your SQL tables, make sure that you set the default value to either 0 or 1 as preferred then change any null values to that default in SQL Server
 

Ashfaque

Student
Local time
Tomorrow, 01:59
Joined
Sep 6, 2004
Messages
894
If you have any bit fields in your SQL tables, make sure that you set the default value to either 0 or 1 as preferred then change any null values to that default in SQL Server
Thanks Isladog,

Excellent observation and solution....

I did it and it worked....

Thanks a ton....

Regards,
 

isladogs

MVP / VIP
Local time
Today, 20:29
Joined
Jan 14, 2017
Messages
18,186
You're welcome. You will also need to do that in each SQL table with bit fields to avoid future problems
 

Ashfaque

Student
Local time
Tomorrow, 01:59
Joined
Sep 6, 2004
Messages
894
Sorry, It is again appearing...let me scan whole tables with such kind of fields where I can set 0 as default value.....
 

Minty

AWF VIP
Local time
Today, 20:29
Joined
Jul 26, 2013
Messages
10,355
Any Bit fields in any table will cause that even in a join, even if they aren't being written to.
 

Ashfaque

Student
Local time
Tomorrow, 01:59
Joined
Sep 6, 2004
Messages
894
Minty,
I have both tbls and set all bit fields default value to 0. I tried even removing direct tbl as datasource of child tbl and instead I placed query based on the child tbl. But still same.

Then I deleted child table and created fresh one and when I started entering value direct to sql server linked tbl....no issue at all. Further I removed old child form from main and placed the new child form just created and set the ProjNum as linked field. But the issue is still unsolved.

Please note that ProjNum field is text field with no Duplicate in main tbl but same field in child form is duplicate allowed.

Shall I upload the db by converting the server tbl to local ?
 

Minty

AWF VIP
Local time
Today, 20:29
Joined
Jul 26, 2013
Messages
10,355
You must make sure all the existing values are set to 0 or 1 as well.

Setting the default works for new records, but not existing ones.
Not sure from your description if that has been handled.
 

Ashfaque

Student
Local time
Tomorrow, 01:59
Joined
Sep 6, 2004
Messages
894
You must make sure all the existing values are set to 0 or 1 as well.

Setting the default works for new records, but not existing ones.
Not sure from your description if that has been handled.
I just tried converting relevant tbls to local and edited record. It has no issue. It means when tble are linked from server, write conflict problem occurs.

For Bit and Int value I already set default value as 0. How about varchar or NvarChar field? Do I need to set default value for these text type field?
 

Minty

AWF VIP
Local time
Today, 20:29
Joined
Jul 26, 2013
Messages
10,355
Bit data is the only one that has the issue, and converting them to a local Access table will remove the problem because they are no longer able to have a null value.

As I said you have to update all your existing table data values to either a 1 or a zero.
http://allenbrowne.com/NoYesNo.html probably is the best explanation.

Also, make sure your datetime fields in SQL aren't DateTime2 data type.
That can cause some issues use - either the old DateTime or SmallDateTime
 

Ashfaque

Student
Local time
Tomorrow, 01:59
Joined
Sep 6, 2004
Messages
894
Thanks again Minty,

This time I kept all default bit value to 1 and I removed CHild and Masterfield link on form. I know that will show me all the records in subform irrelevant to main form record.

And just for trial I removed link between Masterfield and Childfield which was earlier set on ProjNum, and it updated record even I edited record in subform manytimes.

So I believe there is problem in ProjNum filed which I was linking. This is normal process know if we set links both fields, relevant subform record will display when we selects record from main form.

So I dont know how to sort out this issue. Please extend your help.
 

Attachments

  • Bit Update Problem.jpg
    Bit Update Problem.jpg
    74.6 KB · Views: 513

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:29
Joined
May 7, 2009
Messages
19,169
on mssql studio or any tools you use.
go to the child table (that your subform is using).
if there is an Autonumber key and set as PK, remove it as PK.
re-instate your Master/Child Link Fields on your subform.
 

Users who are viewing this thread

Top Bottom