Access Linked Table to MSSQL Stored Procedure

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.

I don't need to access a temp table, I need to access the table returned by the stored procedure.

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

Select allOpen in new window

Thanks everyone else for your help, however mdagis solved the problem for me.