Delphi中的Access技巧集

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列上创造不行反复索引 create unique index iname on tab1 (name); 实行后access中字段name索引属性表露为 - 有(无反复). 底下的语句简略方才创造的两个索引 drop index idate on tab1; drop index iname on tab1;

access与sqlserver中的update语句比较: sqlserver中革新多表的update语句:

update tab1 set a.name = b.name from tab1 a,tab2 b where a.id = b.id;

同样功效的sql语句在access中该当是 update tab1 a,tab2 b set a.name = b.name where a.id = b.id;

即:access中的update语句没有from子句,一切援用的表都列在update要害字后. 上例中即使tab2不妨不是一个表,而是一个查问,例: update tab1 a,(select id,name from tab2) b set a.name = b.name where a.id = b.id;

考察多个各别的access数据库-在sql中运用in子句: select a.*,b.* from tab1 a,tab2 b in 'db2.mdb' where a.id=b.id; 上头的sql语句查问出暂时数据库中tab1和db2.mdb(暂时文献夹中)中tab2以id为关系的一切记载. 缺陷-外部数据库不许带暗号. 弥补:看到ugvanxk在一贴中的回复,不妨用 select * from [c:\aa\a.mdb;pwd=1111].table1; access xp尝试经过

在access中考察其它odbc数据源 下例在access中查问sqlserver中的数据

select * from tab1 in [odbc] [odbc;driver=sql server;uid=sa;pwd=;server=127.0.0.1;database=demo;]

外部数据源贯穿属性的完备参数是: [odbc;driver=driver;server=server;database=database;uid=user;pwd=password;]

个中的driver=driver不妨在备案表中的 hkey_local_machine\software\odbc\odbcinst.ini\ 中找到 异构数据库之间导数据拜见 碧血剑 

access扶助子查问

access扶助外贯穿,但不囊括完备外部联接,如扶助 left join 或 right join 但不扶助 full outer join 或 full join

access中的日子查问 提防:access中的日子功夫分割符是#而不是引号 select * from tab1 where [date]>#2002-1-1#; 在delphi中我如许用 sql.add(format( 'select * from tab1 where [date]>#%s#;', [datetostr(date)]));

access中的字符串不妨用双引号分割,但sqlserver不认,以是为了迁徙简单和兼容, 倡导用单引号动作字符串分割符.