public void GetProcessId_Test()
{
var microsoftAccess = new Microsoft.Office.Interop.Access.Application();
microsoftAccess.OpenCurrentDatabase(@"D:\MSAccessDatabase\MSAccessDatabase.accdb");
var myName = microsoftAccess.Run("GetName");
int id;
GetWindowThreadProcessId(microsoftAccess.hWndAccessApp(), out id);
var result = Process.GetProcessById(id);
Assert.IsNotNull(result);
Console.WriteLine(result.Id);
if (microsoftAccess != null)
{
microsoftAccess.Quit();
Marshal.ReleaseComObject(microsoftAccess);
microsoftAccess = null;
}
}
public void GetName()
{
var microsoftAccess = new Microsoft.Office.Interop.Access.Application();
microsoftAccess.Visible = true;
microsoftAccess.OpenCurrentDatabase(@"D:\MSAccessDatabase.accdb", false);
var myName = microsoftAccess.Run("GetName");
Console.WriteLine($"My Name: {myName}");
Thread.Sleep(3000);
microsoftAccess.CloseCurrentDatabase();
microsoftAccess.Quit();
}
private static void RunAccessFunction(string functionname, string Parameter1, string Parameter2)
{
object oMissing = System.Reflection.Missing.Value;
try
{
_oAccess.Run(functionname, Parameter1, Parameter2, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing
, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing
, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing
, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing
, ref oMissing, ref oMissing);
}
catch (Exception ex)
{
throw new Exception("Error running function in Access: " + ex.Message);
}
}
private static void RunAccessFunction(string functionname, string Parameter1, string Parameter2 = "")
{
object oMissing = System.Reflection.Missing.Value;
try
{
if (_oAccess != null)
{
_oAccess.Run(functionname, Parameter1, Parameter2, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing
, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing
, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing
, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing
, ref oMissing, ref oMissing);
}
}
catch (Exception ex)
{ //Probably, user has closed Access, the TE is closed, but we are still holding on to a handle to the Access process
MessageBox.Show("RunAccessFunction: There was a problem activating the TE. Details: \r\n\r\n" + ex);
CloseAccess();
}
}
public void runApp(string databaseName, string function)
{
VBA.VBComponent f = null;
VBA.VBComponent f2 = null;
Microsoft.Office.Interop.Access.Application app = null;
object Missing = System.Reflection.Missing.Value;
Object tempObject = null;
try
{
app = new Microsoft.Office.Interop.Access.Application();
app.Visible = true;
app.OpenCurrentDatabase(databaseName, false, "");
//Step 1: Programatically create a new temporary class module in the target Access file, with which to call the target function in the Access database
//Create a Guid to append to the object name, so that in case the temporary class and module somehow get "stuck",
//the temp objects won't interfere with other objects each other (if there are multiples).
string tempGuid = Guid.NewGuid().ToString("N");
f = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_ClassModule);
//We must set the Instancing to 2-PublicNotCreatable
f.Properties.Item("Instancing").Value = 2;
f.Name = "TEMP_CLASS_" + tempGuid;
f.CodeModule.AddFromString(
"Public Sub TempClassCall()\r\n" +
" Call " + function + "\r\n" +
"End Sub\r\n");
//Step 2: Append a new standard module to the target Access file, and create a public function to instantiate the class and return it.
f2 = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
f2.Name = "TEMP_MODULE";
f2.CodeModule.AddFromString(string.Format(
"Public Function instantiateTempClass_{0}() As Object\r\n" +
" Set instantiateTempClass_{0} = New TEMP_CLASS_{0}\r\n" +
"End Function"
, tempGuid));
//Step 3: Get a reference to a new TEMP_CLASS_* object
tempObject = app.Run("instantiateTempClass_" + tempGuid, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing);
//Step 4: Call the method on the TEMP_CLASS_* object.
Microsoft.VisualBasic.Interaction.CallByName(tempObject, "TempClassCall", Microsoft.VisualBasic.CallType.Method);
}
catch (COMException e)
{
MessageBox.Show("A VBA Exception occurred in file:" + e.Message);
}
catch (Exception e)
{
MessageBox.Show("A general exception has occurred: " + e.StackTrace.ToString());
}
finally
{
//Clean up
if (f != null)
{
app.VBE.ActiveVBProject.VBComponents.Remove(f);
Marshal.FinalReleaseComObject(f);
}
if (f2 != null)
{
app.VBE.ActiveVBProject.VBComponents.Remove(f2);
Marshal.FinalReleaseComObject(f2);
}
if (tempObject != null)
{
Marshal.FinalReleaseComObject(tempObject);
}
if (app != null)
{
//Step 5: When you close the database, you call Application.Quit() with acQuitSaveNone, so none of the VBA code you just created gets saved.
app.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
Marshal.FinalReleaseComObject(app);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void Work_Macro_Click(object sender, EventArgs e)
{
//Проверяем существование базы данных в текущем каталоге
String db_filename = Directory.GetCurrentDirectory() + @"\db.accdb";
if (File.Exists(Directory.GetCurrentDirectory() + @"\db.accdb") == false)
{
//Создание каталога ADOX
ADOX.Catalog adoxCat = new ADOX.Catalog();
//Создание базы данных
adoxCat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Directory.GetCurrentDirectory() + @"\db.accdb" + ";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Password="******"\db.accdb", false);
//Создаем ссылку класса VBProject на текущий объект БД
Microsoft.Vbe.Interop.VBProject project = oAccess.VBE.VBProjects.Item(1);
//Создаем ссылку класса VBComponent на созданный модуль БД
Microsoft.Vbe.Interop.VBComponent module = project.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
//Вставляем текст в модуль
module.CodeModule.AddFromString(Properties.Resources.Macro);
//Сохранение макроса
oAccess.DoCmd.Save(Microsoft.Office.Interop.Access.AcObjectType.acModule, module.Name.ToString());
//Присваеваем список схем и выбранных таблиц
string[] Schemas = new string[ListBox_Schemes.Items.Count];
string[] Tables = new string[CheckedListBox_Tables.CheckedItems.Count];
//Цикл по схемам
int i = 0;
foreach (Object Схема in ListBox_Schemes.Items)
{
Schemas[i] = (string)Схема;
i++;
}
i = 0;
//Цикл по таблицам
foreach (Object Таблица in CheckedListBox_Tables.CheckedItems)
{
Tables[i] = (string)Таблица;
i++;
}
//Запуск функции "export" в созданном модуле VBA
oAccess.Run("export", Schemas, Tables, Server.Text, Port.Text, Uid.Text, Pwd.Text);
//Закрытие модуля VBA
oAccess.DoCmd.Close(Microsoft.Office.Interop.Access.AcObjectType.acModule, module.Name.ToString(), Microsoft.Office.Interop.Access.AcCloseSave.acSaveYes);
//Удаление модуля VBA
oAccess.DoCmd.DeleteObject(Microsoft.Office.Interop.Access.AcObjectType.acModule, module.Name.ToString());
//Выход из приложения ACCESS без сохранения изменений
oAccess.DoCmd.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
//Освободить объект oAccess
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
oAccess = null;
}
