Clever Geek Handbook

Paste into Access from SQL Server

I want to copy several thousand records from SQL Server to Access in C #. Another direction works with help SqlBulkCopy

. Is there something like that to do it backwards?

I try to stay away from every field in every record and create a disgusting statement Insert

that will not only run forever, but will most likely be terrible if anything changes.

+3


source to share


1 answer


This will work with MS Access OleConnection:

SELECT fld1, fld2 INTO accessTable FROM [sql connection string].sqltable

For example:

SELECT * INTO newtable 
FROM 
[ODBC;Description=Test;DRIVER=SQL Server;SERVER=server\SQLEXPRESS;UID=uid;Trusted_Connection=Yes;DATABASE=Test].table_1

Or add

INSERT INTO newtable
SELECT *
FROM [ODBC;Description=Test;DRIVER=SQL Server;SERVER=server\SQLEXPRESS;UID=uid;Trusted_Connection=Yes;DATABASE=Test].table_1;


Or with FileDSN

INSERT INTO newtable
SELECT * 
FROM [ODBC;FileDSN=z:\docs\test.dsn].table_1;

You will need to find a suitable driver like

ODBC;Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword;

From http://connectionstrings.com works for me, but check your client version.

+5


source






More articles:


All Articles