Delphi中的Access技巧集

   DELPHI中操作ACCESS數據庫(建立mdb文件壓縮數據庫) 
以下代碼在WINKDMDAC下測試通過 
編譯好的程序在WIN第二版無ACCESS環境下運行成功

　　//在之前uses ComObjActiveX
//聲明連接字符串
Const
SConnectionString = &#;Provider=MicrosoftJetOLEDB;Data Source=%s;&#;
+&#;Jet OLEDB:Database Password=%s;&#;;

　　//=============================================================================
// Procedure: GetTempPathFileName
// Author : ysai
// Date :
// Arguments: (None)
// Result : string
//=============================================================================
function GetTempPathFileName():string;
//取得臨時文件名
var
SPathSfile&:array [] of char;
begin
GetTempPath(SPath);
GetTempFileName(SPath&#;~SM&#;SFile);
result:=SFile;
DeleteFile(PChar(result));
end;

　　//=============================================================================
// Procedure: CreateAccessFile
// Author : ysai
// Date :
// Arguments: FileName:String;PassWord:string=&#;&#;
// Result : boolean
//=============================================================================
function CreateAccessFile(FileName:String;PassWord:string=&#;&#;):boolean;
//建立Access文件如果文件存在則失敗
var
STempFileName:string;
vCatalog:OleVariant;
begin
STempFileName:=GetTempPathFileName;
try
vCatalog:=CreateOleObject(&#;ADOXCatalog&#;);
vCatalogCreate(format(SConnectionString[STempFileNamePassWord]));
result:=CopyFile(PChar(STempFileName)PChar(FileName)True);
DeleteFile(STempFileName);
except
result:=false;
end;
end;

　　//=============================================================================
// Procedure: CompactDatabase
// Author : ysai
// Date :
// Arguments: AFileNameAPassWord:string
// Result : boolean
//=============================================================================
function CompactDatabase(AFileNameAPassWord:string):boolean;
//壓縮與修復數據庫覆蓋源文件
var
STempFileName:string;
vJE:OleVariant;
begin
STempFileName:=GetTempPathFileName;
try
vJE:=CreateOleObject(&#;JROJetEngine&#;);
vJECompactDatabase(format(SConnectionString[AFileNameAPassWord])
format(SConnectionString[STempFileNameAPassWord]));
result:=CopyFile(PChar(STempFileName)PChar(AFileName)false);
DeleteFile(STempFileName);
except
result:=false;
end;
end;

　　//=============================================================================
// Procedure: ChangeDatabasePassword
// Author : ysai
// Date :
// Arguments: AFileNameAOldPassWordANewPassWord:string
// Result : boolean
//=============================================================================
function ChangeDatabasePassword(AFileNameAOldPassWordANewPassWord:string):boolean;
//修改ACCESS數據庫密碼
var
STempFileName:string;
vJE:OleVariant;
begin
STempFileName:=GetTempPathFileName;
try
vJE:=CreateOleObject(&#;JROJetEngine&#;);
vJECompactDatabase(format(SConnectionString[AFileNameAOldPassWord])
format(SConnectionString[STempFileNameANewPassWord]));
result:=CopyFile(PChar(STempFileName)PChar(AFileName)false);
DeleteFile(STempFileName);
except
result:=false;
end;
end;

　　ACCESS中使用SQL語句應注意的地方及幾點技巧
以下SQL語句在ACCESS XP的查詢中測試通過
建表:
Create Table Tab (
ID Counter
Name string
Age integer
[Date] DateTime);
技巧:
自增字段用 Counter 聲明
字段名為關鍵字的字段用方括號[]括起來數字作為字段名也可行

　　建立索引:
下面的語句在Tab的Date列上建立可重復索引
Create Index iDate ON Tab ([Date]);
完成后ACCESS中字段Date索引屬性顯示為 有(有重復)
下面的語句在Tab的Name列上建立不可重復索引
Create Unique Index iName ON Tab (Name);
完成后ACCESS中字段    

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈