Solved

Run-Time-error 3146 ODBC

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
get column names from table in vb.net 8 52
Showing random records from database 10 46
awk and Pythagoras? 5 20
question about results where i dont have a match 3 23
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…
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…

821 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