Office Systems Developer

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;
            }
        }

[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
        }

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

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);                
            }
        }    

/// <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);
            }
        }

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);
            }
        }

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);
            }
        }

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);
    }
}

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();
                }
            }
        }

// ----------------------------------------------------------------------------------------
        // Author:                        Joao Tito Livio
        // Company:                       MACL
        // Assembly version:              0.0.*
        // Date:                          21-04-2009
        // Time:                          23:55
        // Project Item Name:             M3Data.cs
        // Project Item Filename:         M3Data.cs
        // Project Item Kind:             Code
        // Class FullName:                Macl.M3Data
        // Class Name:                    M3Data
        // Class Kind Description:        Class
        // Class Kind Keyword:            class
        // Procedure FullName:            Macl.M3Data.ReturnRecordset
        // Procedure Name:                ReturnRecordset
        // Procedure Kind Description:    Function
        // Procedure Kind Keyword:        
        // Function Return Type Name:     Recordset
        // Function Return Type FullName: ADODB.Recordset
        // Function Return Type Alias:    Recordset
        // Purpose:                       Return a recordset from any OleDb provider
        // Parameters:
        //    - MyConnectionString (string)()   : Connection strin for the OleDb Conenction
        //    - MyTableOrSQL (string)()         : Can be a Table or a SQL statement
        //    - MyCursorType (CursorTypeEnum)() : http://msdn.microsoft.com/en-us/library/ms677593(VS.85).aspx
        // ----------------------------------------------------------------------------------------
        public ADODB.Recordset ReturnRecordset(String MyConnectionString, String MyTableOrSQL,
                                               ADODB.CursorTypeEnum MyCursorType)
        {
            ADODB.Connection cnn = new ADODB.Connection();
            ADODB.Recordset rs = new ADODB.Recordset();
            
            try
            {
                cnn.ConnectionString = MyConnectionString;                
                cnn.Open(null, null, null, 0);
                //}                
                rs.Open(MyTableOrSQL, cnn, MyCursorType, ADODB.LockTypeEnum.adLockOptimistic, -1);
                return rs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <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);
            }
        }