Joao Livio { MIX ~ UP on USA}

December 2009 - Posts

VSTO - Detect if Access Database is Open

How to know if a access database is open within VSTO

VB.NET

Private Shared _oAccess As Microsoft.Office.Interop.Access.Application
''' <summary>
'''
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function IsAccessDatabaseOpen() As Boolean
Try
_oAccess = CType(Marshal.GetActiveObject("Access.Application"), Microsoft.Office.Interop.Access.Application)
Select Case _oAccess.CurrentProject.Connection.ToString
Case Nothing
Return False
Case Else
Return True
End Select
Catch ex As Exception
Thrown;
Finally
Marshal.ReleaseComObject(_oAccess)
End Try
End Function
 
C#
 
Microsoft.Office.Interop.Access.Application _oAccess;


// '' <summary>
// ''
// '' </summary>
// '' <returns></returns>
// '' <remarks></remarks>
public static bool IsAccessDatabaseOpen() {
try {
_oAccess = ((Microsoft.Office.Interop.Access.Application)
(Marshal.GetActiveObject("Access.Application")));
switch (_oAccess.CurrentProject.Connection.ToString) {
case null:
return false;
break;
default:
return true;
break;
}
}
catch (Exception ex) {
Thrown;
}
finally {
Marshal.ReleaseComObject(_oAccess);
}
}



Simulating DAvg() equivalent to Microsoft Access

I was writing a ClassLibrary to COM Interop and this one came up. This was original whiten to use with JET (VBA and VB6)

C#

public object DAvg(string connectionString, string expression, string domain, string criteria)
{

var strSQL = "Select " + expression + " As ThisValue" +
" From " + domain +
" Where " + criteria;
using (var con = new OleDbConnection(connectionString))
{
using (var da = new OleDbDataAdapter(strSQL, con))
{
using (var dt = new DataTable())
{
da.Fill(dt);
int i;
double sum = 0;
for (i = 0; (i <= dt.Rows.Count -1); i++)
{
sum = Convert.ToDouble(dt.Rows[i].ItemArray[0].ToString()) + sum;
}
return sum / i;
}
}
}
}
 
VB.NET
 
Public Function DAvg(ByVal connectionString As String, ByVal expression As String, ByVal domain As String, ByVal criteria As String) As Object

Dim strSQL = "Select " + expression + " As ThisValue" + _
" From " + domain + _
" Where " + criteria
Using con = New OleDbConnection(connectionString)
Using da = New OleDbDataAdapter(strSQL, con)
Using dt = New DataTable()
da.Fill(dt)
Dim i As Integer
Dim sum As Double = 0
i = 0
While (i <= dt.Rows.Count - 1)
sum = Convert.ToDouble(dt.Rows(i).ItemArray(0).ToString()) + sum
i += 1
End While
Return sum / i
End Using
End Using
End Using
End Function