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?
cipriano555Asked:
Who is Participating?
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.

BitsqueezerCommented:
Hi,

there would be different methods for that purpose.

1. You can execute the stored procedure using ADO and get the ADO recordset back from the stored procedure. Then you can use VBA to loop through the recordset and write each record into the desired local table. That would be the most straight-forward way because it directly executes the SP and directly write the result into the desired table - disadvantage is that depending on the number of columns and rows this is not very performant.

2. You could let the SP write the result into a selection table. This would have any column the SP produces and additionally have a UserID column. The SP writes the columns and rows into this selection table together with the UserID and deletes all existing entries of this UserID first. The frontend can then simply use a view to get the desired rows back so that an INSERT...SELECT could import the data into the local table. Advantage is that this is very fast and should also only needs a few seconds with large amount of data. Disadvantage could be that the view can open a security problem if used by other users by simply changing the UserID, could be done i.e. with an Excel query. This could be secured if you also generate a random ID like the GUID ("uniqueidentifier") in the output table and sending this GUID to the desired frontend as return value (OUTPUT parameter) so that it can insert the data into the local table and execute a second SP to directly delete the produced rows from the server table, very little risk that the rows can then be used by someone else (it would need to hack the GUID in a few seconds and to know when this was executed and by which user ID).

3. You could let the SP write the result into an external file (that could also be an Access database) at a specific position on a fileserver which only the frontends know. In case of an Access database a link to the query could be made on the frontend and so the data could be imported from a file which the users don't know. Of course this method is not the same performant as the second way descrobed above.

There are of course more solutions possible, that are only three possible ways.

By the way: If you only secure the SP by using a UserID as parameter this is no real security as a hacker could execute the SP as often as he want by changing the user ID each time and so get all the data he wants...;-)

Cheers,

Christian
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
ste5anSenior DeveloperCommented:
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.
0
cipriano555Author Commented:
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
0
cipriano555Author Commented:
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.
0
BitsqueezerCommented:
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
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.