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中字段    

生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠