locked
Create a Linked Table in Access 2007 and link it to a table from Excel 2007 RRS feed

  • Question

  • (Sorry for posting here since this is mostly an SQL question, but I found no section for Microsoft Access or just regular SQL.
    I didn't want to post this in the SQL Server section since it deals with Access.)

    To be honest I have no idea where to really start. I have searched Google for how to create a linked table using SQL and all I found was how to make a normal table

    What I have is an Excel file sitting in a folder named FTP. Every night at midnight an ftp program will go out to a server, grab a new Excel file with the exact same name and replace the old one. I use a Linked Table in Access because it’s just easier to read using C#.

    There are many excel files that are pulled and stored in the FTP folder and many new ones will eventually be added.

    I need a way in C# to make a new linked tale within my access file and import a new excel table into it, that way my program just reads from the linked table everyday and gets the updated info.

    So, just to recap: I already know how to read from and write to an Access table and how to execute SQL commands using C#; I just need to know how to create a linked table using SQL statements in C# and then how to import an Excel table into that Access linked table.

    • Edited by Snow_Wolf Friday, October 9, 2009 5:59 PM I got rid of some odd block of code that must have come from MS Word when I copied and pasted from it.
    • Changed type Harry Zhu Thursday, October 15, 2009 2:51 AM
    • Changed type Snow_Wolf Friday, October 16, 2009 9:35 PM Because it is a simple question....
    Thursday, October 8, 2009 8:50 PM

Answers

  • It is not what I wanted but it works well enough. Please keep in mind that I keep the processing part of this code in a class called classImportFromExcelToAccess.cs and that I will be showing you the four strings you need to write in order to use this class first. Also, keep in mind that "sheetName" has to be the name of the sheet in the Excel book that you wish to import into a new Access table of the same name or whatever name you might want to change it to.

    string excelInfo = @"C:\Excel.xls";
    string accessInfo = @"C:\Database.accdb";
    string sheetName = "Sheet1";
    string passInfo = classImportFromExcelToAccess.ImportToAccess(excelInfo, accessInfo, sheetName);

    classImportFromExcelToAccess.cs

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.IO;
    using System.Data.OleDb;
    
    namespace whatEver_NameSpace
    {
        class classImportFromExcelToAccess
        {
            public static string ImportToAccess(string ExcelPathAndFileName, string AccessPathAndFileName, string sheetName)
            {
                Microsoft.Office.Interop.Access.Application _accessData;
    
                _accessData = new Microsoft.Office.Interop.Access.ApplicationClass();
    
    
                    //File.Delete(AccessPathAndFileName);
                    _accessData.OpenCurrentDatabase(AccessPathAndFileName, false, "");
    
                    _accessData.CloseCurrentDatabase();
    
                    _accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);
                    _accessData = null;
    
                    string _excelConnString;
    
                    _excelConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + ExcelPathAndFileName + ";" +
    
                    "Extended Properties=Excel 8.0;";
                    
                    OleDbConnection _connection = new OleDbConnection(_excelConnString);
                    
                    //con2 is for connecting to the access db and deleting the table
                    //before I run the writeTable command because it will not overwrite
                    //a table that already exists.
                    OleDbConnection con2 = new OleDbConnection();
                    con2.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + AccessPathAndFileName;
    
                    OleDbCommand writeTable = new OleDbCommand();
                    OleDbCommand deleteTable = new OleDbCommand();
    
                    writeTable.Connection = _connection;
                    deleteTable.Connection = con2;
    
                    try
                    {
    
                        writeTable.CommandText = @"SELECT * INTO [MS Access;Database=" + AccessPathAndFileName + "].[" + sheetName + "] FROM [" + sheetName + "$]";
                        deleteTable.CommandText = "DROP TABLE " + sheetName;
    
                        con2.Open();
                            deleteTable.ExecuteNonQuery();
                        con2.Close();
    
                        _connection.Open();
    
                            writeTable.ExecuteNonQuery();
    
                         _connection.Close();
                      
                    }
    
                   catch (Exception)
                    {
                      
                    }
                    
    
                return null;
            }
        }
    }
    
    • Marked as answer by Snow_Wolf Saturday, October 17, 2009 2:49 AM
    Saturday, October 17, 2009 2:40 AM

All replies

  • Hi,

    Could you please be more clear about "Linked Table in Access 2007 and link it to a table "?

    A table in excel ?

    I think you can read data from excel and put them into access .

    Harry
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, October 12, 2009 5:13 AM
  • We are changing the issue type to “General Discussion” because you have not followed up with the necessary information. If you have more time to look at the issue and provide more information, please feel free to change the issue type back to “Question” by opening the Options list at the top of the post  window, and changing the type. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 15, 2009 2:51 AM
  • Well a "Linked Table" is a special kind of table in an Access database. The simplest way to explain it is the following:

    Lets say I have an excel spreadsheet named sheet_A, well I can open up MS Access, import data from the excel book and place it in to a new "linked table" named table_B. In a linked table whenever the source data, in this case the excel spread sheet, is edited Access automatically updates the "Linked Table" with the new data.

    Now I am sort of giving up on this option as I have spent days searching Google and found nothing on linked tables specifically. What I have found recently is of great interest to me but I do have an issue.

    Please, see this link/tutorial for the code as it will keep this post a little more neat: http://www.dotnetspider.com/forum/187304-Using-C-how-import-excel-file-into-ms-access-database-dynamic-table.aspx

    This method will only add new data from the excel sheet to the database if we delete the database first and create a new one.

    How would I use this code to open an existing database and overwrite existing tables with new data from the excel files?

    I tried

    _accessData.OpenCurrentDatabase(@"C:\ Book.mdb", true, "");

    but it gave no errors and seemed to do nothing.
    • Edited by Snow_Wolf Friday, October 16, 2009 9:49 PM spelling error
    Friday, October 16, 2009 9:48 PM
  • It is not what I wanted but it works well enough. Please keep in mind that I keep the processing part of this code in a class called classImportFromExcelToAccess.cs and that I will be showing you the four strings you need to write in order to use this class first. Also, keep in mind that "sheetName" has to be the name of the sheet in the Excel book that you wish to import into a new Access table of the same name or whatever name you might want to change it to.

    string excelInfo = @"C:\Excel.xls";
    string accessInfo = @"C:\Database.accdb";
    string sheetName = "Sheet1";
    string passInfo = classImportFromExcelToAccess.ImportToAccess(excelInfo, accessInfo, sheetName);

    classImportFromExcelToAccess.cs

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.IO;
    using System.Data.OleDb;
    
    namespace whatEver_NameSpace
    {
        class classImportFromExcelToAccess
        {
            public static string ImportToAccess(string ExcelPathAndFileName, string AccessPathAndFileName, string sheetName)
            {
                Microsoft.Office.Interop.Access.Application _accessData;
    
                _accessData = new Microsoft.Office.Interop.Access.ApplicationClass();
    
    
                    //File.Delete(AccessPathAndFileName);
                    _accessData.OpenCurrentDatabase(AccessPathAndFileName, false, "");
    
                    _accessData.CloseCurrentDatabase();
    
                    _accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);
                    _accessData = null;
    
                    string _excelConnString;
    
                    _excelConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + ExcelPathAndFileName + ";" +
    
                    "Extended Properties=Excel 8.0;";
                    
                    OleDbConnection _connection = new OleDbConnection(_excelConnString);
                    
                    //con2 is for connecting to the access db and deleting the table
                    //before I run the writeTable command because it will not overwrite
                    //a table that already exists.
                    OleDbConnection con2 = new OleDbConnection();
                    con2.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + AccessPathAndFileName;
    
                    OleDbCommand writeTable = new OleDbCommand();
                    OleDbCommand deleteTable = new OleDbCommand();
    
                    writeTable.Connection = _connection;
                    deleteTable.Connection = con2;
    
                    try
                    {
    
                        writeTable.CommandText = @"SELECT * INTO [MS Access;Database=" + AccessPathAndFileName + "].[" + sheetName + "] FROM [" + sheetName + "$]";
                        deleteTable.CommandText = "DROP TABLE " + sheetName;
    
                        con2.Open();
                            deleteTable.ExecuteNonQuery();
                        con2.Close();
    
                        _connection.Open();
    
                            writeTable.ExecuteNonQuery();
    
                         _connection.Close();
                      
                    }
    
                   catch (Exception)
                    {
                      
                    }
                    
    
                return null;
            }
        }
    }
    
    • Marked as answer by Snow_Wolf Saturday, October 17, 2009 2:49 AM
    Saturday, October 17, 2009 2:40 AM
  • Hello,

    Is there a way to create dynamic access database, tables and it's columns in c# and export or insert the data in it from excel file in c#?

    How can I create the Access database and tables at run time using c#?

    Thanks.

    Wednesday, November 17, 2010 8:11 AM