CODEWITHSUNDEEP
.netdatabasems-access
Shane LeBlanc
Shane LeBlanc

Reputation: 2643

How can I link an external Access table via .NET?

I have two files located in the App_Data folder in my solution. Both files are .accdb files and one is a file of tables, the core. I have another file that contains the stored queries and some VBA elements that I won't be needing anymore, but I will need the stored queries. This solution is located on a network drive and the file that holds the queries and the file that holds the tables are linked.

When I create an installation project and install the application, I only need the file that contains the queries. The problem is that this file links back to the table file's original location. I need it to request the location of the file that contains the table as this will be installed on another machine where the .accdb file that contains the table could be anywhere. Is there a way to have an OpenFileDialog come up to ask them to point to its location?

I currently have an N-Tier application containing a DAL that gets the connection string stored in My.Settings. The string is "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\App_Data\FPC_Reporting.accdb" which is the file that is included with the install that contains the stored queries. That file though, remains to think that the file containing the tables is still pointing to my network location but as stated, it could be anywhere so I would like to have it ask the user; after installation as to where their local file, that contains the tables, is located.

The error received after installation of the application and also after disconnecting the network drive is "N:\PROJECTS\FPC Reporting Tool\FPCReportBuilder\FPCReportBuilder\App_Data\FPC_Reporting_DATA.accdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides." The filename shown in the error is the file that contains the tables from which are supposed to be linked to the file containing the stored queries shown in the connection string.

Upvotes: 1

Views: 1882

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123839

The following C# code has been tested and confirmed as working in Visual Studio 2010:

private void button1_Click(object sender, EventArgs e)
{
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        string RemoteDatabasePath = openFileDialog1.FileName;

        // the following code requires that the project have a COM reference to:
        // "Microsoft Office 14.0 Access Database Engine Object Library"

        // create the DBEngine object
        var dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();

        // open the local database file containing the linked table
        Microsoft.Office.Interop.Access.Dao.Database db = dbe.OpenDatabase(@"C:\__tmp\testData.accdb");

        // create a TableDef object for the linked table we want to update
        Microsoft.Office.Interop.Access.Dao.TableDef tbd = db.TableDefs["Products"];

        // update the .Connect property with the full path to the remote database
        tbd.Connect = ";DATABASE=" + RemoteDatabasePath;

        // refresh the table link
        tbd.RefreshLink();

        // test the new connection
        Microsoft.Office.Interop.Access.Dao.Recordset rs = db.OpenRecordset("SELECT * FROM Products", Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenSnapshot);
        MessageBox.Show(rs.Fields["ProductName"].Value);
        rs.Close();
    }
    this.Close();
}

Edit re: comment

Checking the version(s) of the Access Database Engine ("ACE") that are installed on a given machine:

Search for the file ACEOLEDB.DLL.

  1. If it is found in C:\Program Files\Common Files\Microsoft Shared\OFFICE14 then ACE is installed and its version matches the "bit-ness" of the OS: 32-bit ACE on 32-bit Windows, and 64-bit ACE on 64-bit Windows.

  2. If it is found in C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14 then 32-bit ACE is installed on 64-bit Windows.

Any application using ACE will need to have the correct version installed: 32-bit applications will require the 32-bit version of ACE (even on 64-bit Windows), and 64-bit applications will require the 64-bit version of ACE. .NET applications targeted to "any platform" will need the version of ACE that matches the "bit-ness" of the host OS.

Upvotes: 3

Vivek Jain
Vivek Jain

Reputation: 3889

You may try an approach to decouple your Access DB from the solution. Remove it from the App_Data folder.

Create a DSN Data Source (ODBC) for Microsoft Access Driver on the system and point to the Access DB file over the network. Even better, if you could map the network drive on your system to automatically map when system is running / user is logged-in.

In your code use the connection-string with the above created DSN.

Check this out.

Edit: If all you want is an OpenFileDialog then you may try this:

private void Button1_Click(object sender, EventArgs e) 
{ 
    OpenFileDialog openFileDialog1 = new OpenFileDialog(); 
    openFileDialog1.InitialDirectory = "C:\\"; 

    openFileDialog1.Title = "Select Database"; 
    openFileDialog1.CheckFileExists = true; 
    openFileDialog1.CheckPathExists = true; 
    openFileDialog1.DefaultExt = "accdb"; 
    openFileDialog1.Filter = "Access DB files (*.accdb;*.mdb)|*.accdb;*.mdb"; 
    openFileDialog1.FilterIndex = 2; 
    openFileDialog1.RestoreDirectory = true; 
    openFileDialog1.ReadOnlyChecked = true; 
    openFileDialog1.ShowReadOnly = true; 
    if (openFileDialog1.ShowDialog() == DialogResult.OK) 
    { 
        // This will give you the selected file
        string file = openFileDialog1.FileName;
        string connectionString = "Some connection stuff; DATABASE=" + file;
        // Connect to the Access DB
    } 
}

Please use this code as a starting point not a copy-paste solution.

Upvotes: 0

Related Questions