Solved

Run-Time-error 3146 ODBC

Posted on 2011-03-08
2
964 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
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 57

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn the different options available in the Backstage view in Excel 2013.
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…

815 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

7 Experts available now in Live!

Get 1:1 Help Now