DataErr 3146
-
- StarLounger
- Posts: 71
- Joined: 27 Aug 2010, 09:13
DataErr 3146
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.
I received the above error on a form and not sure what it means. An explanation would be most helpful. TIA.
Bill K.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DataErr 3146
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?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 71
- Joined: 27 Aug 2010, 09:13
Re: DataErr 3146
Yes, the form is bound to a linked SQL Server table.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DataErr 3146
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?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 71
- Joined: 27 Aug 2010, 09:13
Re: DataErr 3146
See Attachment of the References
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DataErr 3146
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?
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?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 71
- Joined: 27 Aug 2010, 09:13
Re: DataErr 3146
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.
The error occurs when I make a certain change to a record on the subform. I could find no events that would trigger this.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- StarLounger
- Posts: 71
- Joined: 27 Aug 2010, 09:13
Re: DataErr 3146
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.
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.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DataErr 3146
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.
You'll have to investigate how the change that you attempted violates the rules.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 71
- Joined: 27 Aug 2010, 09:13
Re: DataErr 3146
Will do. Thanks for your help.
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: DataErr 3146
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.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- StarLounger
- Posts: 71
- Joined: 27 Aug 2010, 09:13
Re: DataErr 3146
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.
Wendell, no time stamps involved. Thanks for assisting.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DataErr 3146
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 71
- Joined: 27 Aug 2010, 09:13
Re: DataErr 3146
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.
Bill K.
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: DataErr 3146
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.
It shows exactly what SQL Server is objecting to, this helped me more than once.
-
- StarLounger
- Posts: 71
- Joined: 27 Aug 2010, 09:13
Re: DataErr 3146
Thanks Pat. I'll give it a try.