StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAutomation Error 80004005
text
Body
copied!<p>I've been beating myself over the head with this for a few days now, searched up and down the internet. I know there is a lot I don't quite get about Com Interop but I've had success building and using simpler DLL's with Excel. Anyways to the point.</p> <p>I get the above mentioned Error <code>-2147467259 80004005 Automation Error Unspecified Error</code> in Excel VBA when running the following code wrapped in a DLL.</p> <pre><code>[GuidAttribute("96BE21CD-887B-4770-9FAA-CF395375AEA9")] [ComVisibleAttribute(true)] interface QInterface { void ConnectionFill(string SQLQuery, string CONStr); string QueryValue(int QueryKey); string ConnectionValue(int ConnectionKey); string outputFile { get; set; } void ThreadTest(); } [ClassInterfaceAttribute(ClassInterfaceType.None)] [ProgIdAttribute("QueryThread")] class QueryThread : QInterface { DataSet QueryReturn = new DataSet(); private ArrayList SQLList = new ArrayList(); private ArrayList ConList = new ArrayList(); private string OutputFile; public void ConnectionFill(string SQLQuery, string CONStr) { SQLList.Add(SQLQuery); ConList.Add(CONStr); } public string QueryValue(int QueryKey) { return SQLList[QueryKey].ToString(); } public string ConnectionValue(int ConnectionKey) { return ConList[ConnectionKey].ToString(); } public string outputFile { set { OutputFile = value; } get { return OutputFile; } } public void ThreadTest() { int i = 0; i = SQLList.Count; Thread[] myThreads; myThreads = new Thread[i]; for (int t = 0; t != i; t++) { myThreads[t] = new Thread(() => ThreadRun(SQLList[t].ToString(), ConList[t].ToString())); myThreads[t].Name = "Thread " + t; myThreads[t].IsBackground = true; myThreads[t].Start(); Thread.Sleep(600); if (t > 9) { myThreads[t - 9].Join(); } } for (int t = 0; t != i; t++) { while (myThreads[t].IsAlive) { Thread.Sleep(20); } } TextWriter tw = new StreamWriter(OutputFile); for (int t = 0; t < QueryReturn.Tables.Count; t++) { DataTableReader DR = QueryReturn.Tables[t].CreateDataReader(); while (DR.Read()) { tw.WriteLine("{0} : {1}", DR.GetValue(0), DR.GetValue(1)); } } tw.Close(); QueryReturn.Dispose(); } private void ThreadRun(string SQLString, string ConString) { try { OleDbConnection DBCon = new OleDbConnection(ConString); DBCon.Open(); Thread.Sleep(200); OleDbCommand DBCmd = new OleDbCommand(SQLString, DBCon); OleDbDataAdapter DataAdapter = new OleDbDataAdapter(DBCmd); Thread.Sleep(200); DataAdapter.Fill(QueryReturn, Thread.CurrentThread.Name.ToString()); DBCon.Close(); DataAdapter.Dispose(); DBCon.Dispose(); DBCmd.Dispose(); } finally { } } } </code></pre> <p>using this VBA code...</p> <pre><code>Sub test() Dim QT As New QueryThreading.QueryThread Dim MyResults As String Dim outputfile As String Dim InputStuff(1, 1) As String InputStuff(0, 0) = "Select DISTINCT * From TrackingData;" InputStuff(0, 1) = "Provider =Microsoft.ACE.OLEDB.12.0;Data Source =C:\Users\Nick\Desktop\test.accdb; Persist Security Info =False;Connection Timeout=7;" InputStuff(1, 0) = "Select DISTINCT * From TrackingData;" InputStuff(1, 1) = "Provider =Microsoft.ACE.OLEDB.12.0;Data Source =C:\Users\Nick\Desktop\test2.accdb; Persist Security Info =False;Connection Timeout=7;" QT.ConnectionFill InputStuff(0, 0), InputStuff(0, 1) QT.ConnectionFill InputStuff(1, 0), InputStuff(1, 1) outputfile = "C:\Users\Nick\Desktop\testrun.txt" QT.outputfile = outputfile QT.ThreadTest End Sub </code></pre> <p>It runs fine is a pure C# console application. Works perfect and quick with no problems. But via VBA I get the error.</p> <p>I'm assuming it's something to do with the calling of the access databases in multiple threads. I know there is a lot of junk code in there, it's not optimized of course I'm still in the "playing around" phase. </p> <p>I've used RegAsm and enabled com interop and all such stuff, I can read back from the returns just fine. So I know the DLL is working right, just when I fill the threads and run "ThreadTest()" I get the automation error.</p> <p>If I run it a second time Excel locks up.</p> <p>Any help would be greatly appreciated.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload