Office Systems Developer : VSTO

Simulating DAvg() equivalent to Microsoft Access

Joao Livio — Tue, 15 Dec 2009 13:53:18 GMT

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

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

Visual Studio Class Library para o Microsoft Access – C# (Parte1)

Joao Livio — Wed, 04 Nov 2009 11:35:00 GMT

Nesta série de vamos compreender como criar, gerir e implementar um Biblioteca (Class Library) para o Microsoft Access. Vamos utilizar o Visual Studio 2008, C#, Visual Basic e o Microsoft Access 2007.

Estes exemplos provêm da Class Library que criei este ano e que publiquei no Codeplex > aqui

Porque implementar uma Biblioteca de Classes no Microsoft Access?

Se implementarmos uma Classe e expormos a mesma, estamos a permitir que o Microsoft Access por si possa utilizar os seus recursos , ou seja, podemos utilizar as facilidades do Microsoft Framework no Código VBA, bastando para tal adicionar uma referência para o DLL. Podemos também utiliza-la em outro Projecto Visual Studio como por exemplo um Add-in.

Máquina cliente Requisitos

Microsoft Access 2007

Microsoft Framework 2.0 ou 3.5

Vamos necessitar das seguintes referências:

Microsoft Office 12.0 Object Library

Interop.Microsoft.Office.Interop.Access

No Microsoft Access

Ukase Add-ins

O Básico

Criar A Biblioteca
Configurações iniciais
Definições de Visibilidade COM
A Primeira Propriedade
Implementação COM

Código

A Classe

[ComVisible(true)]
[Guid("82382232-F1D7-4048-A1E0-F2879BCA0610")]
[ClassInterface(ClassInterfaceType.None)]
[ProgId("M3CommonFunctions")]
public class M3CommonFunctions : IM3CommonFunctions

Propriedade

Assembly asm = Assembly.GetExecutingAssembly();

     public Version GetMACLVersion
     {   
         get {  return asm.GetName().Version; }
     }

Interface

[Guid("4ECAAC7B-711C-4ac0-BBFC-58C4E3E8EE56")]
   [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
   public interface IM3CommonFunctions
   {
       [DispId(1)]
       Version GetMACLVersion  { get; }
   }

VISUAL BASIC

<ComClass(Namespace_Comuns.ClassId, Namespace_Comuns.InterfaceId, Namespace_Comuns.EventsId)> _
Public Class Namespace_Comuns

    Private m_VersaoBiblioteca As String

#Region "COM GUIDs"
    ' These  GUIDs provide the COM identity for this class 
    ' and its COM interfaces. If you change them, existing 
    ' clients will no longer be able to access the class.
    Public Const ClassId As String = "6b6c0965-e6a5-4089-afbb-0ec787efe024"
    Public Const InterfaceId As String = "d8c53404-58b4-4f28-9d2f-4912f8cb6723"
    Public Const EventsId As String = "165be10c-a5a6-4d57-ac49-0ad7a8bd1cd5"
#End Region

    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Public Sub New()
        MyBase.New()
    End Sub

    Public ReadOnly Property VersaoBiblioteca() As String
        Get
            m_VersaoBiblioteca = My.Application.Info.Version.ToString
            Return m_VersaoBiblioteca
        End Get
    End Property
End Class

VBA

Private Sub Command0_Click()
    Dim obj As New Ukase.M3CommonFunctions
    MsgBox obj.GetMACLVersion
End Sub

Screencast

Get Access ADOX Synonymous from Framework DataColumn

Joao Livio — Wed, 29 Apr 2009 20:55:43 GMT

static ADOX.DataTypeEnum GetAccessFormats(DataColumn col)
        {
            switch (col.DataType.Name)
            {
                case "Boolean":
                    return ADOX.DataTypeEnum.adBoolean;
                case "Byte":
                    return ADOX.DataTypeEnum.adUnsignedTinyInt;
                case "Char":
                    return ADOX.DataTypeEnum.adVarWChar;
                case "DateTime":
                    return ADOX.DataTypeEnum.adDate;
                case "Decimal":
                    return ADOX.DataTypeEnum.adDecimal;
                case "Double":
                    return ADOX.DataTypeEnum.adDouble;
                case "Int16":
                    return ADOX.DataTypeEnum.adSmallInt;
                case "Int32":
                    return ADOX.DataTypeEnum.adInteger;
                case "Int64":
                    return ADOX.DataTypeEnum.adInteger;
                case "SByte":
                    return ADOX.DataTypeEnum.adUnsignedSmallInt;
                case "Single":
                    return ADOX.DataTypeEnum.adSingle;
                case "String":
                    return ADOX.DataTypeEnum.adVarWChar;
                case "TimeStamp":
                    return ADOX.DataTypeEnum.adGUID;
                default:
                    return ADOX.DataTypeEnum.adUserDefined;
            }
        }

You can also ENUM the options

[Flags]
        public enum ADOXFields
        {
            AccessText = DataTypeEnum.adVarWChar,
            AccessMemo = DataTypeEnum.adLongVarWChar,
            AccessNumericByte = DataTypeEnum.adUnsignedTinyInt,
            AccessNumericInteger  = DataTypeEnum.adSmallInt,
            AccessNumericLongInteger = DataTypeEnum.adInteger,
            AccessNumericSinglePrecision = DataTypeEnum.adSingle,
            AccessNumericDoublePrecision = DataTypeEnum.adDouble,
            AccessNumericReplicatieId = DataTypeEnum.adGUID,
            AccessNumericDecimal = DataTypeEnum.adNumeric,
            AccessDateTime = DataTypeEnum.adDate,
            AccessCurrency = DataTypeEnum.adCurrency,
            AccessAutoNumber = DataTypeEnum.adInteger,
            AccessYesNo = DataTypeEnum.adBoolean,
            AccessHyperLink = DataTypeEnum.adLongVarWChar
        }

VB.NET

Public Shared Function GetAccessFormats(ByVal col As DataColumn) As ADOX.DataTypeEnum
        Select Case (col.DataType.Name)
            Case "Boolean"
                Return ADOX.DataTypeEnum.adBoolean
            Case "Byte"
                Return ADOX.DataTypeEnum.adUnsignedTinyInt
            Case "Char"
                Return ADOX.DataTypeEnum.adVarWChar
            Case "DateTime"
                Return ADOX.DataTypeEnum.adDate
            Case "Decimal"
                Return ADOX.DataTypeEnum.adDecimal
            Case "Double"
                Return ADOX.DataTypeEnum.adDouble
            Case "Int16"
                Return ADOX.DataTypeEnum.adSmallInt
            Case "Int32"
                Return ADOX.DataTypeEnum.adInteger
            Case "Int64"
                Return ADOX.DataTypeEnum.adInteger
            Case "SByte"
                Return ADOX.DataTypeEnum.adUnsignedSmallInt
            Case "Single"
                Return ADOX.DataTypeEnum.adSingle
            Case "String"
                Return ADOX.DataTypeEnum.adVarWChar
            Case "TimeStamp"
                Return ADOX.DataTypeEnum.adGUID
            Case Else
                Return ADOX.DataTypeEnum.adUserDefined
        End Select
    End Function

VSTO (Access) – Convert a Field to Upper, Lower, Proper

Joao Livio — Wed, 22 Apr 2009 21:54:32 GMT

public string ConvertFieldToUpper(string MyTable, string MyField)
        {
            Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
            try
            {
                string strSQL;
                strSQL = String.Format("UPDATE {0} SET {1} = UCase([{1}])", MyTable, MyField);

                oAccess.DoCmd.RunSQL(strSQL.ToString(),null);
                return "OK";
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }


        public string ConvertFieldToLower(string MyTable, string MyField)
        {
            Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
            try
            {
                string strSQL;
                strSQL = String.Format("UPDATE {0} SET {1} = LCase([{1}])", MyTable, MyField);

                oAccess.DoCmd.RunSQL(strSQL.ToString(), null);
                return "OK";
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }


        public string ConvertFieldToProper(string MyTable, string MyField)
        {
            Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
            try
            {
                string strSQL;
                strSQL = String.Format("UPDATE {0} SET {1} = STRCONV([{1},3])", MyTable, MyField);

                oAccess.DoCmd.RunSQL(strSQL.ToString(), null);
                return "OK";
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);                
            }
        }

VSTO (Access) a Simple Database initialization

Joao Livio — Wed, 22 Apr 2009 21:50:49 GMT

/// <summary>
        /// 
        /// </summary>
        /// <param name="hideNavigationPane"></param>
        /// <param name="myUIName"></param>
        /// <param name="myUIXML"></param>
        /// <returns>True/False</returns>
        public bool InitializeDatabase(bool hideNavigationPane, 
            string myUIName, string myUIXML)
        {   
            Access.Application oAccess = ((Access.Application)
                   (Marshal.GetActiveObject("Access.Application")));
            try
            {                   
                oAccess.SetOption("DesignWithData", false);                
                oAccess.SetOption("Perform Name AutoCorrect", false);
                oAccess.SetOption("Track Name AutoCorrect Info", false);
                oAccess.SetOption("Auto Compact", false);                                
                oAccess.SetOption("Confirm Record Changes", false);
                oAccess.SetOption("Confirm Document Deletions", false);
                oAccess.SetOption("Confirm Action Queries", false);

                oAccess.LoadCustomUI(myUIName, myUIXML);

                if (hideNavigationPane == true)
                {
                    oAccess.DoCmd.RunCommand(Access.AcCommand.acCmdWindowHide);
                }                
                return true;                
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

VSTO (Access) – Convert a Report to XPS

Joao Livio — Wed, 22 Apr 2009 21:47:34 GMT

public bool ConvertReportToXPS(string myReport, string XPSFileName)
        {
            Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
            try
            {
                oAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport,
                    myReport, Access.Constants.acFormatXPS, XPSFileName,
                    System.Type.Missing, System.Type.Missing, 
                        System.Type.Missing);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

VSTO (Access) Detect if tables are similar

Joao Livio — Wed, 22 Apr 2009 21:42:24 GMT

public bool TablesAreEqual(string MyTableSource, string 
            MyTableToCompare, string MyIndexField)
        {                          
            try
            {
                OleDbConnection cnn;
                using (DataSet ds = new DataSet())
                {
                    OleDbDataAdapter da;
                    string cs = 
                        oAccess.CurrentProject.Connection.ToString();
                    string strSQL = 
                        String.Format("SELECT * FROM {0} LEFT JOIN {1} ON {0}.{2} = {1}.{2}WHERE {1}.{2} Is Null"
                        , MyTableSource, MyTableToCompare, MyIndexField);
                    cnn = new OleDbConnection(cs);
                    da = new OleDbDataAdapter(strSQL, cnn);
                    da.Fill(ds);
                    if (ds.Tables[0].Rows.Count <= 0)
                        return true;
                    else
                        return false;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

VSTO (Access) How to Backup the Current project?

Joao Livio — Wed, 22 Apr 2009 21:40:19 GMT

Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
// ----------------------------------------------------------------------------------------
// Author:                        Joao Tito Livio
// Company:                       MACL
// Assembly version:              0.0.*
// Date:                          21-04-2009
// Time:                          23:47
// Project Item Name:             M3DatabaseUtilities.cs
// Project Item Filename:         M3DatabaseUtilities.cs
// Project Item Kind:             Code
// Class FullName:                Macl.M3DatabaseUtilities
// Class Name:                    M3DatabaseUtilities
// Class Kind Description:        Class
// Class Kind Keyword:            class
// Procedure FullName:            Macl.M3DatabaseUtilities.BackupCurrentDatabase
// Procedure Name:                BackupCurrentDatabase
// Procedure Kind Description:    Function
// Procedure Kind Keyword:        
// Function Return Type Name:     Boolean
// Function Return Type FullName: System.Boolean
// Function Return Type Alias:    bool
// Purpose:                       Backup the Current database, CAN BE IN USE
// Parameters:
//    - MyDestinationpathAndFile (string)() :  Path and File (c:\backup.accdb")
// ----------------------------------------------------------------------------------------
public bool BackupCurrentDatabase(string MyDestinationpathAndFile)
{               
    try
    {
       Access.Application oAccess = ((Access.Application)
        (Marshal.GetActiveObject("Access.Application")));

        File.Copy(oAccess.CurrentProject.FullName, 
            MyDestinationpathAndFile);
        return true;                
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        Marshal.ReleaseComObject(oAccess);
    }
}

VSTO (Access) How to execute a SQL Statement from a File?

Joao Livio — Wed, 22 Apr 2009 21:37:21 GMT

public bool ExecuteSqlFromFile(String MyConnectionString, String MyTextFilePath)
        {
            using (OleDbConnection sqlConnection = new OleDbConnection(MyConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                try
                {
                    using (StreamReader fh = new StreamReader(MyTextFilePath))
                    {
                        String MyText;
                        string s;
                        while ((s = fh.ReadLine()) != null)
                            MyText = s;
                        fh.Close();
                        cmd.CommandText = s;
                    }
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = sqlConnection;
                    sqlConnection.Open();
                    cmd.ExecuteNonQuery();
                    sqlConnection.Close();
                    return true;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                    cmd.Dispose();
                }
            }
        }

VSTO (Access) how to create a Table with fields in a Access Database (ADOX and JET SQL)

Joao Livio — Wed, 22 Apr 2009 15:57:00 GMT


        /// <summary>
        /// 
        /// </summary>
        /// <param name="MyPath"></param>
        /// <returns>True/False</returns>
        public bool GenerateLogDatabase(string myPath)
        {   
            CatalogClass cat = new CatalogClass();
            string strSQL;
            string cs;

            try
            {
                cs = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                     "Data Source=" + myPath + ";" +
                     "Jet OLEDB:Engine Type=5";

                strSQL = "CREATE TABLE Issues (mID AUTOINCREMENT, mUser TEXT(100) NOT NULL " +
                         ", mError TEXT(100) NOT NULL, " +
                         "mDescription TEXT(100) NOT NULL, mDate DATETIME NOT NULL)";

                cat.Create(cs);

                using (OleDbConnection cnn = new OleDbConnection(cs))
                {
                    OleDbCommand cmd = new OleDbCommand();
                    try
                    {
                        cmd.CommandText = strSQL;
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = cnn;
                        cnn.Open();
                        cmd.ExecuteNonQuery();
                        cnn.Close();
                        return true;
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                    finally
                    {
                        cnn.Close();
                        cmd.Dispose();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {   
                Marshal.FinalReleaseComObject(cat);
            }
        }

VBA and Office 14, just to clarify

Joao Livio — Sat, 19 Jan 2008 22:42:00 GMT

According with Kevin Boske, Microsoft VSTA Program Manager, "there's been some discussion on this thread on Slashdot, (updated) which started from this article in The Register UK. To be clear, Microsoft is not replacing VBA with VSTA or VSTO in Office for Windows. (though VSTA and VSTO are still important developer tools for Office 14), VBA is here to stay, as was announced some time back by Steven Sinofsky. The Excel and Access Blogs both have posts covering this from the Office perspective."