delphiaccess:Delphi操作ACCESS技巧集来源: 发布时间:星期四, 2009年2月12日 浏览:126次 评论:0
1.DELPHI中操作ACCESS数据库(建立.mdb文件,压缩数据库) 以下代码在WIN2K,D6,MDAC2.6下测试通过, 编译好 ![]() ![]() //在的前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 : ![]() // ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() function GetTempPathFileName ![]() ![]() //取得临时文件名 var SPath,SFile: 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: ![]() // Result : boolean // ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() function CreateAccessFile(FileName:String;PassWord: ![]() //建立Access文件 ![]() var STempFileName: ![]() 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: ![]() // Result : boolean // ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() function CompactDatabase(AFileName,APassWord: ![]() //压缩和修复数据库,覆盖源文件 var STempFileName: ![]() 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: ![]() // Result : boolean // ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() function ChangeDatabasePassword(AFileName,AOldPassWord,ANewPassWord: ![]() //修改ACCESS数据库密码 var STempFileName: ![]() 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 ![]() Age ![]() [Date] DateTime); 窍门技巧: 自增字段用 Counter 声明. 字段名为关键字 ![]() ![]() 建立索引: 下面 ![]() ![]() Create Index iDate _disibledevent=> UPDATE Tab1 SET a.Name = b.Name FROM Tab1 a,Tab2 b WHERE a.ID = b.ID; 同样功能 ![]() UPDATE Tab1 a,Tab2 b SET a.Name = b.Name WHERE a.ID = b.ID; 即:ACCESS中 ![]() ![]() 上例中如果Tab2可以不是 ![]() ![]() UPDATE Tab1 a,(Select ID,Name From Tab2) b SET a.Name = b.Name WHERE a.ID = b.ID; 访问多个区别 ![]() Select a.*,b.* From Tab1 a,Tab2 b In \'db2.mdb\' Where a.ID=b.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;] 其中 ![]() ![]() 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中 ![]() ![]() 建议用单引号作为 ![]() 0
相关文章读者评论发表评论 |
|