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 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;
}
}
protected void Save()
{
if (AccApp != null && this.IsAppOpened)
{
AccApp.Quit();
this.IsAppOpened = false;
}
this.ClearObject();
}
/// <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 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);
}
}
/// <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);
}
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);
}
}
public void CloseDatabase()
{
if (null != oAccess.CurrentDb()) { oAccess.CloseCurrentDatabase(); }
oAccess.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
GC.Collect();
foreach (Process pr in Process.GetProcessesByName("MSACCESS"))
{
pr.Kill();
}
}
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;
}
}
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;
}
}
}
}
}
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);
}
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");
}
protected void CloseApplication(TargetFileType tgtFileType)
{
switch (tgtFileType)
{
case TargetFileType.ACCESS:
// find orphaned property dialog
string pjtname = null;
foreach (VBProject vbp in AccApp.VBE.VBProjects)
{
pjtname = vbp.Name;
break;
}
var hwnd = FindWindowA(null, pjtname + " - Project Properties");
// Jpn support
if (hwnd == IntPtr.Zero)
{
hwnd = FindWindowA(null, pjtname + " - プロジェクト プロパティ");
}
// find cancel button
var hbtn = FindWindowExA(hwnd, IntPtr.Zero, null, "Cancel");
// Jpn support
if (hbtn == IntPtr.Zero)
{
hbtn = FindWindowExA(hwnd, IntPtr.Zero, null, "キャンセル");
}
if (hbtn != IntPtr.Zero)
{
SendMessage(hbtn, BM_CLICK, IntPtr.Zero, null);
}
if (AccApp != null)
{
AccApp.Quit(Access.AcQuitOption.acQuitSaveNone);
}
if (AccApp != null)
{
Marshal.ReleaseComObject(AccApp);
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
break;
}
}
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);
}
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);
}
/// <summary>
///
/// </summary>
/// <param name="report_name"></param>
/// <returns></returns>
public bool Print_Access_Report(string report_name)
{
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 preview a report named IMEI_SN_fPrint: //1
//oAccess.DoCmd.OpenReport(
// "IMEI_SN_fPrint", //ReportName
// Access.AcView.acViewNormal, //View
// System.Reflection.Missing.Value, //FilterName
// System.Reflection.Missing.Value //WhereCondition
// );
//// Print a report named IMEI_SN_fPrint: //2
//oAccess.DoCmd.OpenReport(
// report_name, //ReportName
// Access.AcView.acViewNormal, //View
// System.Reflection.Missing.Value, //FilterName
// System.Reflection.Missing.Value //WhereCondition
// );
// Select the Employees report in the database window: //3
oAccess.DoCmd.SelectObject(
Access.AcObjectType.acReport, //ObjectType
report_name, //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
);
oAccess.CloseCurrentDatabase();
oAccess.Quit();
Marshal.ReleaseComObject(oAccess);
return(true);
}
catch (Exception ex) {
System.Windows.MessageBox.Show(ex.ToString());
return(false);
}
}
