How to Export SQL Tables To Access MDB

Question

1.44/5 (2 votes)

See more:

Hi Friends ,

I am New To Windows Application, i wanna Export Sql Database Tables to Access MDB File using C sharp Coding in Windows Application , so can any help me for the Same . Thanks in Advance.

Regards,
Saravana Kumar.M

Posted 24-Oct-12 20:39pm

SARAVANAKUMAR.M

Add a Solution

Comments

[no name] 25-Oct-12 2:41am

try to use import data feature of access.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Think On · Answer 1 · 2012-10-25T03:00:00

HTML

Hi

Open sql Server Management Studio and follow the following steps

   - Right Click on Database GoTo Task -> Export Data

   - In the choose a source scree
       - Select source database and provide the credentials 
       - Press Next

   - In the choose a destination screen
       - Select Destination as Microsoft Access
       - Provide a file Path. User id and password if you want to configure

   - Select option as copy data from one or more table or view and say next

   - you will see all the tables in database select the respective tables 
     which you want to export and finish
   
It will export the respective tables in the mdb file.

Kuthuparakkal · Answer 2 · 2012-10-25T03:24:00

Solution 2

Try the following snippette:

C#

//Using JET.OLEDB :
System.Data.OleDb.OleDbConnection AccessConn = new 
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Test Files\\db1.mdb");

//Using ACE.OLEDB :
//System.Data.OleDb.OleDbConnection AccessConn = new
//System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test Files\\db1.mdb")

AccessConn.Open();

//New table, using SELECT INTO
System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO Orders FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn);

//Existing table, using INSERT INTO 
//Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ORDERS] SELECT * FROM [Orders] IN '' //[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)

AccessCommand.ExecuteNonQuery();
AccessConn.Close();

Posted 25-Oct-12 3:24am

Kuthuparakkal

Comments

[no name] 26-Oct-12 1:52am

You are exporting only data from order table, not the entire table (schema + data) to mdb .

Kuthuparakkal 26-Oct-12 7:19am

Read it fully before vomiting sheett

[no name] 26-Oct-12 7:30am

Execute this 'sheett' with a blank MDB and then tell how much this 'sheett' work.

Kuthuparakkal 26-Oct-12 8:09am

You should understand that Select INTO would create new table. Learn to read and digest and use your brain

hayta6891 4-Jun-14 5:22am

It works fine for me to export mssql to mdb.
I just changed :
SELECT * INTO {New Table Name} FROM {Old Table Name} IN '' [ODBC; Driver={SQL Server}; Server=****; Database=****; UID=****; PWD=****];

Kumar Tanmay 29-Jun-16 9:37am

thanks hayta6891.. it worked for me....I had to replace the curly brackets enclosing new and old table name by square brackets..

Member 9877910 8-Jul-15 2:58am

I have tried a lot , I am unable to Fetch data every time getting error as : ODBC--connection to 'SQL Server(local)' failed.

Kuthuparakkal 8-Jul-15 13:53pm

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TestAccessDB
{
class Program
{
static void Main(string[] args)
{
Run();
}

private static void Run()
{
//Using JET.OLEDB :
System.Data.OleDb.OleDbConnection AccessConn = new
System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Matrix\Info.accdb");

//Using ACE.OLEDB :
//System.Data.OleDb.OleDbConnection AccessConn = new
//System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test Files\\db1.mdb")

AccessConn.Open();

//New table, using SELECT INTO
System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO [Accounts] FROM [Accounts] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Test;Trusted_Connection=yes];", AccessConn);

//Existing table, using INSERT INTO
//Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ORDERS] SELECT * FROM [Orders] IN '' //[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)

AccessCommand.ExecuteNonQuery();
AccessConn.Close();
}
}
}

Member 3015492 12-Dec-16 8:04am

where condition is not working kindly.... kindky suggest me the way to use where productid in( 55,555,777)

System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO [Accounts] FROM [Accounts] where productid in(11,33,333) IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Test;Trusted_Connection=yes];", AccessConn);