如何在Access数据库czb.mdb中加入一个表？

Question

需要在Access数据库czb.mdb中加入一个表名为sbxt.这个表的结构如下：字段名　　字段类型　字段长度　小数位数
a1        字符　　　　20
a2        数值型　　　10       2
a3        布尔型　　　
a4        文件型请使用SQL语句实现，谢谢各位的帮助

Answer 1

//stable  表name
//scommand  sql语句
scommand:='CREATE TABLE '+stable+
            ' (A1 TEXT(20) NOT NULL PRIMARY KEY,A2 TEXT(100),A2_1 SMALLINT,'+
            'A3 TEXT(9),A4 TEXT(9),A5 TEXT(9),A6 TEXT(9),A7 TEXT(9),A8 TEXT(9),A9 TEXT(9)'+
            ',A10 TEXT(4),A13 SMALLINT,A14 MEMO,E1 DATETIME,E2 TEXT(100),'+
            'E2_1 TEXT(100),E3 SMALLINT,E4 SMALLINT,jx MEMO,a15 MEMO,csbs TEXT(2),'+
            'F1 SMALLINT)';
  try
    DM1.ADOCn1.Execute(scommand);
  except
    showmessage('建立数据表时发生错误！');
  end;

Answer 2

A1 TEXT(20) NOT NULL PRIMARY KEY     //A1字符字段 长度20 不能为空 主键
其他类似

Answer 3

with adoquery do
begin
  close;
  sql.clear;
  sql.add('alter table sbxt add a1 varchar(20),a2 numeric(10,2),a3 blooen,
          a4 text');
  try
    execsql;
  execpt
    showmessage('该表已经存在');
  end;
end;

Answer 4

procedure dbgridtoacess;
var
 dbname,tablename:string;
 opendlg: Topendialog;
 sql1,sql2,sql3,sql4:string;
 i:integer;
begin
 opendlg:=Topendialog.Create(nil);
 opendlg.InitialDir:=extractfilepath(application.ExeName);
 opendlg.Title:='请选择输出的Acess数据库名:';
 opendlg.Execute;
 if (opendlg.FileName<>'') and (ExtractFileExt(opendlg.FileName)='.mdb') then dbname:=trim(opendlg.FileName)
 else exit;
 opendlg.Free;
 fm_bf.adocn.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+dbname+';Persist Security Info=False';
 fm_bf.adocn.LoginPrompt:=false;
 fm_bf.adocn.Connected:=true;
 for i:=0 to fm_main.DBGrid.FieldCount-1 do
 begin
   case fm_main.DBGrid.Fields[i].DataType of
    ftstring:tablename:=tablename+fm_main.DBGrid.DataSource.DataSet.Fields[i].FieldName+' varchar('+inttostr(fm_main.DBGrid.Fields[i].DataSize)+'),';
    ftInteger:tablename:=tablename+fm_main.DBGrid.DataSource.DataSet.Fields[i].FieldName+' int('+inttostr(fm_main.DBGrid.Fields[i].DataSize)+'),';
    ftfloat:tablename:=tablename+fm_main.DBGrid.DataSource.DataSet.Fields[i].FieldName+' float('+inttostr(fm_main.DBGrid.Fields[i].DataSize)+'),';
    ftMemo:tablename:=tablename+fm_main.DBGrid.DataSource.DataSet.Fields[i].FieldName+' text'+',';
    ftDateTime:tablename:=tablename+fm_main.DBGrid.DataSource.DataSet.Fields[i].FieldName+' datetime'+',';
    ftCurrency:tablename:=tablename+fm_main.DBGrid.DataSource.DataSet.Fields[i].FieldName+' money('+',';
    ftblob:tablename:=tablename+fm_main.DBGrid.DataSource.DataSet.Fields[i].FieldName+' image'+',';
   else
     tablename:=tablename+fm_main.DBGrid.DataSource.DataSet.Fields[i].FieldName+' varchar('+inttostr(fm_main.DBGrid.Fields[i].DataSize)+'),';
   end;
   // tablename:=tablename+fm_main.DBGrid.DataSource.DataSet.Fields[i].FieldName+' varchar(50)'+',';
 end;
 sql1:='create table '+fm_main.TreeView1.Selected.Text+'('+copy(tablename,1,length(tablename)-1)+')';
 sql2:='drop table '+fm_main.TreeView1.Selected.Text;
 sql3:='select * from '+fm_main.TreeView1.Selected.Text;
{ if fm_bf.exesql(sql3,0) then
 begin
  fm_bf.exesql(sql2,1);
  fm_bf.exesql(sql1,1);
 end
 else }
  fm_bf.exesql(sql1,1);
{ sql1:='select * from  '+fm_main.TreeView1.Selected.Text;
 fm_bf.ado_zj.Close;
 fm_bf.ado_zj.SQL.Clear;
 fm_bf.ado_zj.SQL.Text:=sql1;
 fm_bf.ado_zj.Open;
 fm_main.executesql(sql1,1);
 fm_bf.ado_bf.Close;
 fm_bf.ado_bf.SQL.Clear;
 sql1:='select * into '+fm_main.TreeView1.Selected.Text+' from ';
 sql1:=sql1+' OPENROWSET(''SQLOLEDB'''+',';
 sql1:=sql1+'''coolfilm'';';
 sql1:=sql1+'''sa'';''781213'',';
 sql1:=sql1+'''SELECT * FROM cz.dbo.ybbm'')';
 sql2:='select * into '+fm_main.TreeView1.Selected.Text+' from  vvv';
 fm_bf.ado_bf.SQL.Text:=sql1;
 sql1:='insert into '+fm_main.TreeView1.Selected.Text+' select * from ';
 sql1:=sql1+' OPENROWSET(''SQLOLEDB'''+',';
 sql1:=sql1+'''coolfilm'';';
 sql1:=sql1+'''sa'';''13'',';
 sql1:=sql1+'''SELECT * FROM cz.dbo.ybbm'')';
 fm_bf.ado_bf.SQL.Text:=sql1;
 fm_bf.ado_bf.ExecSQL; }
 //fm_bf.adocn.Connected:=false;
 sql4:='insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'''+','''+dbname+''';''admin'''+';'''''+','+fm_main.TreeView1.Selected.Text+') '
      +'select * from '+fm_main.TreeView1.Selected.Text;
 fm_main.executesql(sql4,1);
end;