sql server保存并转换成ACCESS文件 我想用程序来实现,不用SQLSERVER带的哪个导入导出程序,请高手指教,详细解答我一定送上高分 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 用两个adoquery,一个连sql server , 一个连 access ,while not adoquery1.eofbegin 从 adoquery1 读出数据 向 adoquery2 写数据。 adoquery.next;end;写数据可以用sql语句,然后用adoquery执行。或者直接insert。如果数据量不大的话(<10万),是不会有问题的。 unit FrmConvertData;interfaceuses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, DBTables, DB, Grids, DBGrids, StdCtrls, Buttons, DBCommon, BDE, SMINTF, CheckLst, ExtCtrls, ComCtrls,Registry;type TConvertDataForm = class(TForm) OpenDialog: TOpenDialog; Panel1: TPanel; Label1: TLabel; sbtnOpenFile: TSpeedButton; edtDBName: TEdit; Panel2: TPanel; dbTableDetail: TDBGrid; stbConStatus: TStatusBar; Panel3: TPanel; clbTableName: TCheckListBox; rgSel: TRadioGroup; btnTestConnect: TButton; Panel4: TPanel; btnRefresh: TButton; btnApply: TButton; btnExit: TButton; prbStatus: TProgressBar; btnConfig: TButton; DBDestination: TDatabase; tblDestination: TTable; btmConvertData: TBatchMove; DBSource: TDatabase; tblSource: TTable; dsSource: TDataSource; procedure btnExitClick(Sender: TObject); procedure btnRefreshClick(Sender: TObject); procedure rgSelClick(Sender: TObject); procedure FormShow(Sender: TObject); procedure sbtnOpenFileClick(Sender: TObject); procedure btnTestConnectClick(Sender: TObject); procedure btnApplyClick(Sender: TObject); procedure clbTableNameClick(Sender: TObject); procedure btnConfigClick(Sender: TObject); private { Private declarations } reg:TRegistry; gSourceDBName :string; procedure SetConnectLink; function IsSelected :boolean; public { Public declarations } end;var ConvertDataForm: TConvertDataForm;implementation{$R *.dfm}procedure TConvertDataForm.btnExitClick(Sender: TObject);begin Close;end;procedure TConvertDataForm.btnRefreshClick(Sender: TObject);begin dbSource.GetTableNames(clbTableName.Items,False); rgSel.ItemIndex :=1;end;procedure TConvertDataForm.rgSelClick(Sender: TObject);var i :integer;begin for i:=0 to clbTableName.Items.Count-1 do if rgSel.ItemIndex =0 then clbTableName.Checked[i] :=True else clbTableName.Checked[i] :=False;end;procedure TConvertDataForm.SetConnectLink;begin reg:=TRegistry.Create ; reg.RootKey :=HKEY_LOCAL_MACHINE; if reg.OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',True) then reg.WriteString('TaxNet','SQL Server'); reg.CloseKey; if reg.OpenKey('Software\ODBC\ODBC.INI\TaxNet',True) then begin reg.WriteString('DataBase','TaxNet'); reg.WriteString('Description','山西税务 Fox SQLServer'); reg.WriteString('Driver','C:\WINDOWS\System32\sqlsrv32.dll'); reg.WriteString('LastUser','SA'); reg.WriteString('Server','.'); end; reg.CloseKey; if reg.OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',True) then reg.WriteString('TaxNetFox','Microsoft Visual FoxPro Driver'); reg.CloseKey; if reg.OpenKey ('Software\ODBC\ODBC.INI\TaxNetFox',True) then begin reg.WriteString('BackGroundFetch','Yes'); reg.WriteString('Collate','Machine'); reg.WriteString('Deleted','Yes'); reg.WriteString('Description','山西税务TaxNet For Visual FoxPro'); reg.WriteString('Driver','C:\Windows\System32\vfpodbc.dll'); reg.WriteString('Exclusive','No'); reg.WriteString('SetNoCountOn','No'); reg.WriteString('SourceType','DBC'); end; reg.CloseKey; reg.Free;end;procedure TConvertDataForm.FormShow(Sender: TObject);begin stbConStatus.BringToFront; prbStatus.SendToBack; stbConStatus.Panels[1].Text :='数据库别名: Visual FoxPro为TaxNetFox,SQLServer为\TaxNet!'; SetConnectLink;end;procedure TConvertDataForm.sbtnOpenFileClick(Sender: TObject);begin if OpenDialog.Execute then begin edtDBName.Text :=OpenDialog.FileName; gSourceDBName :=OpenDialog.FileName; end;end;procedure TConvertDataForm.btnTestConnectClick(Sender: TObject);begin if gSourceDBName='' then begin Application.MessageBox('请先设置FoxPro数据库!','提示',MB_IconInformation+MB_OK); Exit; end; reg :=TRegistry.Create; reg.RootKey :=HKEY_LOCAL_MACHINE; if reg.OpenKey('Software\ODBC\ODBC.INI\TaxNetFox',True) then reg.WriteString('SourceDB',gSourceDBName); reg.CloseKey; reg.Free; try dbSource.Close; dbSource.Connected :=True; except dbSource.Connected :=False; Application.MessageBox('数据库连接错误!','提示',MB_IconInformation+MB_OK); Exit; end; dbSource.GetTableNames(clbTableName.Items,False); rgSel.ItemIndex :=1;end;procedure TConvertDataForm.btnApplyClick(Sender: TObject);var i :integer;begin if not IsSelected then Exit; try stbConStatus.SendToBack; prbStatus.BringToFront; prbStatus.Position :=0; for i:=0 to clbTableName.Items.Count-1 do begin stbConStatus.Panels[0].Text :='正在转换:'+clbTableName.Items.Strings[i]; if clbTableName.Checked[i] then begin if tblSource.Active then tblSource.Close; if tblDestination.Active then tblDestination.Close; tblSource.TableName:=clbTableName.Items.Strings[i]; tblDestination.TableName :='tmp_'+ChangeFileExt(ExtractFileName(gSourceDBName),'') +'_'+clbTableName.Items.Strings[i]; tblSource.Open; btmConvertData.Mode :=batCopy; btmConvertData.Source :=tblSource; btmConvertData.Destination :=tblDestination; btmConvertData.Execute; end else continue; prbStatus.Position :=prbStatus.Position+(clbTableName.Items.Count Div 100); Application.ProcessMessages; end; prbStatus.Position :=0; stbConStatus.Panels[1].Text :='数据库别名: Visual FoxPro为TaxNetFox,SQLServer为\TaxNet!'; Application.MessageBox('转换成功!','提示',MB_IconInformation+MB_OK); except prbStatus.Position :=0; stbConStatus.Panels[1].Text :='转换失败'; Application.MessageBox('转换失败!','提示',MB_IconInformation+MB_OK); end; stbConStatus.BringToFront; prbStatus.SendToBack;end;function TConvertDataForm.IsSelected: boolean;var i :integer;begin Result :=False; for i :=0 to clbTableName.Items.Count-1 do begin if clbTableName.Checked[i] =False then Result :=False else begin Result :=True; Exit; end; end;end;procedure TConvertDataForm.clbTableNameClick(Sender: TObject);begin if tblSource.Active then tblSource.Close; tblSource.TableName :=clbTableName.Items.Strings[clbTableName.ItemIndex]; tblSource.Open; stbConStatus.Panels[0].Text :='记录数:'+IntToStr(tblSource.RecordCount);end;procedure TConvertDataForm.btnConfigClick(Sender: TObject);begin WinExec('RunDLL32.exe Shell32.dll,Control_RunDLL ODBCCP32.CPL',SW_SHOWNORMAL);end;end. 我不同意libra163(石头) 的意见,昨天我写的导库程序里面有15万条数据而且还是从paradox里面向sql server里面导一点问题也没有,速度还可以的,哪知是倒数据,但是我好像不知道Access里面还有存储过程呀 //fromhttp://www.csdn.net/develop/read_article.asp?Id=18623Transact-SQL语句进行导入导出:1. 在SQL SERVER里查询access数据:-- ======================================================SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名------------------------------------------------------------------------------------------------- 2. 将access导入SQL server -- ======================================================在SQL SERVER 里运行:SELECT *INTO newtableFROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名------------------------------------------------------------------------------------------------- 3. 将SQL SERVER表里的数据插入到Access表中-- ======================================================在SQL SERVER 里运行:insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名 (列名1,列名2)select 列名1,列名2 from sql表 实例:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\db.mdb';'admin';'', Test) select id,name from Test INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)SELECT *FROM sqltablename------------------------------------------------------------------------------------------------- 注意这里的目录指的是SQL Server运行所在的机器~~ 怎么才能将SQL SERVER里提取的数据保存成ACCESS文件 一个小题 删除一条数据遇到个问题,在线等 *** 请问谁有base64的加解密算法 *** 数据集ClientDataset1,ClientDataset2选取的数据结构一致,请问如何将数据集2的加入到数据集1呢? 关于ini 用odbc连接sqlserver执行存储过程的问题 问各位一个问题,大家不要说我无聊:编程和网络管理,两个职位您选哪个?? 友情链接,我的网站http://fullstrong.myetang.com/ 推见基本DELPHI高级教程!!! 使用动态数组的几个问题 暴希奇的事啊~~~!!! 怎么模拟键盘的汉字输入??急阿!
一个连sql server , 一个连 access ,
while not adoquery1.eof
begin
从 adoquery1 读出数据
向 adoquery2 写数据。
adoquery.next;
end;
写数据可以用sql语句,然后用adoquery执行。
或者直接insert。
如果数据量不大的话(<10万),是不会有问题的。
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DBTables, DB, Grids, DBGrids, StdCtrls, Buttons, DBCommon, BDE, SMINTF,
CheckLst, ExtCtrls, ComCtrls,Registry;type
TConvertDataForm = class(TForm)
OpenDialog: TOpenDialog;
Panel1: TPanel;
Label1: TLabel;
sbtnOpenFile: TSpeedButton;
edtDBName: TEdit;
Panel2: TPanel;
dbTableDetail: TDBGrid;
stbConStatus: TStatusBar;
Panel3: TPanel;
clbTableName: TCheckListBox;
rgSel: TRadioGroup;
btnTestConnect: TButton;
Panel4: TPanel;
btnRefresh: TButton;
btnApply: TButton;
btnExit: TButton;
prbStatus: TProgressBar;
btnConfig: TButton;
DBDestination: TDatabase;
tblDestination: TTable;
btmConvertData: TBatchMove;
DBSource: TDatabase;
tblSource: TTable;
dsSource: TDataSource;
procedure btnExitClick(Sender: TObject);
procedure btnRefreshClick(Sender: TObject);
procedure rgSelClick(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure sbtnOpenFileClick(Sender: TObject);
procedure btnTestConnectClick(Sender: TObject);
procedure btnApplyClick(Sender: TObject);
procedure clbTableNameClick(Sender: TObject);
procedure btnConfigClick(Sender: TObject);
private
{ Private declarations }
reg:TRegistry;
gSourceDBName :string;
procedure SetConnectLink;
function IsSelected :boolean;
public
{ Public declarations }
end;var
ConvertDataForm: TConvertDataForm;implementation{$R *.dfm}
procedure TConvertDataForm.btnExitClick(Sender: TObject);
begin
Close;
end;procedure TConvertDataForm.btnRefreshClick(Sender: TObject);
begin
dbSource.GetTableNames(clbTableName.Items,False);
rgSel.ItemIndex :=1;
end;procedure TConvertDataForm.rgSelClick(Sender: TObject);
var
i :integer;
begin
for i:=0 to clbTableName.Items.Count-1 do
if rgSel.ItemIndex =0 then
clbTableName.Checked[i] :=True
else
clbTableName.Checked[i] :=False;
end;procedure TConvertDataForm.SetConnectLink;
begin
reg:=TRegistry.Create ;
reg.RootKey :=HKEY_LOCAL_MACHINE;
if reg.OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',True) then
reg.WriteString('TaxNet','SQL Server');
reg.CloseKey;
if reg.OpenKey('Software\ODBC\ODBC.INI\TaxNet',True) then
begin
reg.WriteString('DataBase','TaxNet');
reg.WriteString('Description','山西税务 Fox SQLServer');
reg.WriteString('Driver','C:\WINDOWS\System32\sqlsrv32.dll');
reg.WriteString('LastUser','SA');
reg.WriteString('Server','.');
end;
reg.CloseKey;
if reg.OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',True) then
reg.WriteString('TaxNetFox','Microsoft Visual FoxPro Driver');
reg.CloseKey;
if reg.OpenKey ('Software\ODBC\ODBC.INI\TaxNetFox',True) then
begin
reg.WriteString('BackGroundFetch','Yes');
reg.WriteString('Collate','Machine');
reg.WriteString('Deleted','Yes');
reg.WriteString('Description','山西税务TaxNet For Visual FoxPro');
reg.WriteString('Driver','C:\Windows\System32\vfpodbc.dll');
reg.WriteString('Exclusive','No');
reg.WriteString('SetNoCountOn','No');
reg.WriteString('SourceType','DBC');
end;
reg.CloseKey;
reg.Free;
end;procedure TConvertDataForm.FormShow(Sender: TObject);
begin
stbConStatus.BringToFront;
prbStatus.SendToBack;
stbConStatus.Panels[1].Text :='数据库别名: Visual FoxPro为TaxNetFox,SQLServer为\TaxNet!';
SetConnectLink;
end;procedure TConvertDataForm.sbtnOpenFileClick(Sender: TObject);
begin
if OpenDialog.Execute then
begin
edtDBName.Text :=OpenDialog.FileName;
gSourceDBName :=OpenDialog.FileName;
end;
end;procedure TConvertDataForm.btnTestConnectClick(Sender: TObject);
begin
if gSourceDBName='' then
begin
Application.MessageBox('请先设置FoxPro数据库!','提示',MB_IconInformation+MB_OK);
Exit;
end;
reg :=TRegistry.Create;
reg.RootKey :=HKEY_LOCAL_MACHINE;
if reg.OpenKey('Software\ODBC\ODBC.INI\TaxNetFox',True) then
reg.WriteString('SourceDB',gSourceDBName);
reg.CloseKey;
reg.Free;
try
dbSource.Close;
dbSource.Connected :=True;
except
dbSource.Connected :=False;
Application.MessageBox('数据库连接错误!','提示',MB_IconInformation+MB_OK);
Exit;
end;
dbSource.GetTableNames(clbTableName.Items,False);
rgSel.ItemIndex :=1;
end;procedure TConvertDataForm.btnApplyClick(Sender: TObject);
var
i :integer;
begin
if not IsSelected then Exit;
try
stbConStatus.SendToBack;
prbStatus.BringToFront;
prbStatus.Position :=0;
for i:=0 to clbTableName.Items.Count-1 do
begin
stbConStatus.Panels[0].Text :='正在转换:'+clbTableName.Items.Strings[i];
if clbTableName.Checked[i] then
begin
if tblSource.Active then
tblSource.Close;
if tblDestination.Active then
tblDestination.Close;
tblSource.TableName:=clbTableName.Items.Strings[i];
tblDestination.TableName :='tmp_'+ChangeFileExt(ExtractFileName(gSourceDBName),'')
+'_'+clbTableName.Items.Strings[i];
tblSource.Open;
btmConvertData.Mode :=batCopy;
btmConvertData.Source :=tblSource;
btmConvertData.Destination :=tblDestination;
btmConvertData.Execute;
end
else
continue;
prbStatus.Position :=prbStatus.Position+(clbTableName.Items.Count Div 100);
Application.ProcessMessages;
end;
prbStatus.Position :=0;
stbConStatus.Panels[1].Text :='数据库别名: Visual FoxPro为TaxNetFox,SQLServer为\TaxNet!';
Application.MessageBox('转换成功!','提示',MB_IconInformation+MB_OK);
except
prbStatus.Position :=0;
stbConStatus.Panels[1].Text :='转换失败';
Application.MessageBox('转换失败!','提示',MB_IconInformation+MB_OK);
end;
stbConStatus.BringToFront;
prbStatus.SendToBack;
end;function TConvertDataForm.IsSelected: boolean;
var
i :integer;
begin
Result :=False;
for i :=0 to clbTableName.Items.Count-1 do
begin
if clbTableName.Checked[i] =False then
Result :=False
else
begin
Result :=True;
Exit;
end;
end;
end;procedure TConvertDataForm.clbTableNameClick(Sender: TObject);
begin
if tblSource.Active then
tblSource.Close;
tblSource.TableName :=clbTableName.Items.Strings[clbTableName.ItemIndex];
tblSource.Open;
stbConStatus.Panels[0].Text :='记录数:'+IntToStr(tblSource.RecordCount);
end;procedure TConvertDataForm.btnConfigClick(Sender: TObject);
begin
WinExec('RunDLL32.exe Shell32.dll,Control_RunDLL ODBCCP32.CPL',SW_SHOWNORMAL);
end;end.
http://www.csdn.net/develop/read_article.asp?Id=18623Transact-SQL语句进行导入导出:1. 在SQL SERVER里查询access数据:-- ======================================================SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名------------------------------------------------------------------------------------------------- 2. 将access导入SQL server -- ======================================================在SQL SERVER 里运行:SELECT *INTO newtableFROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名------------------------------------------------------------------------------------------------- 3. 将SQL SERVER表里的数据插入到Access表中-- ======================================================在SQL SERVER 里运行:insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名 (列名1,列名2)select 列名1,列名2 from sql表 实例:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\db.mdb';'admin';'', Test) select id,name from Test INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)SELECT *FROM sqltablename-------------------------------------------------------------------------------------------------
注意这里的目录指的是SQL Server运行所在的机器~~