public string RepairAccess(string mdbPath)
{
string strResult = "";
//声明临时数据库的名称
string temp = DateTime.Now.Year.ToString();
temp += DateTime.Now.Month.ToString();
temp += DateTime.Now.Day.ToString();
temp += DateTime.Now.Hour.ToString();
temp += DateTime.Now.Minute.ToString();
temp += DateTime.Now.Second.ToString() + ".accdb";
temp = mdbPath.Substring(0, mdbPath.LastIndexOf("\\") + 1) + temp;
string strlock = mdbPath.Substring(0, mdbPath.LastIndexOf("\\") + 1) + "TransferLock";
string sourceDbSpec = mdbPath;
string destinationDbSpec = temp;
// Required COM reference for project:
// Microsoft Office 14.0 Access Database Engine Object Library
var dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();
try
{
File.Create(strlock).Dispose();
File.WriteAllText(strlock, GetComputerName());
dbe.CompactDatabase(sourceDbSpec, destinationDbSpec);
File.Delete(mdbPath);
File.Copy(destinationDbSpec, mdbPath, true);
strResult = "修复成功!";
}
catch (Exception e)
{
strResult = "Error: " + e.Message;
}
File.Delete(strlock);
return(strResult);
}
/*
* public Boolean dbOpen(string strFilePath)
* {
* m_Provider = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strFilePath + ";Jet OLEDB:Database Password ="******"; ";
* try
* {
* System.IO.FileInfo dbFile = new System.IO.FileInfo(strFilePath);
* if (!dbFile.Exists) m_dbOpen = false;
* else
* {
* m_ObjCon.Open();
* m_dbOpen = true;
* }
* }
* catch (Exception e)
* {
* m_dbOpen = false;
* e.ToString();
* }
* return m_dbOpen;
* }
*/
public Boolean dbOpen(string strFilePath)
{
Boolean bCreate = false;
m_Provider = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strFilePath + ";Jet OLEDB:Database Password ="******"; ";
try
{
System.IO.FileInfo dbFile = new System.IO.FileInfo(strFilePath);
if (!dbFile.Exists)
{
//// DB 파일이름으로 .accdb 만들기.
Catalog myCatalog = new Catalog();
//string strProvider = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strFilePath + ";Jet OLEDB:Database Password ="******"; ";
//string strProvider = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFilePath + ";Jet OLEDB:Database Password ="******"; ";
try
{
myCatalog.Create(m_Provider);
bCreate = true;
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Make AccessDB Error");
Console.WriteLine(ex.Message);
return(false);
}
}
else
{
//로컬 DB 데이터 압축. 데이터 압축 해주지 않으면 로컬 DB 파일의 사이즈가 계속 커지게 된다.
//Table 을 삭제해도 로컬 DB 내에 복구를 위한 데이터는 남아있다. 데이터 압축을 해주면 복구용 데이터를 모두 삭제한다.
try {
//임시파일
string strTempFilePath = "C:/temp.accdb";
if (System.IO.File.Exists(strTempFilePath))
{
System.IO.File.Delete(strTempFilePath);
}
//MDB 파일 데이터 압축. JRO 라이브러리 로드가 필요하다.
//JRO.JetEngine ddd = new JRO.JetEngine();
//string temp1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFilePath + ";Jet OLEDB:Database Password ="******"; ";
//string temp2 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strTempFilePath + ";Jet OLEDB:Database Password ="******"; ";
//ddd.CompactDatabase(temp1, temp2);
//accdb 파일 데이터 압축
Microsoft.Office.Interop.Access.Dao.DBEngine objDbEngine = new Microsoft.Office.Interop.Access.Dao.DBEngine();
objDbEngine.CompactDatabase(strFilePath, strTempFilePath, null, null, ";pwd=" + m_DBPassword);//데이터 압축
if (System.IO.File.Exists(strTempFilePath))
{
System.IO.File.Delete(strFilePath);
System.IO.File.Move(strTempFilePath, strFilePath);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
;
//mdb 파일
//m_ObjCon.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Jet OLEDB:Database Password ="******"; ";
//accdb 파일
//m_ObjCon.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ strFilePath+ ";Jet OLEDB:Database Password ="******";";
//+ "Persist Security Info=False;"
//+ "Database Password=gtf_stfm;";
m_ObjCon.Open();
m_dbOpen = true;
if (bCreate)
{
if (!createBasicTable())
{
m_ObjCon.Close();
m_ObjCon = null;
if (System.IO.File.Exists(strFilePath))
{
System.IO.File.Delete(strFilePath);
}
return(false);
}
}
else
{
// table data 삭제
DeleteTableData("REFUNDSLIP");
DeleteTableData("SALES_GOODS");
DeleteTableData("SLIP_PRINT_DOCS");
DeleteTableData("REFUND_SLIP_SIGN");
}
}
catch (Exception e)
{
m_dbOpen = false;
e.ToString();
}
return(m_dbOpen);
}
private void buttonCompactDatabase_Click(object sender, EventArgs e)
{
//
// 参考
// Microsoft Access Compact and Repair using C# .accdb files - Stack Overflow
// http://stackoverflow.com/questions/29201611/microsoft-access-compact-and-repair-using-c-sharp-accdb-files
//
// "プロジェクト"→"参照の追加"で参照マネージャーを表示
// 次に"COM"→"Microsoft Office 14.0 Access Database Engine Object Library"を選択し
// 参照を追加するとMicrosoft.Office.Interop.Access.Dao.DBEngineを使えるようになる
//
var dbengine = new Microsoft.Office.Interop.Access.Dao.DBEngine();
var src = AccdbFilePath();
var dst = NewAccdbFilePath();
var bak = BackupAccdbFilePath();
if (File.Exists(dst))
{
File.Delete(dst);
}
dbengine.CompactDatabase(src, dst);
if (File.Exists(bak))
{
File.Delete(bak);
}
File.Move(src, bak);
File.Move(dst, src);
Console.WriteLine("buttonCompactDatabase_Click");
}
