CODEWITHSUNDEEP
c#sql-serverms-accessdao
Nikhil
Nikhil

Reputation: 379

'Link Table Manager' functionality Programatically using c#

I want to write some custom code to perform the 'Link Table Manager' operation programatically. I have MS-Access database which is currently referencing external data from Excel,MS-Access and SQL server.

Before executing a Macro on my MS-Access database, I want to re-link the external data sources or provide new location of the data-source in case there is change in the location of the data source.

I have a table in MS-Access database which is Linked to an external SQL data source(Test database). Now I want it to be linked to Production Database. I have tried the following code but it is throwing a COM-Exception with error description as 'ODBC--connection to 'EmployeeConnectionForSQL' failed.'

public void performLinkTableMangerOperationForSQL()
        {  
            string CurrentDatabasePath = @"D:\UDTDatabase\InternalDatabase.accdb";          

            Microsoft.Office.Interop.Access.Dao.DBEngine DAO = new Microsoft.Office.Interop.Access.Dao.DBEngine();
            Microsoft.Office.Interop.Access.Dao.Database db;
            Microsoft.Office.Interop.Access.Dao.TableDefs dt;
            db = DAO.OpenDatabase(CurrentDatabasePath);
            dt = db.TableDefs;

            // Refreshing link for Sql server external table with DSN.
            string sqlSource = @"DATABASE=Employee;";
            string DSNName = "EmployeeConnectionForSQL;";           
            string sqlNewConnectionString = @"ODBC;FileDSN=" + DSNName + sqlSource;

            foreach (Microsoft.Office.Interop.Access.Dao.TableDef table in dt)
            {
                string name = table.Name;
                if (table.Name == "dbo_Employees")
                {
                    table.Connect = sqlNewConnectionString;
                    table.RefreshLink();
                }
            }
         db.Close();
    }

Upvotes: 0

Views: 492

Answers (1)

Erik A
Erik A

Reputation: 32642

That FileDSN is clearly invalid. A FileDSN should point to a .dsn file. If you want to use a normal DSN, use DSN=, not FileDSN=. Since there's an invalid DSN, you can't relink.

I highly recommend you go DSNless, and just use a connection string, but providing a valid FileDSN should work too.

Upvotes: 1

Related Questions