不同数据库之间表的复制问题

Question

我在delphi 6中，用ADOQuery连接ACCESS,用ADOTable连接EXCLE,连接方式都是Microsoft.Jet.OLEDB.4.0，我想把EXCLE表中的内容复制到ACCESS中的一个表，该怎样做？（最好有例子）如果将文本文件的内容复制到ACCESS中的另一个表，又该怎样做？

Answer 1

你是把excel的数据 导入到access吗 怎么你又说得是将文本文件的内容复制到ACCESS中的另一个表

Answer 2

这有两个问题，第一是想问怎样把excel的数据导入到access；第二是想问怎样把文本文件的数据导入到access

Answer 3

关注!!我也遇到样的问题不过用了ADOCONNECTION 解决了.不知道各位有没有更好的解决方案

Answer 4

//这是我一个程序中的几个函数,你修改一下也许有用function TForm_Excel.DatasetToExcel(DataSet: TDataSet; strTab: string;
  sstrAimField: TStrings; sstrSField: TStrings; ProgressBar: TTeThemeProgressBar;
  nOutPutType: integer = 1; bShowExcel: boolean = True; strFileName: string = '';
  const FileFormat: TFileFormat = ffXLS; EType: TExportType = newExcel): boolean;
var
  nCol, nRow: integer;
  i, n, nRecNo, nRecCur: integer;
  strFieldValue, strFieldName, strAimFieldName: string;
  bOk: boolean;
  bInitError: boolean;
begin
  timer1.Enabled := True;
  Result := False;
  bOk := False;
  // nOutPutType:=1;
  try
    if not DataSet.Active then
      DataSet.Open;
  except
    ShowMessage('数据源错误');
    exit;
  end;  try
    if not InitExcelObj(strTab, strFileName, EType) then
    begin
      if EType <> ActiveExcel then        //如果不是导至当前
        ExcelApplication1.Quit;
      Excelworksheet1.disconnect;
      Excelworkbook1.disconnect;
      ExcelApplication1.Disconnect;      if EType <> ActiveExcel then
        raise EADOError.Create('新建页面失败,Excel可能被非正常关闭或是没有安装Excel2000/98!')
      else
        raise EADOError.Create('当前没有被激活的Excel表,无法导出');
      // raise EADOError.Create('');    
    end;
    
    nRecNo := dataSet.RecNo;
    if (sstrAimField = nil) or (sstrSField = nil) then
    //如果目标表字段不指定,则源表字段也不能指定
    begin
      if sstrAimField <> nil then
        sstrAimField.Clear
      else
        sstrAimField := TStringList.Create;      if sstrSField <> nil then
        sstrSField.Clear
      else
        sstrSField := TStringList.Create;      for i := 0 to DataSet.FieldCount - 1 do
      begin
        sstrSField.Add(DataSet.Fields[i].FieldName);
        sstrAimField.Add(DataSet.Fields[i].FieldName);
      end;
    end;
    //
    nCol := sstrSField.Count;
    nRow := DataSet.RecordCount;
    case nOutPutType of
      1: DataSet.First;          //导出所有数据
      2, 3: nRow := 1;               //导出当前一条数据    //仅导出数据表结构
    end;    ProgressBar.Max := nRow;
    DataSet.DisableControls;
    if strTab <> '' then
      ShowMsg('正在向Excel导出-' + strTab + '...')
    else
      ShowMsg('正在向Excel导出数据...');
    for i := 1 to nRow do
    begin
      ProgressBar.Position := i;            //progress
      ShowMsg('正在导出第' + IntToStr(i) + '条记录');
      for n := 1 to nCol do                                 //填入数据
      begin
        //strFieldName:=DataSet.Fields[n-1].FieldName;
        strFieldName := sstrSField.Strings[n - 1];
        strAimFieldName := sstrAimField.Strings[n - 1]; 
        if i = 1 then                                     //填入结构
          ExcelWorksheet1.Cells.Item[i, n] := strAimFieldName;
        try
          if (nOutPutType <> 3) then
          begin
            //strFieldValue:=DataSet.fieldbyname(strFieldName).AsString;
            //ExcelWorksheet1.Cells.Item[i+1,n]:=strFieldValue;
            ExcelWorksheet1.Cells.Item[i + 1, n] :=
              DataSet.FieldByName(strFieldName).AsVariant;
          end;
        except
        end; 
      end;
      DataSet.Next;
      if bTerminate then break;                 //中断
    end;    bTerminate := False;   //reset bTerminate
    Result := True;    //调整Sheet格式
    with   ExcelWorksheet1.Range[ExcelWorksheet1.Cells.Item[1, 1],
      ExcelWorksheet1.Cells.Item[1, nCol]] do
    begin
      font.Bold := True;
      Borders.LineStyle := xlContinuous;
      Interior.ColorIndex := 44;
      Interior.Pattern := xlSolid;
      HorizontalAlignment := xlCenter;
    end;
    //    ExcelWorksheet1.Range[ExcelWorksheet1.Cells.Item[2,1],ExcelWorksheet1.Cells.Item[2,1]].Activate;
    ExcelWorksheet1.Cells.Select;
    ExcelWorksheet1.Cells.Font.Size := 10;
    ExcelWorksheet1.Cells.EntireColumn.AutoFit;
    ExcelWorksheet1.Cells.Item[2, 1].select;
    ExcelApplication1.ActiveWindow.FreezePanes := True;
    
    if bShowExcel then
    begin
      ExcelApplication1.ScreenUpdating[LCID] := True;
      ExcelApplication1.Visible[LCID] := True;
    end
    else
    begin                //自动保存
      try
        if ExcelApplication1.Workbooks.Count = 0 then
          raise ENoActiveWorkbook.Create('没有打开的Excel,无法导出数据');
        ExcelApplication1.DisplayAlerts[LCID] := False;
        case FileFormat of                    // Export data to a file    
          ffXLS: ExcelWorksheet1.SaveAs(StrFileName);
          // Excel 2000/XP format
          ffXL97: ExcelWorksheet1.SaveAs(StrFileName, XlFileFormat(xlExcel9795));
          // Excel 95 and 97 compatible format
          ffCSV: ExcelWorksheet1.SaveAs(StrFileName, XlFileFormat(xlCSV));
          ffHTM: ExcelWorksheet1.SaveAs(StrFileName, XlFileFormat(xlHtml));
          // Only works with Excel 2000/XP
        end;
      finally     
      end;
    end;
    bOk := True;
    
  finally
    //reset dataset
    DataSet.EnableControls;
    nRecCur := DataSet.RecNo;
    DataSet.MoveBy(nRecNo - nRecCur);
    if bOk then
      ShowMsg('数据导出成功,共消耗时间' + IntToStr(nTime) + '秒');
    teStop.Caption := '关闭(&S)';
    Timer1.Enabled := False;
      
    if not bShowExcel then
      ExcelApplication1.Quit;
    Excelworksheet1.disconnect;
    Excelworkbook1.disconnect;
    ExcelApplication1.Disconnect;
  end;
end;

Answer 5

//*************************************************function TForm_Excel.DatasetToSQL(var AimConnection: TAdoConnection;
  strAimTabName: string; sstrAimField: TStrings;
  DataSet: TDataSet; strTab: string; sstrSField: TStrings;
  nType: integer = 1): boolean;
var
  nCol, nRow: integer;
  i, n, nRecNo, nRecCur: integer;
  strFieldValue, strFieldName: string;
  bOk: boolean;
  nOutPutType: integer;
  strFields: string;
  strSField, strAimField: string;
  AdoTab: TAdoTable;
begin
  Result := False;
  timer1.Enabled := True;
  bOk := False;
  nOutPutType := nType;
  try                       //检测源                 !! DataSet与AimConnection可能不是同一数据库
    if not DataSet.Active then
      DataSet.Open;
  except
    ShowMessage('数据源错误,请确认数据是否准备好!');
    exit;
  end;  try                         //检测连接
    if not AimConnection.Connected then
      AimConnection.Connected := True;
  except
    ShowMessage('数据链接错误,请确认需要导出的目标数据库是否准备好!');
    exit;
  end;  try
    AdoTab := TAdoTable.Create(self);
    AdoTab.Connection := AimConnection;
    AdoTab.TableName := strAimTabName;
    AdoTab.Open;
  except
    ShowMessage('目标数据错误,无法找到指定的表');
    exit;
  end;     nCol := sstrSField.Count;
  nRecNo := dataSet.RecNo;
  nRow := DataSet.RecordCount;
  case nType of
    1:
      DataSet.First;
    2, 3:
      nRow := 1; //仅导出结构或是当前一条记录
  end;  if teRadioToLocal.Checked then
    ShowMsg('将' + strTab + teRadioToLocal.Caption);
  if teRadioToRemote.Checked then
    ShowMsg('将' + strTab + teRadioToRemote.Caption);
  tePrgBar1.Max := nRow;  //  strFields:='';
  //  for i:=0 to sstrField.Count-1 do
  //   begin
  //      if sstrField.Strings[i]<>'' then
  //          strFields:= strFields+','+ sstrField.Strings[i];
  //   end;  try
    DataSet.DisableControls;
    for i := 1 to nRow do
    begin
      tePrgBar1.Position := i;            //progress        //  ProgressBar.Update;
      AdoTab.Append;
      for n := 1 to nCol do                                 //填入数据
      begin
        //strFieldName:=DataSet.Fields[n-1].FieldName;
        strAimField := sstrAimField.Strings[n - 1];
        strSField := sstrSField.Strings[n - 1];
        if (nOutPutType <> 3) then
        begin
          // strFieldValue:=DataSet.fieldbyname(strFieldName).AsString;
          // AdoTab.FieldByName(strFieldName).AsString:=strFieldValue;
          try
            if AdoTab.FieldByName(strAimField).DataType <> ftAutoInc then
              //非只读字段
              //    AdoTab.FieldByName(strAimField).AsString :=DataSet.fieldbyname(strSField).AsString;
              AdoTab.FieldByName(strAimField).AsVariant :=
                DataSet.FieldByName(strSField).AsVariant;
          except
          end;       
        end;
      end;
      DataSet.Next;
    end;
    if nOutPutType <> 3 then
    begin
      AdoTab.Post;
      if AdoTab.RecordCount > 0 then 
        AdoTab.First;
    end; 
    Result := True;
  finally
    //error message
    Timer1.Enabled := False;
    DataSet.EnableControls;
    nRecCur := DataSet.RecNo;
    DataSet.MoveBy(nRecNo - nRecCur);
  end;
end;

Answer 6

//******************************
function TForm_Excel.InitExcelObj(strTab: string; strFileName: string = '';
  EType: TExportType = newExcel): boolean;
var
  Temp_Worksheet: _WorkSheet;
  IntIndex: integer;
begin
  ShowMsg('正在新建Excel对象..');
  LCID := LOCALE_USER_DEFAULT;       //GetUserDefaultLCID;
  case ETYpe of
    newExcel, FileExcel:
      begin
        try
          if not FileExists(strFileName) then                 //新文件
          begin
            ExcelApplication1.ConnectKind := ckNewInstance;
            ExcelApplication1.Connect;
            ExcelApplication1.Caption := strTab; 
            ExcelWorkBook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam, 0));
            //  if strTab<>'' then
            //       ExcelWorkBook1.Title[1]:=strTab;
            //    ExcelApplication1.ScreenUpdating[0]:=true;
            Temp_Worksheet := ExcelWorkbook1.WorkSheets.Add(EmptyParam, EmptyParam,
              EmptyParam, EmptyParam, LCID) as _WorkSheet;//?!
            ExcelWorkSheet1.ConnectTo(Temp_WorkSheet);
            if strTab <> '' then
              ExcelWorkSheet1.Name := strTab;
            Result := True;
          end
          else                               //加到存在的文件中去
          begin
            ExcelApplication1.ConnectKind := ckRunningOrNew;
            ExcelApplication1.Workbooks.Open(strFileName,
              EmptyParam, EmptyParam, EmptyParam, EmptyParam,
              EmptyParam, EmptyParam, EmptyParam, EmptyParam,
              EmptyParam, EmptyParam, EmptyParam, EmptyParam, 0);
            ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);
            //        ExcelApplication1.Caption:=strTab;
            IntIndex := GetIndexOfWorksheet(strTab);
            if IntIndex = -1 then
            begin
              ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets.Add(EmptyParam,
                EmptyParam, 1, TOleEnum(xlWBATWorksheet), LCID) as _Worksheet);
              if strTab <> '' then
                ExcelWorkSheet1.Name := strTab;                
            end
            else
              ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[IntIndex] as _Worksheet);            Result := True;
          end;    
        except
          //    ShowMessage('新建Excel页面失败,请确认是否正确安装Excel2000/98!');
          Result := False;
        end;
      end;    ActiveExcel:
      begin
        try
          ExcelApplication1.ConnectKind := ckRunningOrNew;
          ExcelApplication1.Connect;
          if ExcelApplication1.Workbooks.Count = 0 then
          begin
            raise ENoActiveWorkbook.Create('当前没有被激活的Excel表,无法导出');
          end;
          //    ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);
          ExcelWorksheet1.ConnectTo(ExcelApplication1.ActiveSheet as _Worksheet);
          Result := True;
        except
          Result := False;  
        end;    //try
      end;     //begin
  end;
end;