ACCESS技巧集(DELPHI AND SQL)[1]

1.delphi中操作access数据库(建立.mdb文件,压缩数据库)
以下代码在win2k,d6,mdac2.6下测试通过,
编译好的程序在win98第二版无access环境下运行成功.
//在之前uses comobj,activex
//声明连接字符串
const
  sconnectionstring       = 'provider=microsoft.jet.oledb.4.0;data source=%s;'
                                +'jet oledb:database password=%s;';

//=============================================================================
// procedure: gettemppathfilename
// author   : ysai
// date     : 2003-01-27
// arguments: (none)
// result   : string
//=============================================================================
function gettemppathfilename():string;
//取得临时文件名
var
  spath,sfile:array [0..254] of char;
begin
  gettemppath(254,spath);
  gettempfilename(spath,'~sm',0,sfile);
  result:=sfile;
  deletefile(pchar(result));
end;

//=============================================================================
// procedure: createaccessfile
// author   : ysai
// date     : 2003-01-27
// 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('adox.catalog');
    vcatalog.create(format(sconnectionstring,[stempfilename,password]));
    result:=copyfile(pchar(stempfilename),pchar(filename),true);
    deletefile(stempfilename);
  except
    result:=false;
  end;
end;

//=============================================================================
// procedure: compactdatabase
// author   : ysai
// date     : 2003-01-27
// arguments: afilename,apassword:string
// result   : boolean
//=============================================================================
function compactdatabase(afilename,apassword:string):boolean;
//压缩与修复数据库,覆盖源文件
var
  stempfilename:string;
  vje:olevariant;
begin
  stempfilename:=gettemppathfilename;
  try
    vje:=createoleobject('jro.jetengine');
    vje.compactdatabase(format(sconnectionstring,[afilename,apassword]),
        format(sconnectionstring,[stempfilename,apassword]));
    result:=copyfile(pchar(stempfilename),pchar(afilename),false);
    deletefile(stempfilename);
  except
    result:=false;
  end;
end;

//=============================================================================
// procedure: changedatabasepassword
// author   : ysai
// date     : 2003-01-27
// arguments: afilename,aoldpassword,anewpassword:string
// result   : boolean
//=============================================================================
function changedatabasepassword(afilename,aoldpassword,anewpassword:string):boolean;
//压缩数据库并修改access数据库密码
var
  stempfilename:string;
  vje:olevariant;
begin
  stempfilename:=gettemppathfilename;
  try
    vje:=createoleobject('jro.jetengine');
    vje.compactdatabase(format(sconnectionstring,[afilename,aoldpassword]),
        format(sconnectionstring,[stempfilename,anewpassword]));
    result:=copyfile(pchar(stempfilename),pchar(afilename),false);
    deletefile(stempfilename);
  except
    result:=false;
  end;
end;

2.access中使用sql语句应注意的地方及几点技巧
以下sql语句在access xp的查询中测试通过
建表:
    create table tab1 (
        id counter,
        name string,
        age integer,
        [date] datetime);
技巧:
    自增字段用 counter 声明.
    字段名为关键字的字段用方括号[]括起来,数字作为字段名也可行.

建立索引:
    下面的语句在tab1的date列上建立可重复索引
    create index idate on tab1 ([date]);
    完成后access中字段date索引属性显示为 - 有(有重复).
    下面的语句在tab1的name列上建立不可重复索引