请问用delphi怎么样修改access数据库字段的长度?(100分)

G

gang18

Unregistered / Unconfirmed
GUEST, unregistred user!
请问怎么样用delphi修改原有access数据库中某个表中一个字段的长度?

谢谢!
 
有没有李维的那个ado/com+的书呢,上面有教用adox来做的,我现在手头上没书,只能告诉
你原理了。
先import adox控件(其实就是ado高版本带的了,只是我们很少用到罢了)
菜单-project-import type library
有几个叫adoext的东东,选中那个有2.6标志的(我机器的ado是2.6)
看到下面列出来一些class name没,这些和delphi自己的class有冲突,建议你在前面加个
adox吧。
TTable 改成tadoxtable
TColumn tadoxcolumn
TIndex
TKey
TGroup
TUser
TCatalog tadoxcolumn(上面也照着改)
再按install啦

下面是一段代码,你自己看着改吧,ok。
{
一个利用adox来建表和修改字段的小程序,
}
unit DbToolsFrm;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs
, ADOX_TLB, Db, ADODB, ExtCtrls, StdCtrls, ComCtrls;

type
TDbToolsForm = class(TForm)
ADOConnection: TADOConnection;
ButtonUpdate: TButton;
ButtonExit: TButton;
Bevel1: TBevel;
ListBoxDataBase: TListBox;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
ListBoxTables: TListBox;
ButtonConnect: TButton;
ButtonNewTable: TButton;
ListViewFields: TListView;
procedure FormCreate(Sender: TObject);
procedure FormDestroy(Sender: TObject);
procedure ButtonConnectClick(Sender: TObject);
procedure ListBoxTablesClick(Sender: TObject);
procedure ButtonUpdateClick(Sender: TObject);
procedure ButtonNewTableClick(Sender: TObject);
procedure ButtonExitClick(Sender: TObject);
private
{ Private declarations }
AdoxCatalog: _Catalog;
AdoxTables: Tables;
AdoxTable: _Table;
AdoxColumns: Columns;
AdoxColumn: _Column;
function GetADOXFieldType(aType: DataTypeEnum): string;

public
{ Public declarations }
end;

var
DbToolsForm: TDbToolsForm;

implementation

{$R *.DFM}

function tdbtoolsform.GetADOXFieldType(aType: DataTypeEnum): string;
begin
case aType of //
adEmpty: Result := 'adEmpty';
adTinyInt: Result := 'adTinyInt';
adSmallInt: Result := 'adSmallInt';
adInteger: Result := 'adInteger';
adBigInt: Result := 'adBigInt';
adUnsignedTinyInt: Result := 'adUnsignedTinyInt';
adUnsignedSmallInt: Result := 'adUnsignedSmallInt';
adUnsignedInt: Result := 'adUnsignedInt';
adUnsignedBigInt: Result := 'adUnsignedBigInt';
adSingle: Result := 'adSingle';
adDouble: Result := 'adDouble';
adCurrency: Result := 'adCurrency';
adDecimal: Result := 'adDecimal';
adNumeric: Result := 'adNumeric';
adBoolean: Result := 'adBoolean';
adError: Result := 'adError';
adUserDefined: Result := 'adUserDefined';
adVariant: Result := 'adVariant';
adIDispatch: Result := 'adIDispatch';
adIUnknown: Result := 'adIUnknown';
adGUID: Result := 'adGUID';
adDate: Result := 'adDate';
adDBDate: Result := 'adDBDate';
adDBTime: Result := 'adDBTime';
adDBTimeStamp: Result := 'adDBTimeStamp';
adBSTR: Result := 'adBSTR';
adChar: Result := 'adChar';
adVarChar: Result := 'adVarChar';
adLongVarChar: Result := 'adLongVarChar';
adWChar: Result := 'adWChar';
adVarWChar: Result := 'adVarWChar';
adLongVarWChar: Result := 'adLongVarWChar';
adBinary: Result := 'adBinary';
adVarBinary: Result := 'adVarBinary';
adLongVarBinary: Result := 'adLongVarBinary';
adChapter: Result := 'adChapter';
adFileTime: Result := 'adFileTime';
adPropVariant: Result := 'adPropVariant';
adVarNumeric: Result := 'adVarNumeric';
end; // case
end;

procedure TDbToolsForm.FormCreate(Sender: TObject);
var
Dsnstr: string;
begin
AdoxCatalog := CoCatalog.Create;
Dsnstr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + ExtractFilePath(application.ExeName) + 'Customer.mdb;Persist Security Info=False';
ADOConnection.Connected := False;
ADOConnection.ConnectionString := Dsnstr;
try
ADOConnection.Connected := True;
except
Application.MessageBox(pchar('数据库出错,打开数据库失败' + #13 + '[状态:打开通讯录]'),
'错误', MB_OK or MB_ICONWARNING);
Halt;
end;
end;

procedure TDbToolsForm.FormDestroy(Sender: TObject);
begin
AdoxCatalog := nil;
end;

procedure TDbToolsForm.ButtonConnectClick(Sender: TObject);
var
iCount: Integer;
aTableList: TStrings;
begin
ListBoxDataBase.Items.Clear;
ListBoxTables.Items.Clear;
aTableList := TStringList.Create;
try
ADOConnection.GetTableNames(aTableList, False);
for iCount := 0 to atablelist.Count - 1 do
ListBoxDataBase.Items.Add(aTableList.Strings[icount]);
finally;
aTableList.Free;
end;
AdoxCatalog.Set_ActiveConnection(ADOConnection.ConnectionObject);
AdoxTables := AdoxCatalog.Tables;
for iCount := 0 to AdoxTables.Count - 1 do
begin
AdoxTable := AdoxTables.Item[icount];
listboxtables.Items.Add(adoxtable.Name);
end;
end;

procedure TDbToolsForm.ListBoxTablesClick(Sender: TObject);
var
iCount: Integer;
Listitem: TListItem;
begin
if ListBoxTables.ItemIndex <> -1 then
begin
ListViewFields.Items.Clear;
AdoxTable := AdoxTables.Item[listboxtables.ItemIndex];
AdoxColumns := AdoxTable.Columns;
for iCount := 0 to AdoxColumns.Count - 1 do
begin
AdoxColumn := AdoxColumns.Item[icount];
ListItem := ListViewFields.Items.Add;
Listitem.Caption := AdoxColumn.Name;
Listitem.SubItems.Add(GetADOXFieldType(AdoxColumn.Type_));
Listitem.SubItems.Add(IntToStr(AdoxColumn.DefinedSize));
Listitem.SubItems.Add(IntToStr(AdoxColumn.NumericScale));
end;
end;

end;

procedure TDbToolsForm.ButtonUpdateClick(Sender: TObject);
begin
{customer_info
shop_id integer
customer_ticket
shop_id integer
shop
shop_id integer
shop_name 100
shop_address 100
water_info
shop_id integer
}
if ListBoxTables.ItemIndex <> -1 then
begin
//customer_info
if LowerCase(ListBoxTables.Items[ListBoxTables.ItemIndex]) = 'customer_info' then
begin
AdoxColumn := CoColumn.Create;
AdoxColumn.Name := 'shop_id';
AdoxColumn.Type_ := adInteger;
AdoxColumn.DefinedSize := 4;
AdoxColumn.Attributes := adColNullable;
AdoxTable.Columns.Append(AdoxColumn, adInteger, 4);
end;
//customer_ticket
if LowerCase(ListBoxTables.Items[ListBoxTables.ItemIndex]) = 'customer_ticket' then
begin
AdoxColumn := CoColumn.Create;
AdoxColumn.Name := 'shop_id';
AdoxColumn.Type_ := adInteger;
AdoxColumn.DefinedSize := 4;
AdoxColumn.Attributes := adColNullable;
AdoxTable.Columns.Append(AdoxColumn, adInteger, 4);
end;
//water_info
if LowerCase(ListBoxTables.Items[ListBoxTables.ItemIndex]) = 'water_info' then
begin
AdoxColumn := CoColumn.Create;
AdoxColumn.Name := 'shop_id';
AdoxColumn.Type_ := adInteger;
AdoxColumn.DefinedSize := 4;
AdoxColumn.Attributes := adColNullable;
AdoxTable.Columns.Append(AdoxColumn, adInteger, 4);
end;
end;

end;

procedure TDbToolsForm.ButtonNewTableClick(Sender: TObject);
begin

AdoxTable := CoTable.Create;
AdoxTable.ParentCatalog := AdoxCatalog;
AdoxTable.Name := 'shop';
AdoxTable.Columns.Append('shop_id', adInteger, 4);
AdoxTable.Columns.Append('shop_name', adVarWChar, 100);
AdoxTable.Columns.Append('shop_address', adVarWChar, 100);
AdoxTables.Append(AdoxTable);

end;

procedure TDbToolsForm.ButtonExitClick(Sender: TObject);
begin
Close;
end;

end.

 
SQL语句如下:
alter table 你的表名 modify 你的字段名 number(10) null;
^^^^^^^^^^^^^^^ --- 你要修改的字段属性
在 Access中没有试过,你可以试试,看看支持不?
 
Alter table Test
ALTER COLUMN b varchar(10) null
 
modify是不行的。
shd(顺和达??)
的我试试看看。
 
测试了一下,果然Access不认modify,报告Alter table 有语法错误!
Shd老兄说的有理,参见如下:
with ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.Add('alter table tab ALTER COLUMN abcd varchar(250) not null');
ExecSQL; // ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
end;

D6+Access2k顺利通过,修改了字段属性。
学习了,多谢!
 
谢谢各位!
 
多人接受答案了。
 
顶部