3

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
The1nk
  • 702
  • 14
  • 25
  • I should've specified, I'm using a SqlConnection to connect to SQL Server and an OleConnection to connect to Access -- I'm doing a lot of other manipulation in my code. – The1nk Feb 01 '13 at 19:08

1 Answers1

5

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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Looks like this is the right track, but I need some additional help with supplying a password instead of a trusted connection: `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! – The1nk Feb 01 '13 at 19:24
  • 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, check out your client version. – Fionnuala Feb 01 '13 at 19:29
  • You can also use DSN or FileDSN. – Fionnuala Feb 01 '13 at 19:30
  • The string you just posted, `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? – The1nk Feb 01 '13 at 19:34
  • No, it is not. When I run eg `SELECT * FROM [ODBC;FileDSN=z:\docs\test.dsn].table_1` I get records. – Fionnuala Feb 01 '13 at 19:35
  • Strangely enough, if I do a `SELECT * INTO` it works as it should. Not sure why the `SELECT` was returning `#DELETED` rows, but meh.. I'm okay with not knowing for now. Thank you! :) – The1nk Feb 01 '13 at 19:37
  • You might like to see http://dbaspot.com/sqlserver-odbc/246794-sql-2005-linked-table-ms-access-returns-deleted.html. You are welcome :) – Fionnuala Feb 01 '13 at 19:37