?
Solved

Run-Time-error 3146 ODBC

Posted on 2011-03-08
2
Medium Priority
?
969 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 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 …
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 the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

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