I'm looking to copy a few thousand records from SQL Server into Access in C#. The other direction works using SqlBulkCopy. Is there anything in place to do this in reverse?
I'm trying my best to stay away from looping through each field in each record and building a heinous Insert statement that not only would take forever to run, but would likely crash horribly if anything changes.
This will run against the MS Access OleConnection connection:
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 to append
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 the right driver to suit, for example
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 out your client version.
SELECT * FROM [ODBC;Description=Test;DRIVER=SQL Server;SERVER=IpAddress;UID=Username;PASSWORD=Pass;DATABASE=DB].dbo.Orders -- Am I entering the credentials wrong? They don't seem to work like this. Thank you kindly!
ODBC;Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword; from connectionstrings.com works for me, check out your client version.ODBC;Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword;, works to connect - so thank you! Now, for some reason, when I do a SELECT to test it, it returns all #DELETED rows in Access.. Is this normal?SELECT * FROM [ODBC;FileDSN=z:\docs\test.dsn].table_1 I get records.