CODEWITHSUNDEEP
sql-serverms-accessstored-procedures
Leehbi
Leehbi

Reputation: 779

Return stored procedure data to ms access table

I'm calling a stored procedure in SQL Server that includes parameters and returns a dataset.

I need to find a good way of importing this data into an Access table. Is it possible to do it in one go or can I attach the recordset to a query and the paste the records into a table in a second step. I'd appreciate any pointers. 

Private Sub GetOpex()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cnn = New ADODB.Connection

    cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;Initial Catalog=dbaseIntegrated Security=SSPI;"

    cnn.Open
    Set rs = New ADODB.Recordset
    Set rs = cnn.Execute("ProceName, 9, 2014, 22")

    # Insert to an Access Table


    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing 
End Sub

Upvotes: 2

Views: 5071

Answers (1)

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

You can create a Pass-through query and call a T-SQL EXECUTE to the procedure:

EXEC ProceName, 9, 2014, 22

Save it (e.g. as ExecSP), than you can run a SELECT INTO query in Access:

SELECT * INTO YOUR_NEW_TABLE
FROM ExecSP

Upvotes: 4

Related Questions