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

Question

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.

Gord Thompson · Accepted Answer · 2013-05-28T22:36:42.153

3

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.

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.
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.

edited May 28 '13 at 22:36

answered May 28 '13 at 11:58

Gord Thompson

116,920
32
215
418

Hi @Gord, do you think `new Microsoft.Office.Interop.Access.Dao.DBEngine();` will work with `ACCDB` databases? – Vivek Jain May 28 '13 at 12:30
1

@theghostofc Yes it will, because the DAO reference I used (and cited in the code) is the "Microsoft Office 14.0 Access Database Engine Object Library" (part of the Access Database Engine, a.k.a. "ACE") which will operate on both `.accdb` and `.mdb` files. – Gord Thompson May 28 '13 at 12:36
I've updated the question with more information. I'm not quite sure how this code works. The intellisense for these methods is pretty much non-existant. I'm trying to find documentation but for some reason haven't found it yet. As for db.TableDefs(), do I need to add each table in the RemoteDatabasePath file? – Shane LeBlanc May 28 '13 at 15:05
Well, I figured it out and the code was good. Thanks for your help. – Shane LeBlanc May 28 '13 at 17:05
Well, I thought I did, but unfortunately after trying it on another machine I get the error "Unrecognized database format and then it links to the front-end file. – Shane LeBlanc May 28 '13 at 17:40
@user1066133 RE: DAO docs - `Microsoft.Office.Interop.Access.Dao` exposes the DAO objects and methods from the COM world, so any online references to DAO (especially for Access 2007+) should have direct counterparts in your .NET project. RE: "Unrecognized database format" - Confirm that the [Access Database Engine](http://www.microsoft.com/en-US/download/details.aspx?id=13255) is installed on the client machine and the "bitness" of the installed version matches the "bitness" of your app (32-bit or 64-bit). – Gord Thompson May 28 '13 at 18:36
How do I check the version of the one in my app? Actually the Path in the properties of it is in Program Files(x86) so it's 32 bit and the OS of the client machine I tested it on is 32-bit. – Shane LeBlanc May 28 '13 at 19:56
@user1066133 RE: "How do I check the version..." - I have updated my answer. – Gord Thompson May 28 '13 at 22:30
@GordThompson / @user1066133, do you think using the `InterOp` assemblies is the right choice here? Instead we could have created the `connectionString` at run-time based on user's selection of the file. Thoughts? – Vivek Jain May 29 '13 at 06:13
1

@theghostofc The issue here is that we need a way to update the table links in the local database file so the queries in that file will work. Having the application create a connection string to the back-end database would enable the application to manipulate the *tables* in the back-end database directly, but it would be of no use to the queries in the local database file. – Gord Thompson May 29 '13 at 07:21

Vivek Jain · Answer 2 · 2013-05-28T12:58:53.057

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.

Can you check my updated question and see if my issue is more clear? — Shane LeBlanc, May 28 '13 at 15:38
@user1066133, I have checked your updated question. The error you're receiving is a valid error and you'll have to keep a check of this exception. As the file is over a network, the network needs to be there. If not, we have to handle that situation. Show some message like **Could not connect to the database. Check network connectivity**, etc. Also, I'd suggest that instead of using `InterOp` assemblies, we can just create the `connectionString` at run-time and try to access the database normally. You can create a quick wrapper for this too to make your life easier. — Vivek Jain, May 29 '13 at 06:18

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

2 Answers2

Edit re: comment

Linked