Solved

Run-Time-error 3146 ODBC

Posted on 2011-03-08
2
967 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Author Comment

by:psueoc
ID: 35072724
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 35072877

  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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
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…

726 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