asked on

How to copy some data from aSQL Server table to a Microsoft Access 2013 table

In SQL Server 2012, I have a stored procedure which returns rows from a data table. I am writing an application which uses Microsoft Access 2013 as a front end to this database. I want to copy the rows returned by the procedure into a local Access table which has the same columns and compatible data types as the SQL Server table.

Given a User ID, the routine returns the data rows the user is allowed to see. The rows can be obtained from a query like this:

EXEC [dbo].[PERMITTED_DATA_VALUES_FOR_USER_ID] @USER_ID = 1

Within a code module, say as the after click event handler for a button, how would I run this query and store the results in the Access table?

ASKER CERTIFIED SOLUTION

Bitsqueezer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

And last but not least:

Use a pass-through query in Access, if the procedure returns only one recordset. The query text must include SET NOCOUNT ON. E.g.

SET NOCOUNT ON;

EXEC dbo.PERMITTED_DATA_VALUES_FOR_USER_ID @USER_ID = 1;

Open in new window

Then you can use the pass-through query in Access for an append query.

cipriano555

ASKER

I'll try method one. Number of rows returned is small. Also, I am developing a prototype so I'm not concerned with security at this point.

Thanks for the great help.

Cipriano

cipriano555

ASKER

Question: You said "Then you can use VBA to loop through the recordset and write each record into the desired local table."

I don't know the syntax for doing that. I'll seach around the web to try to find something. But I am using ADO to populate a recordset with the data I want to copy into an Access table,

If you can give me any direction on this I would appreciate it.

Bitsqueezer

Hi,

you can find all you need in this Microsoft article:

https://support.microsoft.com/en-us/kb/185125

You only don't need the code example at the beginning to create a stored procedure with VBA, but then there is code to execute a stored procedure and get the result as ADO recordset which you then can either INSERT or create a second recordset for inserting (the recordset for the local table can be DAO.

Cheers,

Christian