Box

Box

  • NA
  • 51
  • 41.7k

opening an access databse and running a macro

Feb 7 2011 10:43 AM

I've been trying for a few hours now to get this to work.
I've treid various ways of doing what i need to with the latest one being
:
 
Access.Application acApp =
new Access.ApplicationClass();//create msaccess application
acApp.OpenCurrentDatabase(@"\\d1fil002\sps\DB\NOSY\Tactical\test.mdb",
false,null);//open mdb file
object
oMissing = System.Reflection.Missing.Value;
 

object
rt = acApp.Run("UpdateNCVC");
 

acApp.Quit(Access.AcQuitOption.acQuitSaveNone);
//exit application

 
I was told to add
 
using
Access = Microsoft.Office.Interop.Access;

 
to the top of it but I get an error saying office is not part of microsoft. im using version 2003 of visual studio.
i originally tried to just conenct to the database via ado and run the sql i needed but this didnt seem to work. basic sql inserts were fine but when running an insert that also used a select to copy a whole table i ran into problems. If anyone can shed any light on how I can achieve this i would be very grateful!
 
The sql that needed running was:
 

INSERT INTO tblNCVCData ( Ref, SBI, Version, Reason, Outome, [QC Result], [2005 Currency], [2005 Value], [2006 Currency], [2006 Value], [2007 Currency], [2007 Value], [2008 Currency], [2008 Value], [2009 Currency], [2009 Value], [2010 Currency], [2010 Value], [2011 Currency], [2011 Value], [2012 Currency], [2012 Value], [Staff ID], [Updated Date], [Duplicate 1], [Duplicate 2] )
SELECT tblNCVC.Ref, tblNCVC.SBI, tblNCVC.Version, tblNCVC.Reason, tblNCVC.Outome, tblNCVC.[QC Result], tblNCVC.[2005 Currency], tblNCVC.[2005 Value], tblNCVC.[2006 Currency], tblNCVC.[2006 Value], tblNCVC.[2007 Currency], tblNCVC.[2007 Value], tblNCVC.[2008 Currency], tblNCVC.[2008 Value], tblNCVC.[2009 Currency], tblNCVC.[2009 Value], tblNCVC.[2010 Currency], tblNCVC.[2010 Value], tblNCVC.[2011 Currency], tblNCVC.[2011 Value], tblNCVC.[2012 Currency], tblNCVC.[2012 Value], tblNCVC.[Staff ID], tblNCVC.[Updated Date], tblNCVC.[Duplicate 1], tblNCVC.[Duplicate 2]
FROM tblNCVC
WHERE (((tblNCVC.Reason)="Manual Payment"));

Thanks

Answers (5)