CODEWITHSUNDEEP
c#sql-server-2008ms-access
vijay shiyani
vijay shiyani

Reputation: 766

How to transfer data from Microsoft Access to SQL server 2008?

In my current project i have to transfer data EVERY DAY from MS Access to Sql Server 2008 standard edition. of course i can use SQL Migration tool to transform Access database into Sql Server database, but problem is i have to do this again and again every day to keep updating my sql server database.

SO is there any way i can write C# program that transfer data from MS ACCESS file to SQL SERVER?

Any help would be highly appreciated.

Upvotes: 3

Views: 2553

Answers (4)

Tony Toews
Tony Toews

Reputation: 7882

You could also run update/delete/append queries inside C#. With varous IN clauses pointing to the databses in question. How exactly you'd do this in C# I don't know but these would be easy to do in Access.

[SELECT | INSERT] INTO destination IN
    {path | ["path" "type"] | ["" [type; DATABASE = path]]}

To identify a source table:

FROM tableexpression IN 
    {path | ["path" "type"] | ["" [type; DATABASE = path]]}

from Access 97 help.

Upvotes: 1

nvogel
nvogel

Reputation: 25526

Why not just migrate the data permanently to SQL Server and use your Access application as the front end to a SQL Server database instead of a Jet/ACE database?

Upvotes: 1

Jagmag
Jagmag

Reputation: 10356

As suggested by Serkan in his comment, I would go with using SSIS (SQL Server Integration Services) for this task especially as you are already using SQL Server and SSIS is build exactly to support this kind of task.

SSIS is very flexible (like most ETL packages) and you can add custom logic for transformation of data (if any)

Additionally, if you create a job in SQL server, then you can schedule this task and this job will execute the SSIS package that connects to the MS Access database and use it as a data source automatically.

Moreover, you do have the option to call an SSIS package directly from C# code (if for some reason you have a requirement to not use scheduled jobs but need to provide some kind of interface for users to kick off the task) using DTExec.exe or by using the classes exposed via Microsoft.SQLServer.ManagedDTS.dll.

Refer this link for how you can invoke an SSIS package directly from C# code

Upvotes: 4

user180100
user180100

Reputation:

I would suggest an ETL (see Talend Open Studio for example). You design a transfer job (with appropriate transform if necessary) and then use your OS task scheduler to run the job every day.

Note: Talend Open Studio generates java or perl code.

Upvotes: 1

Related Questions