PRB: DAO Run-Time Error 3146 When Modifying SQLServer Data
sp_configure "user options", 512SET NOCOUNT ON
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
- Start ISQL-W and run the following command to retrieve the current global SQL Server user option configuration settings: 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.
sp_configure 'user options'
- Start ISQL-W and run the following commands:
sp_configure 'user options',512 GO RECONFIGURE GO SET NOCOUNT ON GO
- 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.
- 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
- Run the form and click Command Button 1. The following error message is returned: "Run-time error '3146': ODBC call failed".
- Restore the global SQL Server user option configuration settings to the original values.
(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by John R. Desch, Microsoft Corporation.
Article ID: 195225 - Last Review: 12/05/2015 09:36:26 - Revision: 3.3
- kbnosurvey kbarchive kbprb KB195225