首先DBF文件要自己建立,结构与类型都定义好。例如:用VF。
我们就以两个字段为例:
filelds1 字符型
filelds2 数值型
表(DBF)文件名称为 Test.dbf
可以SQLServer 的查询分析器中执行下面代码:
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE= c:/','select * from [ test.dbf]')
主要有两个地方需要注意:一个文件存放的路径,一个文件名。
到这应该看出来了。主要就是这句代码:
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/','select * from [test.dbf]')
这个可以相当于一个表或视图来用。可以对它进行insert 、update、delete 、select 的操作。但是delete操作不完全清除DBF文件中的记录,还需要在VF中执行pack才能彻底删除。
粘几段代码,但不解释了
insert into openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/cbjRecord','select cscardid,zch,name,address,classid,classname,iunit,subjoin,ecgsid,ecgsname,ecgsunit from [records.dbf]')
SELECT CsCard.ID, CsCard.Zch, CsCard.cName, CsCard.Address, CsCard.ClassItemID,
ClassItem.cName AS className, ClassItem.iunit, ClassItem.Subjoin, CsCard.Ecgs,
Ecgs.cName AS ecgsName, Ecgs.iUnit AS ecgsunit
FROM CsCard INNER JOIN
ClassItem ON CsCard.ClassItemID = ClassItem.ID INNER JOIN
Ecgs ON CsCard.Ecgs = Ecgs.ID
WHERE (CsCard.CsyId = '01') and cscard.id not in (select cscardid from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/cbjRecord','select * from [records.dbf]'))
ORDER BY CsCard.ID
update openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/cbjRecord','select bfinger,finger,quantity,money,submoney,ecgsmoney,abnorstate from [records.dbf]')
set bfinger=0
update openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/cbjRecord','select bfinger,finger,quantity,money,submoney,ecgsmoney,abnorstate from [records.dbf]')
set bfinger=finger
where finger>bfinger
我们就以两个字段为例:
filelds1 字符型
filelds2 数值型
表(DBF)文件名称为 Test.dbf
可以SQLServer 的查询分析器中执行下面代码:
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE= c:/','select * from [ test.dbf]')
主要有两个地方需要注意:一个文件存放的路径,一个文件名。
到这应该看出来了。主要就是这句代码:
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/','select * from [test.dbf]')
这个可以相当于一个表或视图来用。可以对它进行insert 、update、delete 、select 的操作。但是delete操作不完全清除DBF文件中的记录,还需要在VF中执行pack才能彻底删除。
粘几段代码,但不解释了
insert into openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/cbjRecord','select cscardid,zch,name,address,classid,classname,iunit,subjoin,ecgsid,ecgsname,ecgsunit from [records.dbf]')
SELECT CsCard.ID, CsCard.Zch, CsCard.cName, CsCard.Address, CsCard.ClassItemID,
ClassItem.cName AS className, ClassItem.iunit, ClassItem.Subjoin, CsCard.Ecgs,
Ecgs.cName AS ecgsName, Ecgs.iUnit AS ecgsunit
FROM CsCard INNER JOIN
ClassItem ON CsCard.ClassItemID = ClassItem.ID INNER JOIN
Ecgs ON CsCard.Ecgs = Ecgs.ID
WHERE (CsCard.CsyId = '01') and cscard.id not in (select cscardid from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/cbjRecord','select * from [records.dbf]'))
ORDER BY CsCard.ID
update openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/cbjRecord','select bfinger,finger,quantity,money,submoney,ecgsmoney,abnorstate from [records.dbf]')
set bfinger=0
update openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=c:/cbjRecord','select bfinger,finger,quantity,money,submoney,ecgsmoney,abnorstate from [records.dbf]')
set bfinger=finger
where finger>bfinger