Code stops working after convering flat MDB to SQL-linked mdb

I have modified my code to count records in each table inside an mdb file. It works flawlessly, until it hits an MDB file using linked tables, which point to a SQL server. The开发者_C百科 code cannot be modified to point to the SQL server directly, it is for an in-place software upgrade scenario.

How can I modify this code to work with regular as well as linked tables?

using System;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Collections;
using System.Data.Common;
using System.Collections.Generic;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            string[] databases = new string[3];
            databases[0] = "data.mdb";
            databases[1] = "chunk.mdb";
            databases[2] = "transactions.mdb";

            DateTime dt = DateTime.Now;
            string filename = "Results-" + dt.Hour + "_" + dt.Minute + "_" + dt.Second + ".txt";
            System.IO.StreamWriter file = new System.IO.StreamWriter(filename);



        foreach(string db in databases)
        {
                    file.WriteLine("##########BEGIN " + db + "##########\r\n");
                    Console.Write("Processing " + db + " Database . . . ");

                    string strAccessConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\financial\" + db;

                    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
                    DataTable userTables = null;
                    DataSet myDataSet = new DataSet();
                    OleDbConnection conn = new OleDbConnection(strAccessConn);

                    using (DbConnection connection = factory.CreateConnection())
                    {
                        connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\financial\DATA\" + db;
                        string[] restrictions = new string[4];
                        restrictions[3] = "Table"; 

                        try
                        {
                            connection.Open();
                        }
                        catch
                        {
                            Console.WriteLine("Error opening MDB file. Please ensure it is in the correct location");
                        }

                        userTables = connection.GetSchema("Tables", restrictions);
                    }


                   ArrayList tables = new ArrayList();
                   for (int i=0; i < userTables.Rows.Count; i++)
                        tables.Add(userTables.Rows[i][2].ToString());


                   foreach (string tbl in tables)
                   {
                            string queryString = "SELECT COUNT(*) FROM " + "[" + tbl + "]";
                            OleDbCommand command = new OleDbCommand(queryString, conn);
                            command.Connection.Open();

                            try
                            {
                                int records = (int)command.ExecuteScalar();
                                file.WriteLine("{0,-45}" + records,tbl);
                            }
                            catch (OleDbException e)
                            {
                                Console.WriteLine(e + "OLEDB Exception Occured.");
                            }

                            command.Connection.Close();
                   }


                    file.WriteLine("\r\n##########END " + db + "##########");
                    Console.WriteLine("Done!\n");
         }

            file.Close();
            Console.WriteLine(@"All Databases Complete. Press any key to continue...");
            Console.Read();  // Press any key to continue...
            System.Diagnostics.Process.Start("notepad.exe",filename);

        }
    }
}

Hi I believe that can't be done with ado.net, I had the same scenario some years ago with ado.net 1. You have to use ADOX.

here is a list of ADOX examples that can be useful http://allenbrowne.com/func-adox.html

ADOX does not make a difference between table and linked tables. ill take a look to my hard drive and I may post an example later.

you should execute your query with ADOX and you won't have the same problem that you have with ADO.net

You can also do this with DAO.