在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/ 　　中找到异构数据库之间导数据参见 　　http://www.delphibbs.com/delphibbs/dispq.asp?lid=1691966 　　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不认,所以为了迁移方便和兼容, 　　建议用单引号作为字符串分隔符.     eg1: 大家知道，如果将一个表所有者改为dbo，只需 sp_changeobjectowner '表名','dbo'即可但要将所有的表的所有者都改为dbo,可以用循环处理，此处略其实用SQL的系统存储过程sp_MSForEachTable可以轻松搞定只需exec sp_MSForEachTable 'sp_changeobjectowner ''?'', ''dbo''' 当然，可能运行时会有错(比如有些表的所有者原先就是dbo)，不用理它，运行一遍就行了。   eg2: 所有表都加上一个自增长的字段大家知道对一个表加的话只需： alter table 表名 add iid int identity(1,1) 若需对所有的表都加的话只需: sp_MSForEachTable 'alter table ? add iid int identity(1,1)' 同理，删除所有表中的iid字段只需 sp_MSForEachTable 'alter table ? drop column iid' 当然，可能运行时会有错(比如加字段时该表中已有该字段，删除字段时没有该字段)，不用理它，运行一遍就行了。   eg3: 对所有表中的num字段进行四舍五入处理 sp_MSForEachTable 'update ? set num = round(num,2)' 当然，可能运行时会有错(比如有些表中没有num字段)，不用理它，运行一遍就行了     在SQLServer中执行SELECT * into bmkFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="e:/share";User ID=Admin;Password=;Extended properties=dBase 5.0')...bmk这样就可以把e:/share中的bmk.dbf表导入到Sqlserver中，速度是最快的上面这个方法DBF文件必须在服务器上，如果DBF不在服务器上，就用连接串Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/temp;Extended Properties="dBase 5.0;";Persist Security Info=FalseSQL语句SELECT * into aaa IN [ODBC][ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;] from aaa这是直接把客户端上的DBF传上服务器的最直接方法。把压箱底的方法都告诉大家在Form上放一个ADOConnection,连结指向目标Access库比如txt文件在c:/temp/aaaa.txt就执行ADOConnection.Connected := True;ADOConnection.Execute('Select * Into abcd From [Text;Database=c:/temp].aaaa.txt');就一切Ok了,这个方法一定行的，我好不容易才研究出来的有了这两个例子，异构数据库之间导数据的问题就举一反三，迎刃而解了。把Excel导入到Access中,同txt类似select * into <table name> from [excel 8.0;database=<filename>].[<sheetname>$] 我最讨厌别人用BatchMove导数据了，明明一个SQL的事情，偏要。。。我的目标是让大家抛弃BatchMove Provider=MSDASQL.1;Extended Properties="Driver={Microsoft Visual Foxpro Driver};SourceType=DBF;SourceDB=你的文件所在路径;"       unit Access; interface uses  Windows, SysUtils, Variants, Classes, Graphics, Controls, StdCtrls, ComObj,  DB, ADODB; //创建Access数据库，DBFileName为数据库文件的完整路径//                  ForceWrite为是否强制建立//创建成功返回True，否则返回Falsefunction CreateAccessDB(DBFullName: String; ForceWrite: Boolean): Boolean; //删除Access数据库表procedure DropAccessTable( AccessFullName: String;      //Access数据库完整文件名                           ATable: String               //表名                           ); //从SQL Server导出表至Access数据库procedure SQLServer2Access( AccessFullName: String;      //Access数据库完整文件名                            ASQLServer: String;          //SQL Server服务器名                            AUserID: String;             //SQL Server用户名                            APassword: String;           //SQL Server用户口令                            ADBName: String;             //SQL Server数据库名                            ATable: String               //SQL Server表名                            ); //从Access导出表至Access数据库       16:15:37 procedure Access2Access(AccessFromName: String;      //源Access数据库完整文件名                        AccessToName: String;        //目的Access数据库完整文件名                        ATable: String               //Access表名                        ); //取得Access数据库表列表procedure GetTableList(AccessDBName: String; var TableList: TStringList); //判断Access数据库中是否存在表function TableExists(AccessFullName: String;      //Access数据库完整文件名                     ATable: String               //表名                     ): Boolean; //创建Access数据库表索引procedure CreateAccessIndex( AccessFullName: String;      //Access数据库完整文件名                             ATable: String;              //表名                             AIndex: String;              //索引名                             AFields: String;             //字段描述                             IsUnique: Boolean;           //是否无重复索引                             IsPrimary: Boolean           //是否主键                             ); //压缩与修复数据库,覆盖源文件function CompactDatabase(AccessFullName: String): Boolean; implementation //function CreateAccessDB(DBFullName: String; ForceWrite: Boolean): Boolean;var  AccessDB: OleVariant;begin  Result := True;  if ForceWrite and FileExists(DBFullName) then    if not DeleteFile(DBFullName) then    begin      Result := False;      Exit;    end;   if not ForceWrite and FileExists(DBFullName) then  begin    Result := False;    Exit;  end;   AccessDB := CreateOleObject('ADOX.Catalog');  AccessDB.Create('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + DBFullName);end;/ ///取得Access数据库表列表procedure GetTableList(AccessDBName: String; var TableList: TStringList);var  AccessCont: TADOConnection;begin  AccessCont := TADOConnection.Create(nil);  try    AccessCont.CommandTimeout := 300;    AccessCont.Provider := 'Microsoft.Jet.OLEDB.4.0';    AccessCont.LoginPrompt := False;    AccessCont.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";'      + 'Persist Security Info=True;Data Source=' + AccessDBName;     AccessCont.Open;    AccessCont.GetTableNames(TableList);    AccessCont.Close;  finally    AccessCont.Free;  end;end; / ///判断Access数据库中是否存在表function TableExists(AccessFullName: String;      //Access数据库完整文件名                     ATable: String               //表名                     ): Boolean;var  TableList: TStringList;  AccessCont: TADOConnection;  i: Integer;begin  Result := False;  AccessCont := TADOConnection.Create(nil);  TableList := TStringList.Create;  try    AccessCont.CommandTimeout := 0;    AccessCont.Provider := 'Microsoft.Jet.OLEDB.4.0';    AccessCont.LoginPrompt := False;    AccessCont.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";'      + 'Persist Security Info=True;Data Source=' + AccessFullName;     AccessCont.Open;    AccessCont.GetTableNames(TableList);    //Result := TableList.IndexOfName(ATable) <> -1;    for i := 0 to TableList.Count - 1 do      if TableList[i] = ATable then      begin        Result := True;        Break;      end;    AccessCont.Close;  finally    AccessCont.Free;    TableList.Free;  end;end;/ /procedure SQLServer2Access( AccessFullName: String;      //Access数据库完整文件名                            ASQLServer: String;          //SQL Server服务器名                            AUserID: String;             //SQL Server用户名                            APassword: String;           //SQL Server用户口令                            ADBName: String;             //SQL Server数据库名                            ATable: String               //SQL Server表名                            );var  AccessCont: TADOConnection;  sqltmp: String;begin  AccessCont := TADOConnection.Create(nil);  try    AccessCont.CommandTimeout := 0;    AccessCont.Provider := 'Microsoft.Jet.OLEDB.4.0';    AccessCont.LoginPrompt := False;    AccessCont.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";'      + 'Persist Security Info=True;Data Source=' + AccessFullName;   //目的表存在则删除    if TableExists(AccessFullName, ATable) then DropAccessTable(AccessFullName, ATable);    sqltmp := 'SELECT * INTO [' + ATable + '] FROM [' + ATable + '] IN [ODBC] [ODBC;Driver=SQL Server;'      + 'UID=' + AUserID + ';PWD=' + APassword + ';Server=' + ASQLServer      + ';DataBase=' + ADBName + ';]';    AccessCont.Open;    AccessCont.Execute(sqltmp);  finally    AccessCont.Free;  end;end;/ /procedure CreateAccessIndex( AccessFullName: String;      //Access数据库完整文件名                             ATable: String;              //表名                             AIndex: String;              //索引名                             AFields: String;             //字段描述                             IsUnique: Boolean;           //是否无重复索引                             IsPrimary: Boolean           //是否主键                             );var  sqltmp: String;  AccessCont: TADOConnection;begin  AccessCont := TADOConnection.Create(nil);  try    AccessCont.CommandTimeout := 0;    AccessCont.Provider := 'Microsoft.Jet.OLEDB.4.0';    AccessCont.LoginPrompt := False;    AccessCont.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";'      + 'Persist Security Info=True;Data Source=' + AccessFullName;     if IsUnique then sqltmp := 'CREATE UNIQUE INDEX '    else sqltmp := 'CREATE INDEX ';    sqltmp := sqltmp + AIndex + ' ON [' + ATable + '](' + AFields + ')';    if IsPrimary then sqltmp := sqltmp + ' WITH PRIMARY';     AccessCont.Open;    AccessCont.Execute(sqltmp);  finally    AccessCont.Free;  end;end; / ///删除Access数据库表procedure DropAccessTable( AccessFullName: String;      //Access数据库完整文件名                           ATable: String               //表名                           );var  sqltmp: String;  AccessCont: TADOConnection;begin  AccessCont := TADOConnection.Create(nil);  try    AccessCont.CommandTimeout := 0;    AccessCont.Provider := 'Microsoft.Jet.OLEDB.4.0';    AccessCont.LoginPrompt := False;    AccessCont.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";'      + 'Persist Security Info=True;Data Source=' + AccessFullName;     sqltmp := 'DROP TABLE [' + ATable + ']';     AccessCont.Open;    AccessCont.Execute(sqltmp);  finally    AccessCont.Free;  end;end; / /function CompactDatabase(AccessFullName: String): Boolean;//压缩与修复数据库,覆盖源文件   function GetTempPathFileName:string;  //取得临时文件名  var    SPath, SFile: array[0..254] of char;  begin    GetTempPath(254, SPath);    GetTempFileName(SPath, '~SM', 0, SFile);    Result := SFile;    DeleteFile(Result);  end; var  STempFileName, SConnectionString: String;  vJE: OleVariant;begin  STempFileName := GetTempPathFileName;  SConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=%s';  try    vJE := CreateOleObject('JRO.JetEngine');    vJE.CompactDatabase( Format(SConnectionString,[AccessFullName]),        Format(SConnectionString,[STempFileName]) );    Result := CopyFile(PChar(STempFileName), PChar(AccessFullName), False);    DeleteFile(STempFileName);  except    Result := False;  end;end;/ ///从Access导出表至Access数据库procedure Access2Access(AccessFromName: String;      //源Access数据库完整文件名                        AccessToName: String;        //目的Access数据库完整文件名                        ATable: String               //Access表名                        );var  AccessTo: TADOConnection;  sqltmp: String;begin  AccessTo   := TADOConnection.Create(nil);  try    AccessTo.CommandTimeout := 0;    AccessTo.Provider := 'Microsoft.Jet.OLEDB.4.0';    AccessTo.LoginPrompt := False;    AccessTo.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";'      + 'Persist Security Info=True;Data Source=' + AccessToName;     //目的表存在则删除    if TableExists(AccessToName, ATable) then DropAccessTable(AccessToName, ATable);     sqltmp := 'SELECT * INTO [' + ATable + '] FROM [' + ATable + '] IN ' + QuotedStr(AccessFromName);     AccessTo.Open;    AccessTo.Execute(sqltmp);  finally    AccessTo.Free;  end;end;/ end.