用delphi怎样做sqlserver与access、excel之间的数据转化？

最近做了sql server-->access-->sql server的代码，好象我刚开始解决的时候花了300分(100分csdn分，200分大富翁)啊，可是楼主只有少少的50分啊，不过，看楼主好象分不多，就忍了。

1）sql-->access:创建access数据库：大富翁经典创建：
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;

http://www.tongyi.net/article/20031101/200311013786.shtml

2)adoconnetion连到access数据库上，下面是段挺经典的连接代码，多是多，不过，不出意外：var
  sconstr:string;//连接语句
  sdbpath:string;//数据库路径
  smm:string;    //密码
begin
  smm:='AAA';
  try
    sdbpath:=ExtractFilePath(Application.ExeName)+'AA67.MDB';
    sconstr:='Provider=Microsoft.Jet.OLEDB.4.0;'+
           'User ID=Admin;Data Source='+sdbpath+';'+
           'Mode=Share Deny None;Extended Properties="";'+
           'Locale Identifier=2052;Persist Security Info=False;'+
           'Jet OLEDB:System database="";Jet OLEDB:Registry Path="";'+
           'Jet OLEDB:Database Password='+smm+';'+
           'Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;'+
           'Jet OLEDB:Global Partial Bulk Ops=2;'+
           'Jet OLEDB:Global Bulk Transactions=1;'+
           'Jet OLEDB:New Database Password="";'+
           'Jet OLEDB:Create System Database=False;'+
           'Jet OLEDB:Encrypt Database=False;'+
           'Jet OLEDB:Don''t Copy Locale on Compact=False;'+
           'Jet OLEDB:Compact Without Replica Repair=False;'+
           'Jet OLEDB:SFP=False';
    if DM1.ADOCn1.Connected = True then DM1.ADOCn1.Connected:=False;
    DM1.ADOCn1.ConnectionString:=sconstr;
    DM1.ADOCn1.Connected:=True;//DM1.ADOCN1:TADOCONNECTION
  except
    Application.MessageBox('无法连接数据库！','错误',MB_OK+MB_ICONSTOP);
    Halt;
  end;
end;
3)导入（用odbc来实现）：adoconnection.Execute('SELECT * into 表名  FROM 表名 IN [ODBC][ODBC;Driver=SQL Server;UID='+username+';PWD='+password+';Server='+servername+';DataBase='+database+';]');

等楼主作完sql server-->access,再把剩下给你，现在有点忙

请先熟练使用ADO ADOX,就容易啦. ASP都可以完成啦.

感谢各位大侠的帮助
不过请问有没有sqlserver转excel的？
谢谢了

异构数据库之间导数据参见碧血剑的
http://www.delphibbs.com/delphibbs/dispq.asp?lid=1691966

hongama(不知道该说什么好，好强的id啊，不愧是前辈啊）那个http://www.delphibbs.com/delphibbs/dispq.asp?lid=1691966
大富翁论坛上不去啊
请教方法
还有烦劳您把excel的也赶紧写了
谢您了

调试易

用delphi怎样做sqlserver与access、excel之间的数据转化？

解决方案 »