Access Linked Table to MSSQL Stored Procedure

I have created a stored procedure in MS SQL that returns a table of data the same as a view/query.

It needs to be a stored procedure, as it creates temp tables etc...

my question is, how do I add a linked table in access to this stored procedure so access can query it?

or is there another / better way?
DFPITCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
if you are returning data from Temp Table, in that case you are out of luck, scope of temp table will be reached before you use it from access as temp table. you have to use permanent table only.
0
DFPITCAuthor Commented:
I don't need to access a temp table, I need to access the table returned by the stored procedure.
0
mdagisCommented:
Write the sp in SQL Server and then create a query in Access. Don't add any tables to the query. Select the menu 'Query' then 'SQL Specific' the 'Pass Trough Query'.

Select the properties for the query and set up the odbc connection. Select 'Yes' for setting the password, then type your query something like 'EXEC usp_spName'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alan WarrenApplications DeveloperCommented:
Hi mdagis,

to work with Sql Server stored procedures in Access and get the full features, you would create and Access Data Project (ADP) which will allow you to work with views and Stored procedures locally.

If you want to gank some data from a remote sql server catalog and populate a table in an Access mdb, you can, using an ADODB.Connection to return an ADODB.Recordset populated with the data output from the Sql Server stored procedure.

For the latter to work, we need to know a few things and you need to know a few things:
We need to know:
Does your stored procedure require input parameters and/or output parameters?
If it does, could you please define the required parameters for us please?

You need to know: (dont publish these items here!) you just need to know them.
The name of the server: eg: tcp:sqlcats.somedomain.net
The sql catalog:
The login UserID:
The login User password:

Then you can create an ADODB.Connection from the Access.mdb using VBA.
Using something like the following code sample (AdoConnectSQL)
Then you can create an ADODB.Recordset using something like the following code sample (AdoRecordsetPop)

Once you have the recordset, you can use the rows and fields in the recordset object to do as you wish with.



HTH

Alan ";0)














' AdoConnectSQL sample
' Reference: Microsoft ActiveX Data Objects 2.8 Object Library
Public Function AdoConnectSQL(strServer As String, strUID As String, strPwd As String, _
                              strDatabase As String, ByRef strErrMsg As String) As ADODB.Connection

' Success: Returns an ADODB.Connection object
' Failure: Returns an error string and nothing in lieu of the ADODB.Connection object


  On Error GoTo err_AdoConnectSQL
  
  Dim objConnect As New ADODB.Connection
  Dim strConnectionString As String
  
  strConnectionString = "Driver={SQL SERVER}; Server=" & strServer & ";UID=" & strUID & ";PWD=" & strPwd & ";Database=" & strDatabase
  
  With objConnect
    .ConnectionString = strConnectionString
    .Open
  End With
  
  Set AdoConnectSQL = objConnect
  
  
exit_AdoConnectSQL:
  On Error Resume Next
  Exit Function

  
err_AdoConnectSQL:
  
  strErrMsg = Err.Description
  Set objConnect = Nothing
  Resume exit_AdoConnectSQL
  
End Function




' AdoRecordsetPop Sample
Public Function AdoRecordsetPop(ByRef objConnect As ADODB.Connection, strSql As String, ByRef strErrMsg As String) As ADODB.Recordset

' Success: Returns a populated ADODB.Recordset object
' Failure: Returns an error string and nothing in lieu of the ADODB.Recordset object


  On Error GoTo err_AdoRecordsetPop
  
  Dim objRecordset As New ADODB.Recordset
  
  
  With objRecordset
    .Open strSql, objConnect, adOpenForwardOnly, adLockReadOnly
  End With
  
  Set AdoRecordsetPop = objRecordset
  
  
exit_AdoRecordsetPop:
  On Error Resume Next
  Exit Function

  
err_AdoRecordsetPop:
  
  strErrMsg = Err.Description
  Set objRecordset = Nothing
  Resume exit_AdoRecordsetPop
  
End Function

Open in new window

0
DFPITCAuthor Commented:
Thanks everyone else for your help, however mdagis solved the problem for me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.