Post reply

Link to SP

  • May 23, 2005 at 8:03 am

    #76317

    I am trying to link sql server 2000 sp to Access using the macro:

    Action: TransferDatabase

    Transfer Type: Link

    Database Type: ODBC Database

    Database Name: ODBC;DRIVER=SQL Server;SERVER=HQ-STT-2;APP=Microsoft Office XP;WSID=HQ-STT-2;DATABASE=adp1SQL;Trusted_Connection=Yes;TABLE=dbo.sp_ConsolidatedTC

    Object type: Stored Procedure

    Source: sp_ConsolidatedTC

    Destination: sp_ConsolidatedTC

    Structure Only: No

    Linking tables works but linking sp returns the error:"MS Jet database could not find the object sp_consolidatedTC.."

    Has someone done it before?

    Please help

     

  • May 23, 2005 at 8:17 am

    #560513

    You cannot "link" a stored-procedure this way.  Access knows that sp's are recordsets and you will need to EXEC it via ADOCMD and ADORS to return the results into a recordset...



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • May 23, 2005 at 5:30 pm

    #560634

    To execute the SP create a Query in Access as a Pass Through Query. Then

    Exec sp_ConsolidatedTC

    will execute the query. It would help to know the purpose of the SP.

  • May 23, 2005 at 10:32 pm

    #560654

    Works fine but prompts for Data Source. Any way to avoid the prompt?

    Thanks.

  • May 24, 2005 at 12:56 am

    #560666

    On the toolbar in design view , there is a properties button.  It allows you to specify the ODBC connection string.

    One further point is that it asks - Return Records Y/N.

    Choose which option agrees with sp.

    Richard

     

    The text in this Query can be changed from Access - a handy way of Exec sp.

     

     

  • May 24, 2005 at 1:47 am

    #560680

    Hi Richard,

    Works very fine. How can I ask for parameter values set in the sp in a the Access passthrough?

    Thanks a lot

  • May 24, 2005 at 2:03 am

    #560685

    James,

    I keep a generic Pass Tru Query called QExec.

    form the following code you will see how it works:

    mSQL = "EXEC SP_ADDCUST "

    mSQL = mSQL & "'" & MCUSTCODE & "'"

    mSQL = mSQL & ", '" & MCUSTNAME & "'"

    mSQL = mSQL & ", '" & Format(MDOB, "mm/DD/YYYY") & "'"

    mSQL = mSQL & ", " & MSALARY

    CurrentDb.QueryDefs("QExec").sql = mSQL

    DoCmd.OpenQuery "qexec"

    Each time it is run it changes the .sql in the QExec and effectively executes the sp.

    Nice article from Danny Lesandrini - many thanks to him.

    http://www.databasejournal.com/features/msaccess/article.php/3407531

    Richard

     

     

  • May 24, 2005 at 3:18 am

    #560701

    Hi Richard,

    Thanks very much I will look at the article more closely. My last question. The Access database with the ODBC links works well on my pc but when I emailed it to another user who has access to the server it returned ODBC error as he tried to open the linked tables and queries. What should I check?

    Thanks.

     

  • May 24, 2005 at 3:23 am

    #560702

    James,

    If he tried from Access then he probably hasn't setup the DSN in ODBC.

    Control Panel, Admin, ODBC.

    Create the same DSN name as you have saved in yours and it should work. Otherwise its SQL priveleges and as I'm new its still a mystery to me.

    Richard

     

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply