Solved

Run-Time-error 3146 ODBC

Posted on 2011-03-08
2
961 Views
Last Modified: 2012-06-27
I am receiving an error when attempting to run a function that calls a SQL server stored procedure via ms access.  It completes successfully when using the sample function which calls sp_server_info.  But it displays an error (Run-Time-error 3146 ODBC --call failed) when I try it with a different stored procedure from the same database.  So I believe I have eliminated the ODBC connection string as a possible issue.  Below is the code with both stored procedures and functions so I can toggle between the sample and production version.  I am testing them from the Immediate window as ?ParamSPT2(500) and ?ParamSPT2 respectively.
  Your help is appreciated.

     Function ParamSPT2()
        Dim MyDb As Database, MyQry As QueryDef, MyRS As Recordset
         Set MyDb = CurrentDb()
         Set MyQry = MyDb.CreateQueryDef("")

         ' Type a connect string using the appropriate values for your
         ' server.
         MyQry.connect = "ODBC;DSN=DEV;Description=EPM_Development;UID=user1;Trusted_Connection=Yes;DATABASE=IBIS Import"

         ' Set the SQL property and concatenate the variables.
         'MyQry.SQL = "sp_server_info " & MyParam
         MyQry.SQL = "Get_IBIX_Success_Totals_By_Date"

         MyQry.ReturnsRecords = True
         Set MyRS = MyQry.OpenRecordset()
         MyRS.MoveFirst

         Debug.Print MyRS!attribute_id, MyRS!attribute_name, MyRS!attribute_value

         MyQry.Close
         MyRS.Close
         MyDb.Close

      End Function
0
Comment
Question by:psueoc
2 Comments
 

Author Comment

by:psueoc
Comment Utility
Revised code:

'Function ParamSPT2(MyParam As String)    
 Function ParamSPT2()
        Dim MyDb As Database, MyQry As QueryDef, MyRS As Recordset
         Set MyDb = CurrentDb()
         Set MyQry = MyDb.CreateQueryDef("")

         ' Type a connect string using the appropriate values for your
         ' server.
         MyQry.connect = "ODBC;DSN=DEV;Description=EPM_Development;UID=user1;Trusted_Connection=Yes;DATABASE=IBIS Import"

         ' Set the SQL property and concatenate the variables.
         'MyQry.SQL = "sp_server_info " & MyParam
         MyQry.SQL = "Get_IBIX_Success_Totals_By_Date"

         MyQry.ReturnsRecords = True
         Set MyRS = MyQry.OpenRecordset()
         MyRS.MoveFirst

         Debug.Print MyRS!attribute_id, MyRS!attribute_name, MyRS!attribute_value

         MyQry.Close
         MyRS.Close
         MyDb.Close

      End Function
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility

  As an add-on comment, you may want to extend your error handling a bit to get the specific error messages:

ACC2000: How to Trap Specific ODBC Error Messages
http://support.microsoft.com/kb/209855

  The 3146 is just a generic message to let you know an error occured.

JimD.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now