locked
Importing file into Access 2003 RRS feed

  • Question

  • User410642551 posted

    I'd like to know if it is possible to do the following:

    1. Data is stored in SQL Server databases

    2. Create an application that will export data from SQL database to a .txt file

    3. Automatically import the .txt file into the Access database table.

    Thursday, July 31, 2014 4:17 PM

Answers

  • User1918509225 posted

    Hi honeysuckle,

    I suggest that you can first export data from sql dataabse to a cvs file .

    Then import the cvs file into the access database table.

    Here is main code for importing cvs into access database table :

    string strProvier = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employees.mdb";
     OleDbConnection con = new OleDbConnection(strProvier);
     OleDbCommand cmd = new OleDbCommand();
     int effectedRow = 0;
     int lineCounter = 0;
    
     con.Open();
    
     if ((con.State.ToString() == "Open"))
    {
    
     StreamReader stReader = new StreamReader("Employees.csv");
     string[] strRowData = null;
    
     while (stReader.Peek() >= 0)
     {
     lineCounter = lineCounter + 1;
     strRowData = stReader.ReadLine().Split(",");
    
     try {
     cmd.CommandText = "INSERT INTO tbl_employees(emp_first_name,emp_last_name,emp_salary) VALUES ('" + strRowData(0) + "','" + strRowData(1) + "','" + strRowData(2) + "')";
     cmd.Connection = con;
     effectedRow = cmd.ExecuteNonQuery();
    
     if ((effectedRow == -1)) {
     // Messagebox.Show("Line: " + lineCounter + " Error"); 
     }
     else {
     // Messagebox.Show("Line: " + lineCounter + " Executed Successfully");
    
     }
     }
     catch (OleDbException er) {
     Messagebox.Show("Line: " + lineCounter + " Error: " + er.Message);
    
     }
     }
     stReader.Close();
     con.Close();
     }
     else {
     Messagebox.Show("Not Connected To Database");
     }

    Best Regards,

    Kevin Shen.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 1, 2014 3:07 AM

All replies

  • User622904130 posted

    yes, this is possible.  You can create a watch on a folder and when the csv file is created it processes the file to import the data into access.

    Thursday, July 31, 2014 5:21 PM
  • User1918509225 posted

    Hi honeysuckle,

    I suggest that you can first export data from sql dataabse to a cvs file .

    Then import the cvs file into the access database table.

    Here is main code for importing cvs into access database table :

    string strProvier = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employees.mdb";
     OleDbConnection con = new OleDbConnection(strProvier);
     OleDbCommand cmd = new OleDbCommand();
     int effectedRow = 0;
     int lineCounter = 0;
    
     con.Open();
    
     if ((con.State.ToString() == "Open"))
    {
    
     StreamReader stReader = new StreamReader("Employees.csv");
     string[] strRowData = null;
    
     while (stReader.Peek() >= 0)
     {
     lineCounter = lineCounter + 1;
     strRowData = stReader.ReadLine().Split(",");
    
     try {
     cmd.CommandText = "INSERT INTO tbl_employees(emp_first_name,emp_last_name,emp_salary) VALUES ('" + strRowData(0) + "','" + strRowData(1) + "','" + strRowData(2) + "')";
     cmd.Connection = con;
     effectedRow = cmd.ExecuteNonQuery();
    
     if ((effectedRow == -1)) {
     // Messagebox.Show("Line: " + lineCounter + " Error"); 
     }
     else {
     // Messagebox.Show("Line: " + lineCounter + " Executed Successfully");
    
     }
     }
     catch (OleDbException er) {
     Messagebox.Show("Line: " + lineCounter + " Error: " + er.Message);
    
     }
     }
     stReader.Close();
     con.Close();
     }
     else {
     Messagebox.Show("Not Connected To Database");
     }

    Best Regards,

    Kevin Shen.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 1, 2014 3:07 AM
  • User410642551 posted

    Thank you for the suggestions! Smile

    Friday, August 1, 2014 9:07 AM