Link to SP

Question

Link to SP

James Asante

Old Hand

Points: 361
More actions
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

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

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

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 2

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.

James Asante Old Hand Points: 361 More actions · Answer 3

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

Thanks.

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 4

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.

James Asante Old Hand Points: 361 More actions · Answer 5

Hi Richard,

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

Thanks a lot

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 6

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

James Asante Old Hand Points: 361 More actions · Answer 7

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.

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 8

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