Click here to Skip to main content
15,881,559 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
How to read data from a MS Access file and store it in SQL Server database
Posted

Read the data from Access into a DataTable:
C#
string strConnect = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=D:\Temp\MyDB.accdb";

DataTable dt = new DataTable();
using (OleDbConnection con = new OleDbConnection(strConnect))
    {
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM MyTable", con);
    con.Open();
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    da.Fill(dt);
    }

Then use SqlBulkCopy to update SQL:
C#
string strConnect = @"Data Source=GRIFFPC\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=True";
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlBulkCopy bulk = new SqlBulkCopy(con))
        {
        bulk.DestinationTableName = "Test";
        bulk.WriteToServer(dt);
        }
    }
 
Share this answer
 
Comments
ArunAmalraj 4-Apr-14 8:25am    
Hi,

Thanks a lot for your help so far.
Clarification: Should the columns in the two tables (in Access & SQL) should match ??
OriginalGriff 4-Apr-14 8:27am    
Probably be a good idea, don't you think? :laugh:
ArunAmalraj 4-Apr-14 8:51am    
Yes. :)

I get this err while the columns do not match

Err: The given ColumnMapping does not match up with any column in the source or destination.
ArunAmalraj 4-Apr-14 8:51am    
Is there any way to overcome that
OriginalGriff 4-Apr-14 9:50am    
None that I can give you - I have no idea what your columns are in either table, much less what you should put where! ;)
 
Share this answer
 
Comments
ArunAmalraj 4-Apr-14 9:00am    
Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900