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
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);
}
}
Great News
SharePoint Designer 2007 is now FREE!
Get it here
NOW MACL IS FREE AND IS IN CODEPLEX
My recent project with Libraries to use in Access 2007
Project Description
Improved Microsoft Access Class Libraries *DLL's* with utilities and Data Access.
---------------------------------------------------------------------------------------------
C#, Framework 2.0, 3.5
(Interop API Extensions)
The MACL Interop API Extensions tool uses features found in the .NET Framework 2.0 for now in the Alpha Version and its to be oriented to Framework 3.5 in the Beta Version. MACL Wraps the Access object model providing a more productive environment for the VBA developer. Specifically, it employs extension methods, functions, properties and a most effective data management relative to OLEDB Providers, like returning a single Recordset from any OLEDB Provider using ADODB . It is not a complete managed API for Access, it will reach there, but is designed to augment the raw object model in useful ways.
The Access Interop API Extensions, with its simplified and strongly-typed API, allows VBA developers to be as productive in this context as VBA developers.
The code was original developed in C# 3.0 and is divided in different classes, all classes are in constant update.
Forum Support
http://forum.macl.menos3.netProject Blog (Code Samples) http://macl.menos3.netOnline Documentation: http://www.menos3.net/pp
Today i want to talk about my experience with the DevExpress Components. I use it for about 2 years and the results are amazing, bellow is some samples about my last application. I can localize all components to Portuguese that is great!
JUST SHIFT TO: http://www.devexpress.com/Products/index.xml
AMAZING SEARCH
GREAT GRID CONTROL
MORE
Today, software consumers demand more than ever before from developers. The pressure to deliver more features with higher quality in less time has never been greater. Developer Express engineers components and IDE tools for Visual Studio® .NET to help developers and managers meet these increased demands for quality and functionality, by boosting productivity while eliminating the repetition that erodes precious creativity.