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不認,所以為了遷移方便和相容,
建議用單引號作為字串分隔符號.