求救，如何将结构相同的Excel表数据导入到Access数据库中？谢谢

Question

要将Excel表中的数据追加至Access数据库中，如何实现？
要求Excel表头与Access数据表的结构相同，而且随时有很多Excel表需要追加到数据库中，不能影响已存在的数据！
高分回报！

Answer 1

把Excel导入到Access中,同txt类似
select * into <table name> from [excel 8.0;database=<filename>].[<sheetname>$]这个SQL就可以搞定了

Answer 2

要通过程序来控制，首先建立一个ODBC数据源，连接要导入数据的Access数据库，
然后在程序中控制Excel表，从中读取数据，一边读，一边写入数据库。下面给出一利用Delphi OLE自动化控制器操纵Excel的实例。 ---- 首先新建一Application， 然后在Form1上放置Servers栏控件ExcelApplication1、 ExcelWorkbook1、 ExcelWorksheet1， 再放置控件Table1、 Datasource1、 Dbgrid1、 Button1、 Button2、 Button3、 Button4， 并设置Table1.databasename:=dbdemos， Table1.TableName:=Country.db， Table1.active:=True, Button1.Caption:='SaveToExcel', Button2.caption:='PrintPreview', Button3.caption:='Print', Button4.caption:='CloseExcel'。代码如下： unit Unit1;interfaceuses
 Windows, Messages, SysUtils, Classes, 
 Graphics, Controls, 
 Forms, Dialogs, Excel97, OleServer, Db, DBTables,
 Grids, DBGrids, StdCtrls;type
 TForm1 = class(TForm)
  ExcelApplication1: TExcelApplication;
  ExcelWorkbook1: TExcelWorkbook;
  ExcelWorksheet1: TExcelWorksheet;
  Table1: TTable;
  Table1Name: TStringField;
  Table1Capital: TStringField;
  Table1Continent: TStringField;
  Table1Area: TFloatField;
  Table1Population: TFloatField;
  button1: TButton;
  DataSource1: TDataSource;
  DBGrid1: TDBGrid;
  Button2: TButton;
  Button3: TButton;
  Button4: TButton;
  procedure button1Click(Sender: TObject);
  procedure Button2Click(Sender: TObject);
  procedure Button3Click(Sender: TObject);
  procedure Button4Click(Sender: TObject);
 private
  { Private declarations }
 public
  { Public declarations }
 end;var
 Form1: TForm1;implementation{$R *.DFM}procedure TForm1.button1Click(Sender: TObject);
var
 i,row,column:integer;
begin
 Try
  ExcelApplication1.Connect;
 Except
  MessageDlg('Excel may not be installed',
  mtError, [mbOk], 0);
  Abort;
 End;
 ExcelApplication1.Visible[0]:=True;
 ExcelApplication1.Caption:='Excel Application';
 ExcelApplication1.Workbooks.Add(Null,0);
 ExcelWorkbook1.ConnectTo
 (ExcelApplication1.Workbooks[1]);
 ExcelWorksheet1.ConnectTo
 (ExcelWorkbook1.Worksheets[1] as _Worksheet);
 Table1.Open;
 row:=1;
 While Not(Table1.Eof) do
 begin
   column:=1;
   for i:=1 to Table1.FieldCount do
   begin
    ExcelWorksheet1.Cells.Item[row,column]:
=Table1.fields[i-1].AsString;
    column:=column+1;
   end;
   Table1.Next;
   row:=row+1;
 end;
end;procedure TForm1.Button2Click(Sender: TObject);
begin
ExcelWorksheet1.PrintPreview;
end;procedure TForm1.Button3Click(Sender: TObject);
begin
 ExcelWorksheet1.PrintOut;
end;procedure TForm1.Button4Click(Sender: TObject);
begin
 ExcelApplication1.Disconnect;
 ExcelApplication1.Quit;
end;end.

Answer 3

但是我不能只固定在一个Excel文件，有很多Excel表需要导入至Access数据库！

Answer 4

//以下是我的代码,好象没错了，但是运行时出错，请高手帮我调试一下，谢谢 ！需要添加的组建：//ADOConnection1,OpenDialog1,ExcelApplication1,ExcelWorksheet1,ExcelWorkbook1,Button1
unit Unit1;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, OleServer, Excel2000, Grids, DB, ADODB;type
  TForm1 = class(TForm)
    ExcelApplication1: TExcelApplication;
    Button1: TButton;
    OpenDialog1: TOpenDialog;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    ADOConnection1: TADOConnection;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure closedb(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
var i,j:integer;
begin
 opendialog1.InitialDir:=ExtractFileDir(paramstr(0));//文件的打存放初始路径
 opendialog1.Execute; Try
    ExcelApplication1.Connect;//EXCEL应用程序
  Except
    MessageDlg('Excel may not be installed',mtError, [mbOk], 0);
    Abort;
 End;
 ExcelApplication1.Visible[0]:=False;
 ExcelApplication1.Caption:='Excel Application';
 try
     excelapplication1.Workbooks.Open(opendialog1.FileName,
     null,null,null,null,null,null,null,null,null,null,null,null,0);//打开指定的EXCEL 文件
  except
   begin
    ExcelApplication1.Disconnect;//出现异常情况时关闭
    ExcelApplication1.Quit;showmessage('请选择EXCEL电子表格!');
    exit;
    end;
 end;
  ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);//ExcelWorkbook1与Eexcelapplication1建立连接
  ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1与Excelworkbook1建立连接
  //开始从EXCEL中取数
  for i:=0 to 1000 do ADOConnection1.Execute('Insert into yssj(IP地址，工作组,主机名,用户,MAC地址,姓名) values('+chr(39)+ExcelWorksheet1.Cells.Item[i+1,1]+chr(39)+','+chr(39)+ExcelWorksheet1.Cells.Item[i+1,2]+chr(39)+','+chr(39)+ExcelWorksheet1.Cells.Item[i+1,3]+chr(39)+','+chr(39)+ExcelWorksheet1.Cells.Item[i+1,4]+chr(39)+','+chr(39)+ExcelWorksheet1.Cells.Item[i+1,5]+chr(39)+'),'+chr(39)+ExcelWorksheet1.Cells.Item[i+1,6]+chr(39)+')');
  ExcelApplication1.Disconnect;
  ExcelApplication1.Quit;
end;procedure TForm1.FormCreate(Sender: TObject);
begin
   ADOConnection1.ConnectionString :='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+extractfilepath(application.ExeName)+'\data\result.mdb;Persist Security Info=False';
   ADOConnection1.Connected :=true;
end;procedure TForm1.closedb(Sender: TObject; var Action: TCloseAction);
begin
    ADOConnection1.Close ;
end;end.

Answer 5

1楼兄弟的方法会覆盖原先的数据，应该这样：
  
insert into <table name> select * from [excel 8.0;database=<filename>].[<sheetname>$]此方法效率极高，但要求你确保Excel表头与Access数据表的结构相同

Answer 6

看一看
http://bbs.2ccc.com/topic.asp?topicid=92461

Answer 7

open access x.x 菜单:  文件->获取外部数据->导入  [选择 *.xls] ->确定

Answer 8

adoconnection1.Connected:=false;
   ADOConnection1.ConnectionString:=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db.mdb;'
+'Persist Security Info=False';
ADOConnection1.Execute('select * into book FROM [excel 8.0;database=c:\mybook.xls].emtl');
adoconnection1.Connected:=true;select * into book FROM [excel 8.0;database=c:\mybook.xls].emtl    此SQL语句中book是可以任意取的，相当于ACCESS库的表，另外EMTL是EXCEL表MYBOOK工作簿的一个工作表相当于SHEET1。导出DBF
 ADOConnection1.Execute('Select * into aaa In "C:\" "dbase III;"  from AfterServiceTbl');
导出text
ADOConnection.Execute('Select * Into [Text;Database=c:\temp].aaaa.txt From aaaa');到出excell
DM.ADOCon.Execute('Select* Into Sheet1 In "'+Excel_Dialog.FileName+'" "Excel 8.0;" From ConsumerInfoTbl '+SelCondition1+ 'order by ConsumerID');

Answer 9

打开Acess:
   选择菜单:  文件->获取外部数据->导入  [选择 *.xls] ->确定
   再保存为MDB文件就OK了

Answer 10

jlee084(编程浪子) 
OK了
先用ADO连接好Access数据库
然后就用这条语句就可以了