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.
在Delphi中的Access技巧集
最新推荐文章于 2021-07-22 17:15:27 发布