数据库互导

1.前一段去兰州出差了，所以有些问题没法及时回答，抱歉！
2.明天去泰港澳游了，高兴，发一段代码上来供大家参考！
3.以前测试过，大部分可以用，但也不敢担保一定可以正常运行，我现在也没有环境重新测试，只好你们自已试试了，有些参数自已要改的，我就不写了，有什么问题等半个月后我再来看看！

[FRM]
object Form1: TForm1
  Left = 194
  Top = 126
  Width = 544
  Height = 375
  Caption = '异构数据库数据互导'
  Color = clBtnFace
  Font.Charset = GB2312_CHARSET
  Font.Color = clWindowText
  Font.Height = -12
  Font.Name = '宋体'
  Font.Style = []
  OldCreateOrder = False
  OnCloseQuery = FormCloseQuery
  PixelsPerInch = 96
  TextHeight = 12
  object labTime: TLabel
    Left = 48
    Top = 312
    Width = 6
    Height = 12
  end
  object GroupBox1: TGroupBox
    Left = 8
    Top = 8
    Width = 220
    Height = 153
    Caption = ' Access '
    TabOrder = 0
    object Button1: TButton
      Left = 15
      Top = 23
      Width = 90
      Height = 25
      Caption = 'Access->TXT'
      TabOrder = 0
      OnClick = Button1Click
    end
    object Button3: TButton
      Left = 15
      Top = 55
      Width = 90
      Height = 25
      Caption = 'Access->DBF'
      TabOrder = 1
      OnClick = Button3Click
    end
    object Button4: TButton
      Left = 15
      Top = 120
      Width = 90
      Height = 25
      Caption = 'Access->Access'
      TabOrder = 3
      OnClick = Button4Click
    end
    object Button2: TButton
      Left = 112
      Top = 23
      Width = 90
      Height = 25
      Caption = 'TXT->Access'
      TabOrder = 4
      OnClick = Button2Click
    end
    object Button5: TButton
      Left = 112
      Top = 55
      Width = 90
      Height = 25
      Caption = 'DBF->Access'
      TabOrder = 5
      OnClick = Button5Click
    end
    object Button9: TButton
      Left = 15
      Top = 88
      Width = 90
      Height = 25
      Caption = 'Access->Excel'
      TabOrder = 2
      OnClick = Button9Click
    end
    object Button10: TButton
      Left = 112
      Top = 88
      Width = 90
      Height = 25
      Caption = 'Excel->Access'
      TabOrder = 6
      OnClick = Button10Click
    end
  end
  object GroupBox2: TGroupBox
    Left = 256
    Top = 8
    Width = 217
    Height = 153
    Caption = ' Oracle '
    TabOrder = 1
    object Button6: TButton
      Left = 16
      Top = 88
      Width = 90
      Height = 25
      Caption = 'Oracle->Access'
      TabOrder = 2
      OnClick = Button6Click
    end
    object Button7: TButton
      Left = 16
      Top = 23
      Width = 90
      Height = 25
      Caption = 'Oracle->TXT'
      Font.Charset = GB2312_CHARSET
      Font.Color = clWindowText
      Font.Height = -12
      Font.Name = '宋体'
      Font.Style = []
      ParentFont = False
      TabOrder = 0
      OnClick = Button7Click
    end
    object Button8: TButton
      Left = 16
      Top = 120
      Width = 90
      Height = 25
      Caption = 'Oracle->Excel'
      TabOrder = 3
      OnClick = Button8Click
    end
    object Button11: TButton
      Left = 16
      Top = 55
      Width = 90
      Height = 25
      Caption = 'Oracle->DBF'
      TabOrder = 1
      OnClick = Button11Click
    end
    object Button12: TButton
      Left = 112
      Top = 23
      Width = 90
      Height = 25
      Caption = 'TXT->Oracle'
      Font.Charset = GB2312_CHARSET
      Font.Color = clWindowText
      Font.Height = -12
      Font.Name = '宋体'
      Font.Style = []
      ParentFont = False
      TabOrder = 4
      OnClick = Button12Click
    end
    object Button13: TButton
      Left = 112
      Top = 55
      Width = 90
      Height = 25
      Caption = 'DBF->Oracle'
      TabOrder = 5
      OnClick = Button13Click
    end
    object Button14: TButton
      Left = 112
      Top = 87
      Width = 90
      Height = 25
      Caption = 'Access->Oracle'
      TabOrder = 6
      OnClick = Button14Click
    end
    object Button15: TButton
      Left = 112
      Top = 120
      Width = 90
      Height = 25
      Caption = 'Excel->Oracle'
      TabOrder = 7
      OnClick = Button15Click
    end
  end
  object StatusBar: TStatusBar
    Left = 0
    Top = 329
    Width = 536
    Height = 19
    Panels = <
      item
        Text = '耗时:'
        Width = 32
      end
      item
        Width = 100
      end
      item
        Width = 50
      end>
    SimplePanel = False
  end
  object Button16: TButton
    Left = 24
    Top = 176
    Width = 90
    Height = 25
    Caption = 'TXT->DBF'
    TabOrder = 3
    OnClick = Button16Click
  end
  object AccessConnection: TADOConnection
    ConnectionString =
      'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=E:/Delp' +
      'hilx/ADOSQL/PH/demo.mdb;Persist Security Info=True'
    LoginPrompt = False
    Mode = cmShareDenyNone
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    OnExecuteComplete = AccessConnectionExecuteComplete
    OnWillExecute = AccessConnectionWillExecute
    Left = 228
    Top = 95
  end
  object ExcelConnection: TADOConnection
    ConnectionString =
      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:/aaa.xls;Extended' +
      ' Properties=Excel 8.0;Persist Security Info=False'
    LoginPrompt = False
    Mode = cmShareDenyNone
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    OnExecuteComplete = AccessConnectionExecuteComplete
    OnWillExecute = AccessConnectionWillExecute
    Left = 228
    Top = 128
  end
  object TxtConnection: TADOConnection
    ConnectionString =
      'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=f:/temp' +
      ';Extended Properties="text;HDR=YES;FMT=Delimited";Persist Securi' +
      'ty Info=True'
    LoginPrompt = False
    Mode = cmShareDenyNone
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    OnExecuteComplete = AccessConnectionExecuteComplete
    OnWillExecute = AccessConnectionWillExecute
    Left = 228
    Top = 31
  end
  object DBFConnection: TADOConnection
    ConnectionString =
      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:/;Extended Proper' +
      'ties=DBase 5.0;Persist Security Info=False'
    LoginPrompt = False
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    OnExecuteComplete = AccessConnectionExecuteComplete
    OnWillExecute = AccessConnectionWillExecute
    Left = 228
    Top = 63
  end
  object OracleConnection: TADOConnection
    ConnectionString =
      'Provider=MSDASQL.1;Password=bklskf;Persist Security Info=True;Us' +
      'er ID=bklskf;Data Source=bkls'
    Provider = 'MSDASQL.1'
    Left = 228
    Top = 161
  end
end

[Unit]
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, ComCtrls;

type
  TForm1 = class(TForm)
    AccessConnection: TADOConnection;
    GroupBox1: TGroupBox;
    Button1: TButton;
    Button3: TButton;
    Button4: TButton;
    Button2: TButton;
    Button5: TButton;
    GroupBox2: TGroupBox;
    Button6: TButton;
    Button7: TButton;
    labTime: TLabel;
    StatusBar: TStatusBar;
    ExcelConnection: TADOConnection;
    Button8: TButton;
    TxtConnection: TADOConnection;
    Button9: TButton;
    Button10: TButton;
    Button11: TButton;
    DBFConnection: TADOConnection;
    Button12: TButton;
    Button13: TButton;
    Button14: TButton;
    Button15: TButton;
    OracleConnection: TADOConnection;
    Button16: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure AccessConnectionExecuteComplete(Connection: TADOConnection;
      RecordsAffected: Integer; const Error: Error;
      var EventStatus: TEventStatus; const Command: _Command;
      const Recordset: _Recordset);
    procedure AccessConnectionWillExecute(Connection: TADOConnection;
      var CommandText: WideString; var CursorType: TCursorType;
      var LockType: TADOLockType; var CommandType: TCommandType;
      var ExecuteOptions: TExecuteOptions; var EventStatus: TEventStatus;
      const Command: _Command; const Recordset: _Recordset);
    procedure Button7Click(Sender: TObject);
    procedure Button8Click(Sender: TObject);
    procedure Button9Click(Sender: TObject);
    procedure Button10Click(Sender: TObject);
    procedure Button11Click(Sender: TObject);
    procedure Button12Click(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure Button13Click(Sender: TObject);
    procedure Button14Click(Sender: TObject);
    procedure Button15Click(Sender: TObject);
    procedure Button16Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1             : TForm1;
  sSql              : string;
  iT1, iT2          : integer;

implementation

{$R *.dfm}

procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  TxtConnection.Close;
  DBFConnection.Close;
  AccessConnection.Close;
  ExcelConnection.Close;
end;

procedure TForm1.AccessConnectionExecuteComplete(Connection: TADOConnection;
  RecordsAffected: Integer; const Error: Error;
  var EventStatus: TEventStatus; const Command: _Command;
  const Recordset: _Recordset);
begin
  //记时1
  iT2 := GetTickCount;
  StatusBar.Panels[1].Text := FormatFloat('#,##', iT2 - iT1) + '毫秒';
  StatusBar.Panels[2].Text := '共导记录:' + IntToStr(RecordsAffected) + '条';
end;

procedure TForm1.AccessConnectionWillExecute(Connection: TADOConnection;
  var CommandText: WideString; var CursorType: TCursorType;
  var LockType: TADOLockType; var CommandType: TCommandType;
  var ExecuteOptions: TExecuteOptions; var EventStatus: TEventStatus;
  const Command: _Command; const Recordset: _Recordset);
begin
  iT1 := GetTickCount;
end;

//=================================================================
//Access
//Access->TXT
procedure TForm1.Button1Click(Sender: TObject);
begin
  sSql := 'select * into [Text;Database=f:/].aaa.txt from demo';
  with AccessConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//Access->DBF
procedure TForm1.Button3Click(Sender: TObject);
begin
  sSql := 'select * into aaa in ''f:/'' ''dbase 5.0;'' from demo';
  with AccessConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//Access->Excel
//注意:前一个aaa为Excel文件中的aaa页,后一个aaa为Access文件demo.mdb中的一个表名
procedure TForm1.Button9Click(Sender: TObject);
begin
  sSql :=
    'select * into aaa from aaa in ''E:/Delphilx/ADOSQL/PH/demo.mdb''';
  with ExcelConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//Access->Access
procedure TForm1.Button4Click(Sender: TObject);
begin
  sSql := 'select * into aaa from demo in ''E:/Delphilx/ADOSQL/PH/demo.mdb''';
  with AccessConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//TXT->Access
procedure TForm1.Button2Click(Sender: TObject);
begin
  sSql := 'select * into uform from [Text;Database=f:/].form.txt';
  with AccessConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//DBF->Access
procedure TForm1.Button5Click(Sender: TObject);
begin
  sSql := 'select * into aaa from aaa in ''f:/'' ''dbase 5.0;''';
  with AccessConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//Excel->Access
//[aaa$]->Excel文件aaa.xls中的aaa页
procedure TForm1.Button10Click(Sender: TObject);
begin
  sSql :=
    'select * into bbb from [excel 8.0;database=f:/aaa.xls].[aaa$]';
  with AccessConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//=================================================================
//Oracle
//Oracle->Access
procedure TForm1.Button6Click(Sender: TObject);
begin
  sSql :=
    'select * into ouform from ouform in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bklskf;PWD=bklskf;SERVER=bkls;]';
  with AccessConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//Oracle->TXT
procedure TForm1.Button7Click(Sender: TObject);
begin
  sSql := 'select VGH,VXM,VMM into lsygb.txt from (select * from lsygb in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=BKLS;PWD=BKLS;SERVER=BKLS;])';
//  sSql :=
//    'select * into form.txt from ouform in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bklskf;PWD=bklskf;SERVER=bkls;]';
  with TxtConnection do
    begin
      Close;
      Connected := True;
      Execute(sSql);
    end;
end;

//Oracle->Excel
procedure TForm1.Button8Click(Sender: TObject);
begin
  sSql :=
    'select * into aaa from lsygb in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;]';
  with ExcelConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//Oracle->DBF
procedure TForm1.Button11Click(Sender: TObject);
begin
  sSql :=
    'select * into aaa from lsygb in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;]';
  with DBFConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//TXT->Oracle
//Oracle数据库注意要将表名大写!不然创建出来的表可以看到表名但无法打开
procedure TForm1.Button12Click(Sender: TObject);
begin
  sSql := 'select * into PH_TXT in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from ph_txt.txt';
  with TxtConnection do
    begin
      Close;
      Connected := True;
      Execute(sSql);
    end;
end;

//DBF->Oracle
procedure TForm1.Button13Click(Sender: TObject);
begin
  sSql :=
    'select * into PH_DBF in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from aaa';
  with DBFConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//Access->Oracle
procedure TForm1.Button14Click(Sender: TObject);
begin
  sSql :=
    'select * into PH_ACCESS in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from aaa';
  with AccessConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

//Excel->Oracle
procedure TForm1.Button15Click(Sender: TObject);
begin
//  'select * into MLB in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=BKLS;PWD=BKLS;Server=BKLS;] from MLB'
//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:/aaa.xls;Extended Properties=Excel 8.0;Persist Security Info=False
  sSql :=
    'select * into PH_EXCEL in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from aaa';
  with ExcelConnection do
    begin
      Connected := True;
      Execute(sSql);
    end;
end;

procedure TForm1.Button16Click(Sender: TObject);
begin
  sSql := 'select * into qxb in ''f:/'' ''dbase 5.0;'' from lsqxb.txt';
  with TXTConnection do
    begin
//      Close;
//      ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=f:/temp;Extended Properties="text;HDR=YES;FMT=Delimited";Persist Security Info=True';
      Connected := True;
      Execute(sSql);
    end;
end;

end.