DataErr 3146

[bkessinger]

Hi All,

I received the above error on a form and not sure what it means. An explanation would be most helpful. TIA.

Bill K.

[HansV]

The message for this error is "ODBC--call failed". Is your form bound to a linked SQL Server table or similar? Or do you have VBA code that connects to an external data source?

[bkessinger]

Yes, the form is bound to a linked SQL Server table.

[HansV]

The error says that there was a problem with the connection to SQL Server, but unfortunately it doesn't provide specific info. Do you know if the error occurs during the execution of code, and if so, does this code use DAO or ADODB?

[bkessinger]

See Attachment of the References

[HansV]

Thanks, so you have references to both the DAO and ADO libraries.
But do you know whether the error occurred while VBA code was being executed, for example an event procedure of the form or of a control?

[bkessinger]

After rechecking, I could not find VBA code that would be executing. The only event procedure for this form is the On Error and On Exit. Controls on the form have no associated events. It is a subform.

The error occurs when I make a certain change to a record on the subform. I could find no events that would trigger this.

[HansV]

What does the On Error event procedure look like?

[bkessinger]

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3146 Then
MsgBox "This trainee is already scheduled for the next instance of this course and may not be automatically scheduled again for it. Changing the attended flag to 'N' is attempting to automatically schedule this trainee for the next course..."
Response = acDataErrContinue
End If

End Sub

This leads me to believe there is some code somewhere, I just don't know where or how.

[HansV]

This probably means that the change that you are attempting would cause a duplicate index or something similar. The update fails, and the ODBC driver used to connect to the SQL Server database returns error 3146.

You'll have to investigate how the change that you attempted violates the rules.

[bkessinger]

Will do. Thanks for your help.

[Wendell]

Another thing you might check - does the table where you are adding a record have a timestamp field attached to it? We see this error occasionally, especially on subforms when you try to add a record, but it seems to be a bogus condition of some sort, and not really a duplicate. Adding the timestamp field to the table design resolves the issue. For that reason we put timestamp fields on nearly all SQL Server tables.

[bkessinger]

I did discover by just running the query that the subform is based on yielded an error indicating that a duplicate record can't be created. When records are added using this form, they are added to a junction table in a many-many relationship. Who ever set this up used the foreign keys from the 2 tables as a multi-field key. All I was trying to do is change a record. I don't think this multi-field key is necessary on a junction table. Come Monday, I plan to get rid of the multi-field key and try again.

Wendell, no time stamps involved. Thanks for assisting.

[HansV]

Take a good look at the junction table and the tables it links to - in most situations, you *do* want the combination of the two foreign keys to be unique. So you should only remove the multi-field key if you are certain that it won't cause conflicts.

[bkessinger]

After researching into this problem with my SQL DBA, we found that a duplicate record is trying to be created. We will need to address the business rule for this. Thanks again for your assistance.

Bill K.

[Pat]

See this post which was a Hans reply to me way back re this same message.
It shows exactly what SQL Server is objecting to, this helped me more than once.

[bkessinger]

Thanks Pat. I'll give it a try.