2

I wrote a little program in Visual Studio 2010 to open an Access database, enter a value into a table, and open a form. Pretty simple, and the program works great on my laptop.

The problem arises when I try and run the exe on a second PC. I cannot enter a value into a table. Both computers can open the project with Visual Studio 2010 just fine. The error I receive when I run it is:

"System.Runtime.InteropServices.COMException (0x800A09C5): The RunSQL action was canceled.\r\n at Microsoft.Office.Interop.Access.DoCmd.RunSQL(Object SQLStatement, Object UseTransaction)\r\n at OpenGasAlarmSheet.Program.Main(String[] args) in \Program.cs:line 47"

I pass some arguments into a string to get the equivalent of:

String sql = "INSERT INTO tbl_LOG ([ALARM], [DATE]) 
              VALUES ('KNITERV4', #07-17-2012 10:22:29 AM#);"

And here's the code for part of the program to run that SQL string: 

Access.Application oAccess = null;
oAccess = new Access.Application();
oAccess.OpenCurrentDatabase(<file path to mdb>, false);
oAccess.DoCmd.RunSQL(sql); //This is line 47

I've been trying to fix this error for about a week, and I'm completely stuck. Any help would be much appreciated!!!

Thanks, Chris

Edit: 07/18/2012

Here's where I'm at....

Option 1:

String sql = "INSERT INTO tbl_LOG ([ALARM], [DATE]) 
     VALUES ('KNITERV4', #07-17-2012 10:22:29 AM#);";
oAccess.CurrentDb().Execute(sql); //ERROR HERE

Results in.....

PC #1: Works correctly!

PC #2 & #3: Error (see below)

"System.Runtime.InteropServices.COMException (0x8002801D): Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))\r\n at Microsoft.Office.Interop.Access.ApplicationClass.CurrentDb()\r\n at Test.Program.Main(String[] args) in \Program.cs:line 47"

Option 2:

String sql = "INSERT INTO tbl_LOG ([ALARM], [DATE]) 
     VALUES ('KNITERV4', #07-17-2012 10:22:29 AM#);";
oAccess.DoCmd.RunSQL(sql); //ERROR HERE

Results in.....

PC #1: Works correctly!

PC #2 & #3: Error (see below)

"System.Runtime.InteropServices.COMException (0x800A09C5): The RunSQL action was canceled.\r\n at Microsoft.Office.Interop.Access.DoCmd.RunSQL(Object SQLStatement, Object UseTransaction)\r\n at OpenGasAlarmSheet.Program.Main(String[] args) in \Program.cs:line 47"

Option 3:

cn.ConnectionString = oAccess.CurrentProject.Connection.ConnectionString; //ERROR HERE
cn.Open();

rs.ActiveConnection = cn;
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic;
rs.CursorType = ADODB.CursorTypeEnum.adOpenKeyset;
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

rs.Open("tbl_LOG");
rs.AddNew("ALARM", "KNITERV4");
rs.UpdateBatch();

Results in.....

PC #1 & #3: Error (see below)

"System.Runtime.InteropServices.COMException (0x8000FFFF): Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))\r\n at Microsoft.Office.Interop.Access._CurrentProject.get_Connection()\r\n
at Test.Program.Main(String[] args) in \Test\Program.cs:line 38"

PC #2: Works Correctly!!!

I can't get this thing to work on multiple PCs. FRUSTRATING!!! :(

user1532518
  • 21
  • 3
  • This sounds like an installation problem, perhaps with Access drivers. Can you open the Access database and perform inserts manually? – ron tornambe Jul 17 '12 at 17:44
  • Are the Access versions the same on both PC's? – Botonomous Jul 17 '12 at 17:55
  • @rontornambe Yes, I can manually insert into the Access database using the computer that's having problems. – user1532518 Jul 17 '12 at 18:26
  • @Anon Both PCs show the same version (12.0.6423.1000). Thanks guys for the comments. Hopefully we can figure this out. :) – user1532518 Jul 17 '12 at 18:28
  • Have you tried currentdb.execute for your sql? – Fionnuala Jul 17 '12 at 18:42
  • Have you checked to see if the OLEDB drivers (Microsoft.ACE.OLEDB.12.0 OLEDB and Microsoft.Jet.OLEDB.4.0) are installed? – ron tornambe Jul 17 '12 at 18:45
  • @Remou Just changed the code to currentdb.execute and I get this error on the second PC: "System.Runtime.InteropServices.COMException (0x8002801D): Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))\r\n at Microsoft.Office.Interop.Access.ApplicationClass.CurrentDb()\r\n at Test.Program.Main(String[] args) in \\Program.cs:line 47" – user1532518 Jul 17 '12 at 20:01
  • @rontornambe I looked in regedit and I see Microsoft.ACE.OLEDB.12.0 and Microsoft.Jet.OLEDB.4.0 under the HKEY_CLASSES_ROOT. – user1532518 Jul 17 '12 at 20:45
  • I ran the repair Microsoft Office Enterprise 2007. I read a few forums saying it might be some bad dlls. That did not work. – user1532518 Jul 17 '12 at 20:55
  • What happens when you start Access on the second PC, then open the database in that Access session? Does the db open without error, and can you successfully execute your same `INSERT` statement within that session? – HansUp Jul 18 '12 at 04:53
  • @HansUp Yes, I just ran the SQL and it inserted the row after hitting 'Yes' on the message box to append 1 row(s). To get it to work I had to change the Trust Center option because I was in Disabled Mode. Must be corporate settings. However, the error still shows up when I run the code. – user1532518 Jul 18 '12 at 17:46

1 Answers1

3

It's probably far too late to help the OP, but hopefully this will help someone else like me who couldn't find an answer.

For me, the solution to the The RunSQL action was canceled. error was to open the MDB in Access and click the Enable Content button. This file was generated on a client's machine (external to our network, of course). Judging by the fact TortoiseSVN showed the file as changed after that and I was able to commit those changes, it seems that enabling content physically modifies the file. It must store some permissions inside the file, perhaps even storing permissions on a per machine basis, and use them even when executing via Interop.

This was with MS Access 2010.

jpmc26
  • 28,463
  • 14
  • 94
  • 146