public static int CreateDataBase(string path)
{
Access.Application AccessAplication = new Access.Application();
AccessAplication.NewCurrentDatabase(path,Access.AcNewDatabaseFormat.acNewDatabaseFormatUserDefault);
AccessAplication.CloseCurrentDatabase();
AccessAplication.Quit();
return 0;
}
/// <summary>
/// Transforma o relatorio do Microsoft Access em arquivo pdf
/// </summary>
/// <param name="reportName"></param>
/// <param name="msAccess"></param>
/// <param name="outputPdf"></param>
/// <param name="filtersReport"></param>
public static void GenerateFile(string reportName, string msAccess, string outputPdf, string filterReport, bool debugMode = false)
{
var app = new MsAccess.Application();
try
{
if (string.IsNullOrWhiteSpace(reportName))
throw new ArgumentException("Nome do relatório inválido", "reportName");
if (string.IsNullOrWhiteSpace(msAccess) || !File.Exists(msAccess))
throw new ArgumentException("Arquivo do Microsoft Access Inválido ou não encontrado", "msAccess");
if (string.IsNullOrWhiteSpace(outputPdf))
throw new ArgumentException("Arquivo de destino inválido", "msAccess");
app.OpenCurrentDatabase(msAccess, false, "");
app.Visible = debugMode == true;
if (!string.IsNullOrWhiteSpace(filterReport))
{
app.DoCmd.OpenReport(
reportName,
MsAccess.AcView.acViewReport,
null,
filterReport,
MsAccess.AcWindowMode.acHidden,
null
);
ExportToPdf(app, reportName, outputPdf);
}
else
{
ExportToPdf(app, reportName, outputPdf);
}
app.CloseCurrentDatabase();
app.DoCmd.Close(
MsAccess.AcObjectType.acReport,
reportName,
MsAccess.AcCloseSave.acSaveNo);
}
finally
{
app.Quit(MsAccess.AcQuitOption.acQuitSaveNone);
Marshal.FinalReleaseComObject(app);
app = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void TextToAccessConvert(string path)
{
var accApplication = new ACCESS.Application();
var pieces = path.Split('\\');
string directory = "";
foreach (var piece in pieces)
{
if (piece.Contains("."))
{
continue;
}
directory += piece;
directory += "\\";
}
if (File.Exists(path))
{
File.Delete(path);
}
accApplication.NewCurrentDatabase(path);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "AMT", FileName: directory + "tempAMT.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "AMT_Proteins", FileName: directory + "tempAMT_Proteins.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "AMT_to_Protein_Map", FileName: directory + "tempAMT_to_Protein_Map.txt", HasFieldNames: true);
accApplication.CloseCurrentDatabase();
accApplication.Quit();
File.Delete(directory + "tempAMT.txt");
File.Delete(directory + "tempAMT_Proteins.txt");
File.Delete(directory + "tempAMT_to_Protein_Map.txt");
}
/// <summary>
/// Exports the selected file to an Excel file.
/// </summary>
/// <param name="fileName">The absolute path of the Excel file to export to.</param>
public void ExportCurrentFileToExcel(string fileName)
{
if (File.Exists(@fileName))
{
try
{
File.Delete(@fileName);
}
catch (Exception)
{
MessageBox.Show("Unable to replace file \"" + @fileName + "\"! It may already " +
"be in use by another applicaton.", "Export Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
try
{
var application = new Microsoft.Office.Interop.Access.Application();
application.OpenCurrentDatabase(this.currentFile);
application.DoCmd.TransferSpreadsheet(AcDataTransferType.acExport,
AcSpreadSheetType.acSpreadsheetTypeExcel12Xml,
DatabaseModel.TABLE_NAME, fileName, true);
application.CloseCurrentDatabase();
application.Quit();
Marshal.ReleaseComObject(application);
MessageBox.Show("\"" + fileName + "\" saved successfully!", "Export Success",
MessageBoxButtons.OK, MessageBoxIcon.Information);
} catch (Exception ex)
{
MessageBox.Show("\"" + fileName + "\" failed to export! Error: \"" + ex.ToString() +
"\".", "Export Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void RunMacro()
{
Console.WriteLine("uploading...");
Access.Application oAccess = new Access.Application();
oAccess.Visible = false;
oAccess.OpenCurrentDatabase(fileName, false);
try
{
oAccess.Run("ImportMacro");
oAccess.Quit();
}
catch (Exception x)
{
oAccess.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
oAccess = null;
if (attemptNum < 3)
{
attemptNum++;
Console.WriteLine("attempting time " + attemptNum);
RunMacro();
}
Console.WriteLine("something went wrong with the access macro: \n" + x.Message);
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
oAccess = null;
}
}
private void printCategoryBtn_Click(object sender, EventArgs e)
{
//this button is only for printing out category reports regardless of location and status.
string strCriteria;
string CatIDStart = "";
string CatIDEnd = "";
//check if "All Categories has been selected"
if (categoriesLbx.SelectedValue.ToString() == "-1")
{
strCriteria = "";
}
else
{
CatIDStart = lUEquipmentDataSet.tblCategory.FindByCat_ID(int.Parse(categoriesLbx.SelectedValue.ToString())).Cat_IDStart;
CatIDEnd = lUEquipmentDataSet.tblCategory.FindByCat_ID(int.Parse(categoriesLbx.SelectedValue.ToString())).Cat_IDEnd;
strCriteria = "Equip_Number between '" + CatIDStart + "' AND '" + CatIDEnd + "'";
}
Access.Application oAccess = new Access.Application();
oAccess.Visible = true;
oAccess.OpenCurrentDatabase(Path.Combine(Environment.CurrentDirectory, "LUEquipment.mdb"), false);
oAccess.DoCmd.SetParameter("category", locationsLbx.SelectedValue.ToString());
oAccess.DoCmd.OpenReport("rptEquipment", Access.AcView.acViewPreview, //View
System.Reflection.Missing.Value, //FilterName
strCriteria);
}
/// <summary>
/// Creates a new instance of Access.Application interop object and loads the database or project file
/// </summary>
/// <exception cref="COMException">when user cancel security message in databases with VBA code or when Macros security level is high</exception>
protected virtual void IntanceAccessApplication()
{
Application = new Access.Application();
Application.UserControl = false;
Application.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
Application.Visible = false;
}
/// <summary>
/// Compare et retourne une note pour la comparaison de URIAttendu avec URIAComparer
/// </summary>
/// <param name="worker">Tâche en arrière plan</param>
/// <param name="e">Événement pour interagir avec la tâche</param>
/// <returns>Une note</returns>
public decimal Comparer(BackgroundWorker worker, DoWorkEventArgs e)
{
this._worker = worker;
Access.Application access = null;
worker.ReportProgress(0, "Démarrage");
try
{
access = new Access.Application();
RapporterProgression("Access démarré");
access.OpenCurrentDatabase(URIAttendu, false, null);
RapporterProgression("Bd ouverte");
RapporterProgression("Lecture des tables : " + access.CurrentData.AllTables.Count);
}
catch (Exception exp)
{
RapporterProgression(exp.Message);
}
finally
{
if (access != null)
{
access.Quit(Access.AcQuitOption.acQuitSaveNone);
Marshal.ReleaseComObject(access);
access = null;
RapporterProgression("Access fermé");
}
}
return(0);
}
/// <summary>
///
/// </summary>
/// <param name="databaseLocation"></param>
/// <param name="queryNameToExport"></param>
/// <param name="locationToExportTo"></param>
/// <returns></returns>
public bool ExportQuery(string tableName, string locationToExportTo)
{
try {
//init access file
Access.Application oAccess = null;
// Start a new instance of Access for Automation:
oAccess = new Access.Application();
oAccess.Visible = false;
// Open a database in exclusive mode:
oAccess.OpenCurrentDatabase(Access_FileFullName);
//transfer access data to excel file
oAccess.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport, Access.AcSpreadSheetType.acSpreadsheetTypeExcel12, tableName, locationToExportTo, true);
//close database
oAccess.CloseCurrentDatabase();
oAccess.Quit();
Marshal.ReleaseComObject(oAccess);
return(true);
} catch (Exception ex) {
System.Windows.MessageBox.Show(ex.ToString());
return(false);
}
}
private void searchBtn_Click(object sender, EventArgs e)
{
//create equipment number
string equipNumber = search1TxtBox.Text + search2TxtBox.Text;
string whereCond = "Equip_Number ='" + equipNumber + "'"; //whereCond will be use to filter result in Access
this.tblEquipTableAdapter1.Fill(this.luEquipmentDataSet1.tblEquip);
//input validation
if (equipNumber.Length != 6 || !int.TryParse(equipNumber, out int tempItemNumb))
{
MessageBox.Show("Please enter a valid equipment number!", "Error");
}
else if (this.luEquipmentDataSet1.tblEquip.Select("Equip_Number = '" + equipNumber + "'").Length != 1)
{
MessageBox.Show("There is no item with that number.", "Error");
}
else
{
//open up Access => open Equipment History Report with preset condition
Access.Application oAccess = new Access.Application();
oAccess.Visible = true;
oAccess.OpenCurrentDatabase(Path.Combine(Environment.CurrentDirectory, "LUEquipment.mdb"), false);
//open Access, equipment number will be asked within Access.
oAccess.DoCmd.OpenReport("rptEquipHistory", Access.AcView.acViewPreview, //View
System.Reflection.Missing.Value, //FilterName,
whereCond //WhereCondition
);
}
//clear textbox
search2TxtBox.Clear();
search1TxtBox.Clear();
}
private void printEquipBtn_Click(object sender, EventArgs e)
{
string CatIDStart;
string CatIDEnd;
string strCriteria = "";
if (categoriesLbx.SelectedValue.ToString() == "-1") //all category
{
CatIDStart = "";
CatIDEnd = "";
if (locationsLbx.SelectedValue.ToString() == "-1" && statusLbx.SelectedValue.ToString() != "-1") // all locations but NOT all status
{
strCriteria = "Status_ID LIKE '" + statusLbx.SelectedValue + "'";
}
else if (locationsLbx.SelectedValue.ToString() == "-1" && statusLbx.SelectedValue.ToString() == "-1") //all locations AND all status
{
strCriteria = "";
}
else if (locationsLbx.SelectedValue.ToString() != "-1" && statusLbx.SelectedValue.ToString() == "-1") //NOT ALL locations AND all status
{
strCriteria = "Loc_ID LIKE '" + locationsLbx.SelectedValue.ToString() + "'";
}
else if (locationsLbx.SelectedValue.ToString() != "-1" && statusLbx.SelectedValue.ToString() != "-1") //specific location,specific status
{
strCriteria = "Loc_ID LIKE '" + locationsLbx.SelectedValue + "' AND Status_ID LIKE '" + statusLbx.SelectedValue + "'";
}
}
else //specific category
{
CatIDStart = lUEquipmentDataSet.tblCategory.FindByCat_ID(int.Parse(categoriesLbx.SelectedValue.ToString())).Cat_IDStart;
CatIDEnd = lUEquipmentDataSet.tblCategory.FindByCat_ID(int.Parse(categoriesLbx.SelectedValue.ToString())).Cat_IDEnd;
if (locationsLbx.SelectedValue.ToString() == "-1" && statusLbx.SelectedValue.ToString() != "-1") // all locations but NOT all status
{
strCriteria = "Status_ID LIKE '" + statusLbx.SelectedValue + "' AND Equip_Number between '" + CatIDStart + "' AND '" + CatIDEnd + "'";
}
else if (locationsLbx.SelectedValue.ToString() == "-1" && statusLbx.SelectedValue.ToString() == "-1") //all locations AND all status
{
strCriteria = "Equip_Number between '" + CatIDStart + "' AND '" + CatIDEnd + "'";
}
else if (locationsLbx.SelectedValue.ToString() != "-1" && statusLbx.SelectedValue.ToString() == "-1") //NOT ALL locations AND all status
{
strCriteria = "Loc_ID LIKE '" + locationsLbx.SelectedValue.ToString() + "' AND Equip_Number between '" + CatIDStart + "' AND '" + CatIDEnd + "'";
}
else if (locationsLbx.SelectedValue.ToString() != "-1" && statusLbx.SelectedValue.ToString() != "-1") //specific location,specific status
{
strCriteria = "Loc_ID LIKE '" + locationsLbx.SelectedValue + "' AND Status_ID LIKE '" + statusLbx.SelectedValue + "' AND Equip_Number between '" + CatIDStart + "' AND '" + CatIDEnd + "'";
}
}
//print Equipment by location
Access.Application oAccess = new Access.Application();
oAccess.Visible = true;
oAccess.OpenCurrentDatabase(Path.Combine(Environment.CurrentDirectory, "LUEquipment.mdb"), false);
oAccess.DoCmd.OpenReport("rptEquipment", Access.AcView.acViewPreview, //View
System.Reflection.Missing.Value, strCriteria
);
}
public void Dispose()
{
if (application.CurrentDb() != null)
{
application.CloseCurrentDatabase();
}
application = null;
}
//DataTable from the linked external database with coulumns including a controlling field and updating fields.
private void CreateSourceTable()
{
try
{
oAccess = new Access.Application();
foreach (string tableName in externalFields.Keys)
{
LinkedParameter linkedParam = externalFields[tableName];
DataTable sourceTable = new DataTable(linkedParam.TableName);
sourceTable.Columns.Add(linkedParam.ControlField);
foreach (string updateParam in linkedParam.UpdateParameterField.Keys)
{
string updateField = linkedParam.UpdateParameterField[updateParam];
sourceTable.Columns.Add(updateField);
}
oAccess.OpenCurrentDatabase(linkedParam.DBPath);
oAccess.Visible = false;
extDB = oAccess.CurrentDb();
string strFields = "";
foreach (DataColumn col in sourceTable.Columns)
{
strFields += " [" + col.ColumnName + "],";
}
strFields=strFields.Substring(0, strFields.Length - 1);//to remove the last colone in the string
string sqlQuery = "SELECT" + strFields + " FROM [" + linkedParam.TableName + "]";
Recordset recordset;
recordset = extDB.OpenRecordset(sqlQuery);
DataRow row;
while (!recordset.EOF)
{
row = sourceTable.NewRow();
foreach (DataColumn column in sourceTable.Columns)
{
row[column] = recordset.Fields[column.ColumnName].Value;
}
sourceTable.Rows.Add(row);
recordset.MoveNext();
}
recordset.Close();
if (null != oAccess.CurrentDb()) { oAccess.CloseCurrentDatabase(); }
sourceTables.Add(tableName, sourceTable);
}
}
catch (Exception ex)
{
MessageBox.Show("Cannot create source tables. \n" + ex.Message, "ExternalReference Error:", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
static void Main(string[] args)
{
var access = new Access.Application();
access.OpenCurrentDatabase(@"C:\whatever.mdb");
access.DoCmd.RunSQL("INSERT INTO Table1 SELECT * FROM Table2");
access.CloseCurrentDatabase();
Marshal.ReleaseComObject(access);
}
private void outstandingAccountsBtn_Click(object sender, EventArgs e)
{
Access.Application oAccess = new Access.Application();
oAccess.OpenCurrentDatabase(Path.Combine(Environment.CurrentDirectory, "LUEquipment.mdb"), false);
//this report is opened in Access and ready to print
oAccess.Visible = true;
oAccess.DoCmd.OpenReport("rptOwing", Access.AcView.acViewPreview, //View
System.Reflection.Missing.Value, //FilterName
System.Reflection.Missing.Value //WhereCondition
);
}
public bool Print_Access_Report(params string[] reports)
{
try {
//close access database
if (accessDB.IsConnected)
{
accessDB.Close();
}
Thread.Sleep(100);
//init access file
Access.Application oAccess = null;
// Start a new instance of Access for Automation:
oAccess = new Access.Application();
oAccess.Visible = false;
// Open a database in exclusive mode:
oAccess.OpenCurrentDatabase(Access_FileFullName);
//print out report
foreach (var report in reports)
{
// Select the Employees report in the database window: //3
oAccess.DoCmd.SelectObject(
Access.AcObjectType.acReport, //ObjectType
report, //ObjectName
true //InDatabaseWindow
);
// Print 1 copies of the selected object:
oAccess.DoCmd.PrintOut(
Access.AcPrintRange.acPrintAll, //PrintRange
System.Reflection.Missing.Value, //PageFrom
System.Reflection.Missing.Value, //PageTo
Access.AcPrintQuality.acHigh, //PrintQuality
1, //Copies
false //CollateCopies
);
}
//quit and release resource
oAccess.CloseCurrentDatabase();
oAccess.Quit();
Marshal.ReleaseComObject(oAccess);
return(true);
}
catch (Exception ex) {
System.Windows.MessageBox.Show(ex.ToString());
return(false);
}
}
private bool ExtractCodeFromAccess(ref Access.Application AccApp, string srcDir)
{
foreach (VBProject vbp in AccApp.VBE.VBProjects)
{
foreach (VBComponent vbc in vbp.VBComponents)
{
CreateTargetSourceFile(srcDir, vbp.Name, vbc);
}
}
return(true);
}
private void FileCreation()
{
if (!File.Exists(strAccessFilePath))
{
if (!new DirectoryInfo(strAccessFilePath).Exists)
{
new DirectoryInfo(new FileInfo(strAccessFilePath).DirectoryName).Create();
}
Access.Application _accessApp = new Access.Application();
_accessApp.Visible = false;
_accessApp.NewCurrentDatabase(strAccessFilePath);
_accessApp.CloseCurrentDatabase();
_accessApp.Quit(Access.AcQuitOption.acQuitSaveAll);
base.releaseObject(_accessApp);
base.ClearnGarbage();
}
else
{
string _conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + this.strAccessFilePath + "'";
using (OleDbConnection _connection = new OleDbConnection(_conn))
{
string[] restrictions = new string[4];
restrictions[2] = this.strAccessTableName;
_connection.Open();
DataTable dbTbl = _connection.GetSchema("Tables", restrictions);
if (dbTbl != null)
{
if (dbTbl.Rows.Count > 0)
{
dbTbl.Dispose();
dbTbl = null;
throw new Exception("table is already exists");
}
else
{
dbTbl.Dispose();
dbTbl = null;
}
}
}
}
}
static void Main(string[] args)
{
Access.Application accessApp = null;
//Access.DoCmd doCmdDisp = null;
Access.AllObjects allTblsDisp = null;
try
{
accessApp = new Access.Application();
accessApp.OpenCurrentDatabase(args[0]);
//doCmdDisp = accessApp.DoCmd;
allTblsDisp = accessApp.CurrentData.AllTables;
long tableCnt = allTblsDisp.Count;
System.Collections.IEnumerator tblEnumerator = allTblsDisp.GetEnumerator();
Access.AccessObject tableObj = null;
List <string> tblList = new List <string>();
while (tblEnumerator.MoveNext())
{
tableObj = (Access.AccessObject)tblEnumerator.Current;
tblList.Add(tableObj.Name);
}
int progressGauge = 0;
Stopwatch stopWatch = new Stopwatch();
foreach (string tblName in tblList)
{
//doCmdDisp.TransferText(Access.AcTextTransferType.acExportDelim, tmpObj, tblName, args[1] + tblName + ".csv");
accessApp.ExportXML(Access.AcExportXMLObjectType.acExportTable, tblName, args[1] + tblName + ".xml");
Console.Write("Convert MDB to XML... {0}%\r", progressGauge * 100 / tblList.Count + 1);
++progressGauge;
}
stopWatch.Stop();
Console.WriteLine(stopWatch.ElapsedMilliseconds.ToString() + "ms");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
accessApp.Quit(Access.AcQuitOption.acQuitSaveNone);
accessApp = null;
}
}
protected void InitApp()
{
this.AccApp = new AccessObj.Application();
this.IsAppOpened = true;
if (string.IsNullOrEmpty(StrPassword))
{
AccApp.OpenCurrentDatabase(StrFilePath, false);
}
else
{
AccApp.OpenCurrentDatabase(StrFilePath, false, StrPassword);
}
}
public void PrintReports()
{
Microsoft.Office.Interop.Access.Application objAccess = new Microsoft.Office.Interop.Access.Application();
objAccess.OpenCurrentDatabase(DBPath, false);
// Print 2 copies of the selected object:
objAccess.DoCmd.OpenReport("PickTicketAuto");
objAccess.DoCmd.OpenReport("PickTicketAuto");
objAccess.Visible = false;
objAccess.CloseCurrentDatabase();
System.Runtime.InteropServices.Marshal.ReleaseComObject(objAccess);
}
public void runAccessQuery()
{
Access.Application myAccess = new Access.Application();
myAccess.Visible = false;
myAccess.OpenCurrentDatabase(@"U:\Reserving\Legacy Process\ADS_Uploader.accdb");
myAccess.DoCmd.RunSQL(@"DELETE * FROM tblCombinedDataToUpload");
myAccess.DoCmd.OpenQuery(@"qryOutputData");
//myAccess.DoCmd.RunMacro("Test");
myAccess.Quit(Access.AcQuitOption.acQuitSaveNone);
}
protected void ClearObject()
{
if (this.AccApp != null)
{
if (this.IsAppOpened)
{
AccApp.Quit();
this.IsAppOpened = false;
}
ReleaseObj.Marshal.ReleaseComObject(AccApp);
this.AccApp = null;
}
this.ClearnGarbage();
}
public void PrintReports2(string strRefNumber)
{
Microsoft.Office.Interop.Access.Application objAccess = new Microsoft.Office.Interop.Access.Application();
objAccess.OpenCurrentDatabase(DBPath, false);
objAccess.DoCmd.OpenForm("PickTicketFinal", AcFormView.acNormal, Type.Missing, Type.Missing, AcFormOpenDataMode.acFormPropertySettings, AcWindowMode.acHidden, Type.Missing);
// Print 2 copies of the selected object:
objAccess.DoCmd.OpenReport("PickTicketFinal");
objAccess.DoCmd.OpenReport("PickTicketFinal");
objAccess.Visible = false;
objAccess.CloseCurrentDatabase();
System.Runtime.InteropServices.Marshal.ReleaseComObject(objAccess);
}
public void ConvertToDbFormat(string path)
{
var accApplication = new ACCESS.Application();
var pieces = path.Split('\\');
string directory = "";
foreach (var piece in pieces)
{
if (piece.Contains("."))
{
continue;
}
directory += piece;
directory += "\\";
}
if (File.Exists(path))
{
File.Delete(path);
}
accApplication.NewCurrentDatabase(path);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "T_Mass_Tags", FileName: directory + "tempMassTags.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "T_Mass_Tags_NET", FileName: directory + "tempMassTagsNet.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "T_Proteins", FileName: directory + "tempProteins.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "T_Mass_Tags_to_Protein_Map", FileName: directory + "tempMassTagToProteins.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "T_Analysis_Description", FileName: directory + "tempAnalysisDescription.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
TableName: "V_Filter_Set_Overview_Ex", FileName: directory + "tempFilterSet.txt", HasFieldNames: true);
accApplication.CloseCurrentDatabase();
accApplication.Quit();
File.Delete(directory + "tempMassTags.txt");
File.Delete(directory + "tempPeptides.txt");
File.Delete(directory + "tempModInfo.txt");
File.Delete(directory + "tempMassTagsNet.txt");
File.Delete(directory + "tempProteins.txt");
File.Delete(directory + "tempMassTagToProteins.txt");
File.Delete(directory + "tempAnalysisDescription.txt");
File.Delete(directory + "tempFilterSet.txt");
}
private void OpenForm(AccessObjectModel formToLoad, bool openInDesigner, MSAccess.Application application)
{
try
{
var formName = formToLoad.FullName ?? formToLoad.Name;
application.DoCmd.OpenForm(
formName,
openInDesigner ? MSAccess.AcFormView.acDesign : MSAccess.AcFormView.acNormal
);
var form = application.Forms[formName];
form.SetFocus();
RobotWin32.BringWindowToFront((IntPtr)form.Hwnd);
}
catch (Exception ex)
{
RobotMessageBox.Show(ex.Message);
}
}
/// <summary>
/// 根据Access版本构建连接字符串
/// </summary>
/// <param name="daPath"></param>
/// <returns></returns>
public static string GetAccessVersionConStr(string dbPath)
{
string constr = "";
Microsoft.Office.Interop.Access.Application excelApp = new Microsoft.Office.Interop.Access.Application();
string accessVersion = excelApp.Version;
switch (accessVersion)
{
case "4.0":
constr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}'", dbPath);
break;
default:
constr = string.Format(@"Provider=microsoft.ace.oledb.12.0;Data Source='{0}'", dbPath);
//constr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}'", daPath);
break;
}
return(constr);
}
public static bool AccessExist()
{
string constr = "";
try
{
Microsoft.Office.Interop.Access.Application excelApp = new Microsoft.Office.Interop.Access.Application();
string accessVersion = excelApp.Version;
if (accessVersion == "12.0" || accessVersion == "14.0" || accessVersion == "4.0")
{
return(true);
}
}
catch (Exception ex)
{
return(false);
}
return(false);
}
public RevitDBEditor(UIApplication uiapp, string dbfile)
{
try
{
m_app = uiapp;
doc = m_app.ActiveUIDocument.Document;
dbPath = dbfile;
oAccess = new Access.Application();
oAccess.OpenCurrentDatabase(dbPath, false);
daoDB = oAccess.CurrentDb();
oAccess.Visible = false;
ReadExcludeInst();
ReadFamilyData(); //Reading FamilyInfo table to collect pre-selected typeID for symboltoExclude
ReadSyncData(); //Reading Auto-Sync settings
CheckNewTypes(); //Check if there are new families added or removed for synchronization
}
catch (Exception ex)
{
MessageBox.Show("Failed to start RevidDBEditor: \n" + ex.Message);
CloseDatabase();
}
}
public IEnumerable <LcmsDataSet> Read(string path)
{
// Read in the data from the access database
// put it into a text file (?)
// Read the data from the text file into program
var accApplication = new ACCESS.Application();
var pathPieces = path.Split('\\');
string directory = "";
foreach (var piece in pathPieces)
{
if (piece.Contains("."))
{
continue;
}
directory += piece;
directory += "\\";
}
accApplication.OpenCurrentDatabase(path);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acExportDelim,
TableName: "AMT", FileName: directory + "outTempAMT.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acExportDelim,
TableName: "AMT_Proteins", FileName: directory + "outTempAMT_Proteins.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acExportDelim,
TableName: "AMT_to_Protein_Map", FileName: directory + "outTempAMT_to_Protein_Map.txt", HasFieldNames: true);
accApplication.CloseCurrentDatabase();
accApplication.Quit();
var priorDatasets = new List <LcmsDataSet>();
return(priorDatasets);
}
private static void DoCompactAndRepair(string currentPath, string dbName, string tempName, string repairedName, bool isTestingRun)
{
Access.Application oAccess = null;
try
{
string originalDbPath = Path.Combine(currentPath, dbName);
string tempOiginalDbPath = Path.Combine(currentPath, tempName);
string compactedRepairedDbPath = Path.Combine(currentPath, repairedName);
if (File.Exists(compactedRepairedDbPath) || File.Exists(tempOiginalDbPath))
{
throw new IOException(
string.Format("It appears that a previous compact and repair did not complete successfully for {0}.",
originalDbPath) +
" Notify Rich of the issue.");
}
if (isTestingRun)
{
Console.WriteLine();
Console.WriteLine("Testing Run - no actual compact and repair performed. {0}", originalDbPath);
}
else
{
Task taskCompactRepair = new Task(() =>
{
oAccess = new Access.Application();
oAccess.CompactRepair(originalDbPath, compactedRepairedDbPath, true);
System.Threading.Thread.Sleep(600);
});
taskCompactRepair.Start();
while (!taskCompactRepair.IsCompleted)
{
System.Threading.Thread.Sleep(200);
Console.Write(".");
}
Console.WriteLine();
taskCompactRepair.Wait();
if (!File.Exists(compactedRepairedDbPath))
{
throw new FileNotFoundException(
string.Format("Compact and repair failed to create the repaired database. {0}",
compactedRepairedDbPath));
}
File.Move(originalDbPath, tempOiginalDbPath);
File.Move(compactedRepairedDbPath, originalDbPath);
File.Delete(tempOiginalDbPath);
}
}
finally
{
try
{
if (oAccess != null)
{
oAccess.Quit();
}
}
catch (Exception)
{
Console.WriteLine("There may have been a problem doing compact and repair.");
}
}
}
private void Imprimer_Click(object sender, EventArgs e)
{
try{
string strFileName = "Palene_Gest_Vente.accdb";
string strAccReport = lstARReports.SelectedItem.ToString(); //Get Selected ListBox Item
objAccApp.Application app = new objAccApp.Application(); //Instantiate Access Application Object
//Open Database app.OpenCurrentDatabase(txtARSource.Text.Trim(), false, "");
app.OpenCurrentDatabase(strFileName, false, "");
app.Visible = false; //Do Not Show Access Window(s)
app.DoCmd.OpenReport(strAccReport, Microsoft.Office.Interop.Access.AcView.acViewPreview, Type.Missing, Type.Missing, objAccApp.AcWindowMode.acWindowNormal, Type.Missing); //Open Selected Report
// Print the document.
string formats = "dd_MM_yyyy-HH_mm_ss";
string ReportFileName ="C:/Users/" + Environment.UserName.ToString() + "/Documents/" + lstARReports.SelectedItem.ToString() + " ("+System.DateTime.Now.ToString(formats)+").pdf" ;
app.DoCmd.PrintOut(objAccApp.AcPrintRange.acPrintAll, Type.Missing, Type.Missing, objAccApp.AcPrintQuality.acHigh, Type.Missing, Type.Missing); //Print Report
//app.DoCmd.OutputTo(objAccApp.AcOutputObjectType.acOutputReport, Type.Missing, "PDF Format (*.pdf)", ReportFileName, Type.Missing, Type.Missing, objAccApp.AcPrintQuality.acHigh);
app.CloseCurrentDatabase(); //Close Database
app = null; //Release Resources
}
catch(NullReferenceException)
{
MessageBox.Show("Veuillez selectionner un etat à imprimer");
}
catch(Exception)
{
MessageBox.Show("Erreur d'impression !!! Veuillez ressayer SVP");
}
}
private void Chercher_Click(object sender, EventArgs e)
{
string strFileName = "C:/Users/" + Environment.UserName.ToString() + "/Documents/Palene_Gest_Vente.accdb"; //File Name To Open txtARSource.Text.Trim();
OpenFileDialog ofdAccReport = new OpenFileDialog();
ofdAccReport.FileName = strFileName;
//Create New Open File Dialog
//Set File Filter For OFD ofdAccReport.Filter = "Microsoft Access (*.accdb)|*.accdb)";
/* if (strFileName.Length > 0) //If File Selected
{
ofdAccReport.FileName = strFileName;
}
if (ofdAccReport.ShowDialog() == DialogResult.Cancel) //If Cancelled
{
return;
}
*/
lstARReports.Items.Clear(); //Erase Previous ListBox Items
objAccApp.Application app = new objAccApp.Application(); //Instantiate Access Object
app.Visible = false; //Do Not Display Access Window
app.OpenCurrentDatabase(ofdAccReport.FileName, false, ""); //Open Selected Access Database
string strReportSQL = "SELECT [Name] FROM MSysObjects WHERE Type = -32764"; //Search All Access Reports
dao.Database daoDB = (DAO.Database)app.CurrentDb(); //Open The Access Database
dao.Recordset rsReports = daoDB.OpenRecordset(strReportSQL, Type.Missing, Type.Missing, Type.Missing); //Find All Reports
while (!rsReports.EOF)
{
lstARReports.Items.Add(rsReports.Fields[0].Value); //Add Each Report Name To The ListBox
rsReports.MoveNext(); //Continue
}
rsReports.Close(); //Close All Objects
daoDB.Close();
app.CloseCurrentDatabase();
rsReports = null; //Release All Resources
daoDB = null;
app = null;
}
public TargetDatabase ReadDb(string path)
{
// Read in the data from the access database
// put it into a text file (?)
// Read the data from the text file into program
var accApplication = new ACCESS.Application();
var pathPieces = path.Split('\\');
string directory = "";
foreach (var piece in pathPieces)
{
if (piece.Contains("."))
{
continue;
}
directory += piece;
directory += "\\";
}
accApplication.OpenCurrentDatabase(path);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acExportDelim,
TableName: "AMT", FileName: directory + "outTempAMT.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acExportDelim,
TableName: "AMT_Proteins", FileName: directory + "outTempAMT_Proteins.txt", HasFieldNames: true);
accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acExportDelim,
TableName: "AMT_to_Protein_Map", FileName: directory + "outTempAMT_to_Protein_Map.txt", HasFieldNames: true);
accApplication.CloseCurrentDatabase();
accApplication.Quit();
// Put the data into its objects
// AMT stuff going in Consensus targets
// NET, MonoMass, Pred. Net, Peptide (Sequence with numeric mods), ID (can be crushed later)
// OBSERVED <-- number of times this peptide was seen in the AMT
// for <observed> times, add an evidence with the info? would make sense and would allow the stats calcs to be accurate
// Prot stuff going into ProteinInfo
// Prot name only thing important for MTDB, ID (can be crushed later)
// AMT map
// Link Consensus and Protein (ct[ct_id].protein.add(protein[prot_id]))
var consensusTargets = new Dictionary <int, ConsensusTarget>();
var proteins = new Dictionary <int, ProteinInformation>();
var ctReader = new StreamReader(directory + "outTempAMT.txt");
var protReader = new StreamReader(directory + "outTempAMT_Proteins.txt");
var mapReader = new StreamReader(directory + "outTempAMT_to_Protein_Map.txt");
// Read the headers for the files
ctReader.ReadLine();
protReader.ReadLine();
mapReader.ReadLine();
// Read the first "Data" lines from the files
var ctLine = ctReader.ReadLine();
var protLine = protReader.ReadLine();
var mapLine = mapReader.ReadLine();
while (ctLine != null)
{
var pieces = ctLine.Split(',');
var target = new ConsensusTarget
{
Id = Convert.ToInt32(pieces[0]),
TheoreticalMonoIsotopicMass = Convert.ToDouble(pieces[1]),
AverageNet = Convert.ToDouble(pieces[2]),
PredictedNet = Convert.ToDouble(pieces[3]),
EncodedNumericSequence = pieces[6]
};
var totalEvidences = Convert.ToInt32(pieces[4]);
var normScore = Convert.ToDouble(pieces[5]);
for (var evNum = 0; evNum < totalEvidences; evNum++)
{
var evidence = new Evidence
{
ObservedNet = target.AverageNet,
ObservedMonoisotopicMass = target.TheoreticalMonoIsotopicMass,
PredictedNet = target.PredictedNet,
NormalizedScore = normScore,
SeqWithNumericMods = target.EncodedNumericSequence,
Parent = target
};
target.Evidences.Add(evidence);
}
consensusTargets.Add(target.Id, target);
ctLine = ctReader.ReadLine();
}
while (protLine != null)
{
var pieces = protLine.Split(',');
var protein = new ProteinInformation
{
ProteinName = pieces[1]
};
proteins.Add(Convert.ToInt32(pieces[0]), protein);
protLine = protReader.ReadLine();
}
while (mapLine != null)
{
var pieces = mapLine.Split(',');
consensusTargets[Convert.ToInt32(pieces[0])].AddProtein(proteins[Convert.ToInt32(pieces[1])]);
mapLine = mapReader.ReadLine();
}
ctReader.Close();
protReader.Close();
mapReader.Close();
File.Delete(directory + "outTempAMT.txt");
File.Delete(directory + "outTempAMT_Proteins.txt");
File.Delete(directory + "outTempAMT_to_Protein_Map.txt");
var database = new TargetDatabase();
foreach (var target in consensusTargets)
{
database.AddConsensusTarget(target.Value);
}
database.Proteins = proteins.Values.ToList();
return(database);
}
public AccessApi(Access.Application application)
{
_officeApplication = application;
}