久久中文视频-久久中文网-久久中文亚洲国产-久久中文字幕久久久久-亚洲狠狠成人综合网-亚洲狠狠婷婷综合久久久久

以文本方式查看主題

-  曙海教育集團論壇  (http://www.xinguifushi.cn/bbs/index.asp)
--  Delphi程序設計  (http://www.xinguifushi.cn/bbs/list.asp?boardid=76)
----  Delphi中的Access技巧集  (http://www.xinguifushi.cn/bbs/dispbbs.asp?boardid=76&id=2568)

--  作者：wangxinxin
--  發布時間：2010-12-14 11:02:52
--  Delphi中的Access技巧集

1.DELPHI中操作Access數據庫(建立.mdb文件,壓縮數據庫)
以下代碼在WIN2K,D6,MDAC2.6下測試通過,
編譯好的程序在WIN98第二版無Access環境下運行成功.
G#i;k&yW1j0//在之前uses ComObj,ActiveX
//聲明連接字符串
Const
SC
+\'Jet OLEDB:Database Password=%s;\';
$Uio\'L{ O e)I0

//=============================================================================
// Procedure: GetTempPathFileName
// Author : ysai
lV#H0|GW0// Date : 2003-01-27
Wii3m xq7Z~PW0// Arguments: (None)
// Result : string
//=============================================================================
f ? \\8o:[7_C0function GetTempPathFileName():string;
:Bnr%[2m9v0//取得臨時文件名
var
6?[vh|&_%S0SPath,Sfile&:array [0..254] of char;
DEmTKw`(H;KS0begin
%])xJ,V%N+eYN7M:P0GetTempPath(254,SPath);
^"^/L?!T&YAm:V#t!mP0GetTempFileName(SPath,\'~SM\',0,SFile);
result:=SFile;
:h/G"fY7z6k0DeleteFile(PChar(result));
G(WzO a1i [^z)b0end; www.iTbulo.com0sJZ0

G(WzO a1i [^z)b0end; www.iTbulo.com0sJZ0.n,M.yP B8U3g6~2f0

@+Y6Y |#^opfc\\^N.b:f0//=============================================================================
,g7b,O%^C0// Procedure: CreateAccessFile
)Y2y RWO.c0// Author : ysai
// Date : 2003-01-27
1lLR7?5Fdfm#E]0// Arguments: FileName:String;Password:string=\'\'
// Result : boolean
//=============================================================================
m8CU3E8xa(_0pj0function CreateAccessFile(FileName:String;Password:string=\'\'):boolean;
-b,mD4x9T MTa/R0//建立Access文件，如果文件存在則失敗
.\\b)?!MIO0var
yy/ET x0STempFileName:string;
rPd:k@$l:g\'k[1f\'D0vCatalog:OleVariant;
begin
LtD3y@"{ j&`*T q0STempFileName:=GetTempPathFileName;
X&P7vn%Yb%e J&t0try
pk`7O#txMPQ&C0vCatalog:=CreateOleObject(\'ADOX.Catalog\');
vCatalog.Create(format(SConnectionString,[STempFileName,Password]));
result:=CopyFile(PChar(STempFileName),PChar(FileName),True);
DeleteFile(STempFileName);
except
result:=false;
1F^W _ez0end;
S&N!]L+t^0end; www.iTbulo.com0sJZ0

0cP3^6HE9ct;Vg$S0//=============================================================================
// Procedure: CompactDatabase
// Author : ysai
// Date : 2003-01-27
[ov7A+P0ss0// Arguments: AFileName,APassword:string
P1W\'Xi|0// Result : boolean
//=============================================================================
s#r4[ u+J1b~0@!e0function CompactDatabase(AFileName,APassword:string):boolean;
~1VsUD0//壓縮與修復數據庫,覆蓋源文件
uN5e ?X0var
STempFileName:string;
vJE:OleVariant;
begin
7] \\I/g)Z|[0STempFileName:=GetTempPathFileName;
try
vB%T4p2Ak?a0vJE:=CreateOleObject(\'JRO.JetEngine\');
Nt)v%J(E e*~ qT8q/?*]0vJE.CompactDatabase(format(SConnectionString,[AFileName,APassword]),
format(SConnectionString,[STempFileName,APassword]));
result:=CopyFile(PChar(STempFileName),PChar(AFileName),false);
3[6j1T,gI0DeleteFile(STempFileName);
W0Jt9\\h$a9V.z ID:^.{0except
result:=false;
end;
end; www.iTbulo.com0sJZ0

o9mk*C.f*V5^(W1_/h0

\\G(q|#w0u.Xu0    //=============================================================================
ycSQ yE0// Procedure: ChangeDatabasePassword
// Author : ysai
,d6t+M.g3J7k^0// Date : 2003-01-27
1DG]JF II0// Arguments: AFileName,AOldPassword,ANewPassWord:string
$ni%]h9O8o@!mu{0// Result : boolean
PJ9p6k:M0//=============================================================================
function ChangeDatabasePassword(AFileName,AOldPassWord,ANewPassWord:string):boolean;
;`yy U(a\'DJ0//修改Access數據庫密碼
r]F(ay4l+k0var
STempFileName:string;
vJE:OleVariant;
begin
k7W(XYi9R0^Q_1s2@0STempFileName:=GetTempPathFileName;
3Mkm%e{7C`-A w&\\0try
5zhs,|m3Z [V9N0v2Q0vJE:=CreateOleObject(\'JRO.JetEngine\');
m&R+]/G/jf:z0gD0vJE.CompactDatabase(format(SConnectionString,[AFileName,AOldPassword]),
format(SConnectionString,[STempFileName,ANewPassword]));
w6R"Q&dE%q[f@Y0result:=CopyFile(PChar(STempFileName),PChar(AFileName),false);
-v H$@ T!Z;v0DeleteFile(STempFileName);
except
i$x%AU2u_w mC0result:=false;
end;
!p(A,l6]z^ej1t0end; www.iTbulo.com0sJZ0

2.Access中使用SQL語句應注意的地方及幾點技巧
以下SQL語句在Access XP的查詢中測試通過
建表:
?UCXzP7?R0Create Table Tab1 (
ID Counter,
Name string,
^wbV+WN^3a#P1l e0Age integer,
[Date] DateTime);
技巧:
自增字段用 Counter 聲明.
M"YQ3zG,ccO O^0字段名為關鍵字的字段用方括號[]括起來,數字作為字段名也可行. www.iTbulo.com0sJZ0

Y{*OV` N0

建立索引:
z\'w/YZT3lG D4Z0下面的語句在Tab1的Date列上建立可重復索引
W6M"X&d"m&}h0Create Index iDate ON Tab1 ([Date]);
完成后Access中字段Date索引屬性顯示為 - 有(有重復).
Ll)@y(d0下面的語句在Tab1的Name列上建立不可重復索引
Create Unique Index iName ON Tab1 (Name);
完成后Access中字段Name索引屬性顯示為 - 有(無重復).
!R@0UDo` Q}o e0下面的語句刪除剛才建立的兩個索引
Drop Index iDate ON Tab1;
W QuVU,_[wQ0Drop Index iName ON Tab1; www.iTbulo.com0sJZ0

%{T\'Htk-p9p(?.[0

Access與SQLSERVER中的UPDATE語句對比:
SQLSERVER中更新多表的UPDATE語句:
W(X?/N @_(Do$@I0UPDATE Tab1
SET a.Name = b.Name
$X3jL1]8F H K0FROM Tab1 a,Tab2 b
WHERE a.ID = b.ID;
d ?h9HEK0同樣功能的SQL語句在Access中應該是
$i.f9A*yz\'e0 UPDATE Tab1 a,Tab2 b
SET a.Name = b.Name
An{S-t#F9p |S3xL0WHERE a.ID = b.ID;
即:Access中的UPDATE語句沒有FROM子句,所有引用的表都列在UPDATE關鍵字后.
上例中如果Tab2可以不是一個表,而是一個查詢,例:
,Gzs9cE V4?1[ f0UPDATE Tab1 a,(Select ID,Name From Tab2) b
D3\\Jg3G[C0SET a.Name = b.Name
WHERE a.ID = b.ID;
a%m(r4Y#`0o&CK0www.iTbulo.com0sJZ0

訪問多個不同的Access數據庫-在SQL中使用In子句:
Select a.*,b.* From Tab1 a,Tab2 b In \'DB2.mdb\' Where a.ID=b.ID;
)]tZ E!G)bYW;L%e C-c0上面的SQL語句查詢出當前數據庫中Tab1和DB2.mdb(當前文件夾中)中Tab2以ID為關聯的所有記錄.
缺點-外部數據庫不能帶密碼.
補充:看到ugvanxk在一貼中的答復,可以用
Select * from [c:\\aa\\a.mdb;pwd=1111].table1;