怎样在程序中动态创建access表

Question

在线等

Answer 1

commandstr := 'CREATE TABLE total ( total numeric(8,2) )'; 
adocommand1.CommandText := commandstr; 
adocommand1.Execute;

Answer 2

procedure TForm1.CreateDataBase(Var
ServerHost,DataBaseDesc,UserID,Pass:String);
begin
  try   //Initial Catalog=UFSystem;Data Source=JIANGWEI
    AccConn.Connected:=False;
    AccConn.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;'+
      'User ID=' +UserID+ ';Data Source='+ServerHost;
   //-------密码一定不能为空！----------------------
  // if Trim(Pass) <> '' then
    // AccConn.ConnectionString := AccConn.ConnectionString +';Password='+Pass;
     AccConn.Connected := True;
  except
     MessageDlg('连接数据库错误，请检查连接字符串是否有误！', mtError,[mbOk],0);
     Exit;
  end;
  //-----------------------------------------
  try
    AccConn.Execute('Create Database ' + Trim(DatabaseDesc)); // Add Some Param
    MessageDlg('成功创建数据库！',mtConfirmation,[mbOk],0);
  except
    MessageDlg('创建数据库时出错！',mtError,[mbOK],0);
  end;
    AccConn.Connected := False; 
end;procedure TForm1.ExecSqlScript(Var ServerHost,DataBaseDesc,UserID,Pass:String);
var
  TempString,SQLFileName:String;
  SqlString:AnsiString;
  SqlFile : TextFile;
  CreateCount,i,j : Integer;
  TblStr,FldStr:TStringList;
  TmpStream : TMemoryStream;
begin
  SQLFileName := ExtractFilePath(ParamStr(0))+ 'Init.Sql';
  if not FileExists(SQLFileName) then begin
    MessageDlg('SQL执行脚本文件不存在！',mtError,[mbOk],0);
    Exit;
  end;
  FrmProcess:=TFrmprocess.Create(Self);
  FrmProcess.TransCount.MinValue:=0;
  FrmProcess.TransCount.MaxValue:=100;
  FrmProcess.Show;
  //打开SQL执行脚本文件
  try
    AssignFile(SqlFile,SQLFileName);
    Reset(SqlFile);
  except
    MessageDlg('打开SQL执行脚本文件时出错！',mtWarning ,[mbOk],0);
    FrmProcess.Close;
    Exit;
  end;
    FrmProcess.TransCount.Progress:=2;
  //连接数据库
  try
    AccConn.Connected := False;
    AccConn.ConnectionString := 'Provider=SQLOLEDB.1;Persist Security
Info=False;'+
      'User ID='+UserID+';Data Source='+ServerHost+';Initial
Catalog='+DatabaseDesc;
    if Trim(Pass) <> '' then
      AccConn.ConnectionString := AccConn.ConnectionString +
';password=' +Pass;
    //连接数据库
    AccConn.Connected := True;
    if Uppercase(Trim(AccConn.DefaultDatabase)) = 'MASTER' then
      if Application.Messagebox('真要在Master数据库上执行此操作吗？','警告
',mb_yesno+mb_iconInformation)=idno then begin
        CloseFile(SqlFile);
        AccConn.Connected := False;
        Exit;
      End;
  except
    MessageDlg('连接数据库错误，请检查数据库连接信息是否有误！
',mtError,[mbOk],0);
    CloseFile(SqlFile);
    Exit;
  end;
    FrmProcess.TransCount.Progress:=4;
  //读SQL执行脚本文件以确定有多少句Go语句 -->Step
  CreateCount := 0 ;
  try
    while not Eof(SqlFile) do begin
      ReadLn(SqlFile,TempString);
      if Uppercase(copy(TempString,1,2)) = 'GO' then
        Inc(CreateCount);
    end;
    Reset(SqlFile);
  except
    MessageDlg('读SQL执行脚本文件时出错！' ,mtError,[mbOK],0);
    CloseFile(SqlFile);
    AccConn.Connected := False;
    Exit;
  end;
    FrmProcess.TransCount.MaxValue:=Round(CreateCount*1.25);
    FrmProcess.TransCount.Progress:=Round(FrmProcess.TransCount.MaxV
alue*0.9)-CreateCount;
  //执行SQL语句
  AccConn.BeginTrans;
  try
    while not Eof(SqlFile) do begin
      SqlString :='';
      while not Eof(SqlFile) do begin
        ReadLn(SqlFile,TempString);
        if Copy(TempString,1,2) = 'GO' then Break;
        SqlString := SqlString + #13#10 + TempString;
      end;
      try
        //-----------------------------
        AccConn.Execute(SqlString);
        //----------------------------
      except
        if Application.MessageBox('初始化数据库出现错误，'+#13#10+'如果继续
执行可能导致以后使用有问题，继续执行吗？','提示
',mb_YesNo+MB_ICONINFORMATION)=IDNO then
          raise Exception.Create('事务执行错误！');
      end;
        if 
FrmProcess.TransCount.Progress<FrmProcess.TransCount.MaxValue 
then
           FrmProcess.TransCount.Progress:=FrmProcess.TransCount.Progres
s+1;
    end;
    AccConn.CommitTrans ;
  except
    AccConn.RollbackTrans ;
    FrmProcess.Close;
    MessageDlg('事务执行过程出现错误！',mtError,[mbOK],0);
    CloseFile(SqlFile);
    AccConn.Connected := False;
    Exit;
  end;
  //追加基本数据表数据----Adapt In 
2002-06-10---------vinson------------------
  Accconn.Connected:=False;
  Sleep(10);
  Accconn.Connected:=True;
  Accconn.BeginTrans;
  try
    // Dict-File
    SourceConn.ConnectionString:=
    'Provider=Microsoft.Jet.OLEDB.4.0;Persist Security 
Info=False;Data Source=' +
    GetCurrentDir +'\DbDict.IND';
    SourceConn.Connected:=True;
    TblStr:=TStringList.Create;
    FldStr:=TStringList.Create;
    SourceConn.GetTableNames(TblStr);
    FrmProcess.TransCount.Maxvalue:=Round(TblStr.Count * 12.5);
    FrmProcess.TransCount.Progress:=FrmProcess.TransCount.MaxValue-T
blStr.Count;
    TmpStream:=TMemoryStream.Create;
    for i := 0 to TblStr.Count - 1 do
    try
      FldStr.Clear;
      Accconn.GetFieldNames(TblStr[i],FldStr);
      tblsource.Active := False;
      tblsource.TableName := TblStr[i];
      tblsource.Active := True;
      tblDesc.Active := False;
      tblDesc.TableName:=TblStr[i];
      tblDesc.Active := True;
      tblsource.First;
      while not tblsource.Eof do begin
        tblDesc.Insert;
        for j := 0 to FldStr.Count -1 do
          if tblDesc.FieldByName(FldStr[j]).AutoGenerateValue = arNone 
then
          case tblDesc.FieldByName(FldStr[j]).DataType of
            ftBlob :
              tblDesc.FieldByName(FldStr[j]).Assign( tblsource.FieldByName
(FldStr[j]));
            ftString,ftWideString,ftMemo:
              tblDesc.FieldByName(FldStr[j]).AsString := 
Trim(tblsource.FieldByName(FldStr[j]).Asstring) ;
            ftBoolean:
              tblDesc.FieldByName(FldStr[j]).AsBoolean := 
tblsource.FieldByName(FldStr[j]).AsBoolean ;
            else
              tblDesc.FieldByName(FldStr[j]).Value := 
tblsource.FieldByName(FldStr[j]).Value ;
          end;
        tbldesc.Post;
        tblsource.Next;
      end;
      if 
FrmProcess.TransCount.Progress<FrmProcess.TransCount.MaxValue 
then
         FrmProcess.TransCount.Progress:=FrmProcess.TransCount.Progress
+1;
    except
    end;
    Accconn.CommitTrans;
    FrmProcess.TransCount.Progress:=FrmProcess.TransCount.MaxValue;
    FrmProcess.Close;
    Messagedlg('事务执行完毕，请设置帐套参数。',mtinformation,[mbOk],0);
  except
    Accconn.RollbackTrans;
    Messagedlg('事务执行过程当中出现错误！'+#13#10+'请与软件提供商联系。
',mtinformation,[mbOk],0);
  end;
  //===========================================================
  CloseFile(SqlFile);
  FrmProcess.Close;
  Accconn.Connected:=False;
  TblStr.Free;
  FldStr.Free;
  SourceConn.Connected:=False;
end;  

Answer 3

adoquery1.sql.clear;
adoquery1.sql.add('create table text (id int(2),userid varchar(20))');
adoquery1.execsql;