ADO/ADO.NET
 

ADO/ADO.NET - how to export the table of sqlserver to ms access database  ADO/ADO.NET - how to export the table of sqlserver to ms access database

Asked By Bhanuprakash T.R
28-Jul-11 08:20 AM
Hello sir,
i have one table as purchaseOrder in sql server i am using datagrid view the table
sqlconection con=new sqlconnection(" ");
SqlCeCommand com = new SqlCeCommand("select * from purchaseOrder", con);
        SqlCeDataAdapter da = new SqlCeDataAdapter();
        da.Fill(ds);
        DataTable dt = ds.Tables[1];
        dataGridView1.DataSource = ds.Tables[1];
 i want same table same data into my access database
i create one database as pra.mdb i want move purchaseOrder table of my sql sever to purchaseOrder table of ms access please say any solutions
  Ravi S replied to Bhanuprakash T.R
28-Jul-11 08:22 AM
HI

I have taken two con. object for connecting with 2 sdf database .

and also i have taken two dataset ds1 and ds2, which will hold value from different database.

here I have  read data from ds2 and then append to ds1.

finally using update() method of dataadapter, i have inserted data into database. 

try this code-

string stConnection1 = "constring1";
string stConnection2 = "constring2";
      
         OleDBConnection cn1 = new OleDBConnection (stConnection1 );  //access
         OleDBCommand cmd1= new OleDBCommand ("select * from employees",cn1);
         OleDBDataAdapter da1= new OleDBDataAdapter (cmd1);
         OleCommandBuilder cb = new OleCommandBuilder(da1);


   SqlConnection cn2 = new SqlConnection(stConnection2 ); //sql server
   SqlCommand cmd2= new SqlCommand("select * from employees",cn2);
   SqlCeDataAdapter da2= new SqlCeDataAdapter (cmd2);





         DataSet ds1= new DataSet();
         DataSet ds2 = new DataSet();
         da1.Fill(ds1,"Employees");
   da2.Fill(ds2,"Employees");


  




int limit = ds2.Tables["Employees"].Rows.Count;

for (int i = 0; i < limit; i++)
{
DataRow NewR = ds1.Tables["Employees"].NewRow();
NewR.ItemArray = ds2.Tables["Employees"].Rows[i].ItemArray;
ds1.Tables["Employees"].Rows.Add(NewR);
}
da1.Update(ds, "Employees");
 
 
 
 
 

try this code and let me know.
  James H replied to Bhanuprakash T.R
28-Jul-11 08:23 AM

What you want to do is have a couple of scripts that you run against the master database from C# using the regular connection objects (SqlConnection, etc).

There are commands to backup and commands to restore to and from a file. All you then need to do is manage the file in your application.

To restore:

http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/

To backup:

http://msdn.microsoft.com/en-us/library/ms191304.aspx

There is also a set of DLLs from Microsoft to work against SQL Server databases that are not part of the ADO.NET provider set. Here is a tutorial covering these:

http://www.codeproject.com/KB/database/SQL_Server_2005_Database.aspx

Quickest way would be to connect to the server and execute the SQL Commands to backup and restore SQL Server Databases directly. Google the following to get the syntax; For Backup Syntax; tsql backup database msdn For Restore Syntax; tsql restore database msdn

There are alternatives to the above though;

  1. Use SMO (SQL Management Objects), a .Net library used to help manage SQL from .Net Applications. Here's a link with many links to some reference material that will be helpful;http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/5638666e-cd2e-467d-bd03-6d20e2cbbe1b/

  2. Use SQL itself. Did you know SQL Maintenance Plans can perform Backups and Restores on more than the local server and can be scheduled to run at any time. You 'may' need to implement a job to copy the backup files over to the alternate server as well. At worst a combination of plans on the two servers can be used.

My preference would be to try the maintenance plans first Consider the fact that a DBA will see it know about it and be able to change it to adapt to the business environment. Also you can hang off error messaging that is emailed/logged when it fails and it's all built in functionality.

If you really want to or have to write an application, consider the SQL command option as SMO is limited to specific versions of SQL Server. Older versions use DMO. No idea what the next version will be =)

  Reena Jain replied to Bhanuprakash T.R
28-Jul-11 08:31 AM
There are a few options here. You could use the  http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx class. You can also use SQL:
Code Snippet

Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test files\db1 XP.mdb")
 
AccessConnection.Open()
 
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Table_3] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes] FROM [Table_3]", AccessConnection)
 
AccessCommand.ExecuteNonQuery()
AccessConnection.Close()

 for exporting and importing Data from SQL to Access in Mdb File
http://www.codeproject.com/KB/asp/SQL_to_Access_Data_Export.aspx
http://support.microsoft.com/kb/200427
  Vickey F replied to Bhanuprakash T.R
28-Jul-11 08:39 AM
The easiest way to convert an Access database to SQL Server is to use the Upsizing Wizard. The Upsizing Wizard:
  • Preserves database structure, including data, indexes, and default settings.
  • Automatically converts Access validation rules and default settings to the appropriate SQL Server equivalents.
  • Maintains table relationships and referential integrity after you upsize.
To run the Upsizing Wizard in Access 2000, on the Tools menu, point to Database Utilities, and then click Upsizing Wizard.

To use the Upsizing Wizard in Access 97, you must first download the Microsoft Access Upsizing Tools 97 from the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?familyid=f5c7a907-7c5f-46cb-9157-a1ec4e96083d&displaylang=en

To run the Upsizing Wizard from Access 97, on the Tools menu, point to Add-ins, and then click Upsize to SQL Server.

Follow this link-

http://support.microsoft.com/kb/237980

Hope this will help you.
  Vickey F replied to Bhanuprakash T.R
28-Jul-11 08:43 AM
Sorry for previous post that was opposite .

Follow these steps-

This is possible with the enterprise manager .

1. right click on the database to export,
2. choose all tasks/export data
3. and then the right datasource (Microsoft Access).
4. then you have to point to the database and that basically does it. But there are a few peculiarities.

For example a bit column is not converted well, (makes 0 and -1 entries in a Yes/No column) which means the opposite of what it should. .

Follow this link also-
http://quomon.com/question-how-to-export-a-database-from-ms-sql-server-to-access-1030.aspx
Hope this will help you.
  dipa ahuja replied to Bhanuprakash T.R
28-Jul-11 08:44 AM
Try this 
void exportToAccess()
{
  //load data from SQL
  SqlDataAdapter da = new SqlDataAdapter("select * from table1", "Sqlconnectionstring");
  DataTable dt = new DataTable();
  da.Fill(dt); //load into data
 
  //copy to Access table
 
  OleDbConnection conn = new OleDbConnection("Accsessconnectionstring");
  OleDbCommand comm;
  for (int i = 0; i < dt.Rows.Count; i++)
  {
    string q = "inser into table1 (id,item) values ('" + dt.Rows[i]["Id"].ToString() + "','" +
    dt.Rows[i]["item"].ToString() + "')";
 
  conn.Open();
  comm = new OleDbCommand(q, conn);
  comm.ExecuteNonQuery();
  conn.Close();
  }
}
  Ravi S replied to Bhanuprakash T.R
30-Jul-11 08:45 AM
HI

There is basically two functions involved in this process,

  1. CreateBackup(byval db)
  2. GetDataTypeEnum(byval val,byval siz)

CreateBackup(byval db)

This function Accepts parameter db as the path of mdb file which has to be created as backup file. Here in order to create backup first we get all the User tables in Database then store these table names in array,

'Getting the name of all UserTables from SQL Server 
sql ="SELECT name FROM sysobjects WHERE xtype='u' and status >0"  
rs.Open sql, Cnn 'conMdbection to sql server
 if not rs.EOF then Redim arTables(0) 
   While not rs.eof 
     arTables(UBound(arTables)) = rs("name") 
    ReDim Preserve arTables(UBound(arTables) + 1) 
     rs.movenext 
   Wend 
  ReDim Preserve arTables(UBound(arTables) - 1) 
end if

and after that for each element(Table) in array create a table in mdb file then get structure of table in SQL Server then start Creating tables in mdb file for those tables only which have at least one row. then Copying of data from SQL server to Access table is done using two recordsets for Access and SQL respectively.

For iCount=0 To UBound(arTables) 'Creation of table in mdb file starts here 
 if rs.state=1 then rs.close 
  sql ="SELECT * FROM " & arTables(iCount) 
    rs.Open sql, Cnn 
      If Not rs.EOF Then 
        tableName = arTables(iCount) 
          tableCreate = "CREATE TABLE " & tableName & "(" 
             For i=0 to rs.Fields.Count-2 
               tableCreate =tableCreate & rs.Fields.Item(i).Name &" " &_
                GetDataTypeEnum(cint(rs.Fields.Item(i).Type), rs.Fields.Item(i).DefinedSize) &", " 
             Next 
               tableCreate =tableCreate & rs.Fields.Item(rs.Fields.Count-1).Name &" "&_
                GetDataTypeEnum(cint(rs.Fields.Item(rs.Fields.Count-1).Type),rs.Fields.Item(i).DefinedSize) &")"  
             conMdb.execute tableCreate, , 129 'Table Creation in Mdb 
    
      sqlMdb ="SELECT * FROM " & arTables(iCount) 'SQL to new Table in Mdb 
      rsMdb.Open sqlMdb, conMdb,2,3 
      while Not rs.EOF 
    rsMdb.addnew 'Adding Records to Table 
    For i=0 to rs.Fields.Count-1 
      if Not isnull(rs(i)) Then rsMdb(i)=rs(i) 'Copying Data From SQL table to Access Table 
    Next 
    rs.movenext 
    rsMdb.update 
   Wend 
    rsMdb.close 
 End If 
Next

In above function while creating DDL(Data Defintion Language) Script to generate Access Table there was a problem that access the datatypes for sql and Access are different so to parse SQL datatypes into access i created FunctionGetDataTypeEnum(byval val,byval siz) which accepts two parameters one is the type of SQL field and other is its size,

GetDataTypeEnum(byval val, byval siz)

Case 3 GetDataTypeEnum= "INT"'"adInteger" 
Case 7 GetDataTypeEnum= "DATETIME"'"adDate" 
Case 11 GetDataTypeEnum= "YESNO"'"adBoolean"
Case 133 GetDataTypeEnum= "DATETIME"'"adDBDate"
Case 200 '"adVarChar" 
   if siz<255 then GetDataTypeEnum= "VARCHAR("& siz &")" else GetDataTypeEnum= "MEMO" 
Case 201 GetDataTypeEnum= "MEMO DEFAULT ''"'"adLongVarChar"
Create New Account
help
s generic usage pattern: 1. Get initial data from database to dataset: dataadapter.Fill(dataset, "Table") ' Data goes from database to empty DataSet - done one time on startup. 2. Change data dataset as needed. 3. Submit data back to database from changed dataset: dataadapter.Update(dataset, "Table") ' Changed / added data goes from DataSet to database, data marked current after that. Repeat steps 3. RowUpdating 4. RowDeleting Try this code sample . aspx code- <asp:GridView ID = "GridView2" runat = "server" AutoGenerateColumns = "false" DataKeyNames = "empid" onrowcancelingedit = "GridView2_RowCancelingEdit" onrowdeleting = "GridView2_RowDeleting" onrowediting = "GridView2_RowEditing" onrowupdating = "GridView2_RowUpdating" onselectedindexchanged = "Gridiew2_SelectedIndexChanged" onrowcommand = "GridView2_RowCommand" > <Columns> <asp:TemplateField HeaderText = "Select"> <ItemTemplate> <asp:CheckBox ID = "CheckBox1" runat = "server" / > < / ItemTemplate> < / asp:TemplateField> <asp:BoundField DataField = "empid" HeaderText = "empid" / > <asp:BoundField DataField = "empname" HeaderText = "empname asp:TemplateField HeaderText = "dept"> <ItemTemplate> <%# Eval("dep") %> < / ItemTemplate> <EditItemTemplate> <asp:DropDownList runat = "server" DataSourceID = "SqlDataSource1" ID = "DropDeptId" DataTextField = "Deptid" DataValueField = "DeptId" / > < / EditItemTemplate> < / asp:TemplateField> <asp:CommandField ShowEditButton = "true" ShowDeleteButton = "true" / > < / Columns> < / asp:GridView> .cs code- / / Function to bind Grid private void getdata() { SqlConnection con = new SqlConnection("CONNECTION STRING"); SqlDataAdapter da; string mySQL = "SELECT empid, empname, dept FROM emp "; da = new SqlDataAdapter RowIndex].Cells[1].Text; / / FOR GETTING ENPID string deleteQuery = "delete from emp where empid = ' " + strEmpId + " ' "; SqlConnection cn = new SqlConnection("CONNECTION STRING"); cn.Open(); SqlCommand cmd = new SqlCommand(deleteQuery, cn); cmd.ExecuteNonQuery
how to export table of sqlserver to ms access Hello Sir, i view the table using datagrid in sql connection. . . that table name purchaseOrder. . . i want move that table into ms access please send the coding for that thanks Bhanuprakash There are code snaps which export sql server table data into access http: / / www.codeproject.com / KB / asp / SQL_to_Access_Data_Export.aspx Try this: void export
hi, you can use a third party commercial application DbNetCopy that will copy data between SQL Server Compact and most other major database vendors. Limited to 1000 records per table for the non-licensed version. Hope this will help you HI here is the link downloads / convert / sdf / to / mdb / Step 1 Make a new Access database. Go to the tables tab and right click in the large whitespace area. Select Import. You'll need an odbc connection to your SQL database. You can import all of your tables at once. Step 2 For your views, I would try copying and pasting the views SQL into queries. I don't think it will work to import them as queries, and there will probably be some differences in the SQL between Access and MS SQL. Thanks I have taken two con. object for connecting with
using c# codings Hello Sir, i am read the .sdf file in grid view using sql ce connection . . . . . . the .sdf contains 3 tables . . . . i want write coding for 3 tables move access database please send me possible links and please send the coding for that codeproject.com / KB / database / StartSqlServerEW.aspx?df = 100&forumid = 321708&exp = 0&select = 1584032 HI SQL Server everywhere was the intiall name of SQL Server Compact Edition. If you installed correctly sql server compact edition 3.1 in your machine you would'n have problems insteed. About