2

I'm trying to connect to an access DB and retrieve information.

Everything is going well the project is almost complete but I need to figure out how to quit interop.

I start by creating my access application then I make sure visible is set to false. (I don't see Access) but as soon as my code get to line app.Quit() (verified using step though) Access flashes on the screen just to disappear again.

For added information: If I do a step through the Access window doesn't disappear and I can't manual close it (red x top right). The application just reopens blank. I have to force close it.

As you can see in my commented out code I use a Process Kill to make sure that nothing flashes on the screen but that causes my application to become unstable (so many access DB backups created due to crash).

If you are experiences the normal (blank stare). Please at least let me know where to start digging if you have a gut feeling.

static public DataTable ExecuteSQLToDataTable(string sql)
        {
            DataTable dt = new DataTable();

            lock (Locks.AccessDB)
            {
                Microsoft.Office.Interop.Access.Application accApp = new Microsoft.Office.Interop.Access.Application();
                accApp.Visible = false;
                Microsoft.Office.Interop.Access.Dao.Recordset rst = null;
                Microsoft.Office.Interop.Access.Dao.Database cdb = null;


                try
                {
                    accApp.OpenCurrentDatabase(ConnectionDatabase.DatabasePath, false, @"[somepassword]");

                    cdb = accApp.CurrentDb();
                    rst =
                        cdb.OpenRecordset(sql,
                        Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenSnapshot);
                    rst.MoveLast();
                    int recordCount = rst.RecordCount;
                    rst.MoveFirst();
                    object[,] recordsArray = (object[,])rst.GetRows(recordCount);

                    var rowCount = recordsArray.GetLength(1);
                    var columnCount = recordsArray.GetLength(0);

                    var dtTemp = new DataTable();
                    foreach (Microsoft.Office.Interop.Access.Dao.Field fld in rst.Fields)
                        dt.Columns.Add(fld.Name, typeof(string));

                    foreach (var r in Enumerable.Range(1, rowCount))
                        dt.Rows.Add(Enumerable.Range(1, columnCount)
                            .Select(c => recordsArray[c - 1, r - 1]).ToArray());

                }
                catch
                {
                    //TODO Add catch
                }
                finally
                {

                    GetWindowThreadProcessId(accApp.hWndAccessApp(), out int id);
                    if (rst != null) { Marshal.ReleaseComObject(rst); }
                    if (cdb != null) { Marshal.ReleaseComObject(cdb); }
                    if (accApp != null) { accApp.Quit(); Marshal.ReleaseComObject(accApp); }
                    rst = null;
                    cdb = null;
                    accApp = null;
                    //Process.GetProcessById(id).Kill();

                }

                return dt;
            }
        }

Locks is a static class Locks.AccessDB is just an empty object

Mandelbrotter
  • 2,216
  • 2
  • 11
  • 28
  • If I were you, I wouldn't try to kill it. I would move the Access window out of sight (SetWindowPos) and then I woudn't care what Quit does. – Nick Oct 01 '19 at 12:51
  • Thank you. This is a workaround I can live with. I still want an answer to develop my understanding but this will do for the project. Thank you again. – Mandelbrotter Oct 01 '19 at 13:23
  • There is no actual answer. Obviously, on closing Access is doing something to show itself on screen. There is nothing you can do about it. – Nick Oct 01 '19 at 14:02

1 Answers1

1

I am inclined to believe that Access is doing something of its own to show itself on screen when you call all Quit().

You can workaround the issue by using SetWindowPos to move the Access window off the screen so you never see it when you call Quit().

Nick
  • 4,787
  • 2
  • 18
  • 24
  • Based on the answer for https://stackoverflow.com/questions/16185217/c-sharp-process-mainwindowhandle-always-returns-intptr-zero could I still use SetWindowPos to move a window that has its visibility set to false? I'm running into the same issue as OP from link. – Mandelbrotter Oct 01 '19 at 15:12