PRB: DAO Run-Time Error 3146 When Modifying SQLServer Data

Article translations Article translations
Article ID: 195225 - View products that this article applies to.
This article was previously published under Q195225
Expand all | Collapse all

On This Page

SYMPTOMS

When attempting to delete or update a record in a SQL Server cursor, the delete or update operation succeeds, but the following error returns:

Run-time error 3146 "ODBC call failed".

CAUSE

This behavior occurs when SQL Server does not return a message indicating the number of rows returned by a statement. SQL Server does not return a message indicating the number of rows affected by a statement after the following commands have been executed on SQL Server:


sp_configure "user options", 512
SET NOCOUNT ON

				

MORE INFORMATION

The SQL Server stored procedure, sp_configure, displays or changes global SQL Server configuration settings. EXECuting the sp_configure using the following syntax turns off the message returned at the end of each statement indicating the number of rows affected by the statement:


sp_configure "user options", 512

				


EXECuting the following SQL Server command also turns off the message returned at the end of each statement indicating the number of rows affected by the statement:


SET NOCOUNT ON

				


SET NOCOUNT ON also prevents SQL Server from sending DONE_IN_PROC messages to the client for each statement executed in a stored procedure.

Steps to Reproduce Behavior


  1. Start ISQL-W and run the following command to retrieve the current global SQL Server user option configuration settings:
    
    sp_configure 'user options'
    						
    WARNING: The following code changes SQL Server global configuration settings and may impact triggers as well as stored procedures and should be executed with extreme caution.

  2. Start ISQL-W and run the following commands:
    
       sp_configure 'user options',512
       GO
       RECONFIGURE
       GO
       SET NOCOUNT ON
       GO
    						
  3. Create a new Visual Basic Project, and add a reference to the Microsoft DAO 3.5 Object Library. Add three text boxes and two command buttons to Form1.
  4. Paste the following code into the form.

    Note You must change UID <username> and PWD <strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.
    
       Dim wk As Workspace
       Dim rs As Recordset
       Dim cn As Connection
    
       Private Sub Form_Load()
       Dim strConnect As String
       Set wk = DBEngine.CreateWorkspace("ODBCDirect", "", "", dbUseODBC)
                strConnect = "ODBC;DATABASE=PUBS;UID=<user name>;PWD=<strong password>;DSN=MY_DSN"
       Set cn = wk.OpenConnection("Connect1", dbDriverNoPrompt, _
                False, strConnect)
       Set rs = cn.OpenRecordset("SELECT * FROM STORES", dbOpenDynamic, _
                0, dbOptimistic)
       rs.MoveFirst
              Text1.Text = rs.Fields("STOR_ID")
              Text2.Text = rs.Fields("STOR_NAME")
              Text3.Text = rs.Fields("STOR_ADDRESS")
           End Sub
    
      Private Sub Form_Unload(Cancel As Integer)
           rs.Close
           cn.Close
           wk.Close
      End Sub
    
      Private Sub Command1_Click()
           rs.Edit
           rs.Fields("STOR_NAME") = Text2.Text
           rs.Fields("STOR_ADDRESS") = Text3.Text
           rs.Update
      End Sub
    
      Private Sub Command2_Click()
          Unload Me
      End Sub
    
    						
  5. Run the form and click Command Button 1. The following error message is returned:
    "Run-time error '3146': ODBC call failed".
  6. Restore the global SQL Server user option configuration settings to the original values.

REFERENCES

Transact - SQL Reference Help; search on: "sp_configure"; "SET".

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by John R. Desch, Microsoft Corporation.

Properties

Article ID: 195225 - Last Review: March 2, 2005 - Revision: 3.3
APPLIES TO
  • Microsoft Visual Basic 4.0 Enterprise Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
Keywords: 
kbprb KB195225

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com