MS SQL and export from it?

Question

Microsoft Access

Good day to all.
In general, the database is not my profile and it brought me there with a crazy wind.
There is a task, with the help of a script, let me quote:

One click and you're done!

move table from SQL database (2008 R2) to Access. (And back)
ODBC won't work.
I so understand in 1 party by means of ADO.

Answer 1

Can I clarify why? Isn't it easier to abstract from the database by writing a web muzzle, with which it will be easier for users to work even from a PC, even from a tablet, even from a smartphone, than to tie everyone to MS Office and Access, which, ahem, comes either separately or in as part of MS Orffice Pro for 18k rubles per PC.
And to transfer basis, for example on SQL Express if to buy normal MS SQL the company will not pull?

Answer 2

Right click on the database -> Tasks -> Export Data. You set everything up there. In general, this is already a solution. But if you need to "just click and it's done", then at the end there is an option to save the dtsx package instead of starting the export. Write a batch file simply that will run it. This can be done manually, or you can run dtexec.exe and configure all the launch options (in the main path and connection strings), copy the finished line with all the parameters from the last tab. Save it to a batch file and that's it.
Identical, remember that the package formats between versions of the esquel server differ. If you do it under 2008, then for 2005, for example, it will not start. Or on R2.
This approach will allow you to do without programming at all.
If you want more difficult - sql server business intelligence studio
If it would be desirable hands, or the edition of the server described above does not allow why ODBC that will not roll? You apparently issue not all conditions of the task.

Answer 3

Restriction on ODBC very hinders. You can’t even imagine, perhaps, how much , because in Access, and, if I’m not mistaken, in MS SQL itself, you can select directly through SQL into an external table:

SELECT * INTO [ODBC;DSN=myODBC].my_table FROM my_table

Transferring the table entirely in one request.
Although, it is possible, probably, without ODBC, through OLE DB:

SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0', 
   'c:\northwind.mdb';'admin'; 'password', 
   'SELECT CustomerID, CompanyName
         FROM Customers
         WHERE Region = ''WA'' ')

© MSDN: Using Pass-Through Queries as Tables
See Distributed Queries and keywords starting with OPEN ( OPENDATASOURCE , OPENQUERY , OPENROWSET ).