//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);
}
}
public IEnumerable <SearchResultModel> SearchFile(string path, string searchTerm)
{
if (application.CurrentDb() != null)
{
application.CloseCurrentDatabase();
}
// deze regex split tekst op per lijn, zodat we lijnnummer van zoekresultaat kunnen bepalen
var regex = new Regex($".*\n?");
application.OpenCurrentDatabase(path);
foreach (VBProject project in application.VBE.VBProjects)
{
foreach (VBComponent component in project.VBComponents)
{
var lineCount = component.CodeModule.CountOfLines;
for (int i = 1; i < lineCount; i++)
{
var content = component.CodeModule.Lines[i, 1];
if (content.ToLower().Contains(searchTerm.ToLower()))
{
yield return(new SearchResultModel(path, component.Name, i, content));
}
}
}
}
foreach (AccessObject queryObject in application.CurrentData.AllQueries)
{
QueryDef query = application.CurrentDb().OpenQueryDef(queryObject.Name);
if (regex.IsMatch(query.SQL))
{
var matches = regex.Matches(query.SQL);
for (int i = 0; i < matches.Count; i++)
{
if (matches[i].Value.ToLower().Contains(searchTerm.ToLower()))
{
yield return(new SearchResultModel(path, "Query: " + query.Name, i + 1, matches[i].Value.Trim('\n', '\r')));
}
}
}
}
application.CloseCurrentDatabase();
}
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();
}
}
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();
}
}
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;
}
