与Access数据库结构有关的一些函数
//与数据库结构有关的一些函数//1、动态改变字段名称
uses ComObj;
//Access
//TableName: 表名; OldColName: 原字段名; NewColName: 新字段名;
procedure RenameField(const TableName, OldColName, NewColName: string);
var
DB, Col: OleVariant;
begin
DB := CreateOleObject('ADOX.Catalog');
DB.ActiveConnection := ADOConnection1.ConnectionObject;
Col := CreateOleObject('ADOX.Column');
Col := DB.Tables[TableName].Columns[OldColName];
Col.Name := NewColName;
end;
//SQLServer
procedure RenameField(const TableName, OldColName, NewColName: string);
begin
with ADOCommand1 do
begin
CommandText := 'EXEC sp_rename ''' + TableName + '.' + OldColName +
''',''' + NewColName + ''',''COLUMN'';';
Excute;
end;
end;
2、取得 Access 库中的表结构
type
TTableDef = record
Name,
DateCreated,
LastUpdated,
Description: string;
end;
TTableDefs = array of TTableDef;
procedure GetTableDefs(const DBName: string; out TableDefs: TTableDefs);
var
DBEngine, DB: OleVariant;
I: Longint;
begin
try
DBEngine := CreateOleObject('DAO.DBEngine.36');
DB := DBEngine.OpenDatabase(DBName);
SetLength(TableDefs, Longint(DB.TableDefs.Count));
for I := Low(TableDefs) to High(TableDefs) do
begin
TableDefs[I].Name := DB.TableDefs[I].Name;
TableDefs[I].DateCreated := DB.TableDefs[I].DateCreated;
TableDefs[I].LastUpdated := DB.TableDefs[I].LastUpdated;
try
TableDefs[I].Description := DB.TableDefs[I].Properties['Description'].Value;
except
TableDefs[I].Description := '';
end;
end;
finally
DB := Unassigned;
DBEngine := Unassigned;
end;
end;
3、取得 Access 表中的字段结构
type
TFieldDef = record
Name: string;
Types,
Size: Longint;
Description: string;
end;
TFieldDefs = array of TFieldDef;
procedure GetFieldDefs(const DBName, TableName: string; out FieldDefs: TFieldDefs);
var
DBEngine, DB: OleVariant;
I: Longint;
begin
try
DBEngine := CreateOleObject('DAO.DBEngine.36');
DB := DBEngine.OpenDatabase(DBName);
SetLength(FieldDefs, Longint(DB.TableDefs[TableName].Fields.Count));
for I := Low(FieldDefs) to High(FieldDefs) do
begin
FieldDefs[I].Name := DB.TableDefs[TableName].Fields[I].Name;
FieldDefs[I].Types := DB.TableDefs[TableName].Fields[I].Type;
FieldDefs[I].Size := DB.TableDefs[TableName].Fields[I].Size;
try
FieldDefs[I].Description := DB.TableDefs[TableName].Fields[I].Properties['Description'].Value;
except
FieldDefs[I].Description := '';
end;
end;
finally
DB := Unassigned;
DBEngine := Unassigned;
end;
end;
打印MSAccess2000数据库结构信息
阅读:
576次 时间:2003-08-19 00:00:00 字体:[
大
中
小]
若有问题请联系: xinsheng_chang@163.com
0710-6545726
ReportControl、tvAPIThing 单元可在网上找,也可以与作者联系。
unit GetDBStruInfo;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, ExtCtrls, ReportControl, Grids, DBGrids,
tvAPIThing, Buttons;
type
TGetDBStruInfoFrm = class(TForm)
ADOConnection1: TADOConnection;
ListBox1: TListBox;
Panel1: TPanel;
Button1: TButton;
ADOTable1: TADOTable;
ADOTable2: TADOTable;
ADOConnection2: TADOConnection;
ReportRunTime1: TReportRunTime;
Button2: TButton;
OpenDialog1: TOpenDialog;
BitBtn1: TBitBtn;
Bevel1: TBevel;
Label1: TLabel;
Label2: TLabel;
Button3: TButton;
tvAPIThing1: TtvAPIThing;
Image1: TImage;
procedure Button1Click(Sender: TObject);
function GetFieldType(ly: TFieldType): string;
function GetFieldBZ(ly: TFieldType): string;
procedure Button2Click(Sender: TObject);
procedure BitBtn1Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
GetDBStruInfoFrm: TGetDBStruInfoFrm;
dbnamevar: string;
implementation
{$R *.dfm}
procedure TGetDBStruInfoFrm.Button1Click(Sender: TObject);
var
i, j, k, p: integer;
mytextfile: TextFile;
begin
ADOConnection1.Connected := false;
OpenDialog1.Title := '请选择数据库';
OpenDialog1.Filter := 'MS Access files (*.mdb)|*.mdb';
if not OpenDialog1.Execute then
exit;
if OpenDialog1.FileName = '' then
exit;
ADOConnection1.ConnectionString :=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +
OpenDialog1.FileName;
dbnamevar := OpenDialog1.FileName;
Label2.Caption := dbnamevar;
Refresh;
AssignFile(mytextfile, ExtractFilePath(Application.exename) +
'DBStruInfo.txt');
ReWrite(mytextfile);
writeln(mytextfile, '序号,', '名称,', '字段类型,', '字段宽度,', '备注');
screen.cursor := crHourGlass;
try
ADOConnection1.GetTableNames(listbox1.Items, false);
j := listbox1.Items.Count;
for i := 0 to j - 1 do
begin
ADOTable1.Active := false;
writeln(mytextfile, 'B', 100 + i + 1, ',', listbox1.items[i]);
ADOTable1.TableName := '[' + listbox1.items[i] + ']';
ADOTable1.Active := true;
p := ADOTable1.FieldCount;
for k := 0 to p - 1 do
begin
writeln(mytextfile, 'B', 100 + i + 1, 'ZD', 100 + k + 1, ',',
ADOTable1.Fields[K].FieldName,
',', GetFieldType(ADOTable1.Fields[k].DataType), ',',
ADOTable1.Fields[k].Size,
',', GetFieldBZ(ADOTable1.Fields[k].DataType));
end;
end;
Button2.Enabled := true;
finally
closefile(mytextfile);
ADOConnection1.Connected := false;
screen.cursor := crDefault;
SHOWMESSAGE('完成!');
end;
end;
function TGetDBStruInfoFrm.GetFieldType(ly: TFieldType): string;
begin
case ly of
ftUnknown: Result := 'Unknown';
ftString: Result := 'Character or string';
ftSmallint: Result := '16-bit integer';
ftInteger: Result := '32-bit integer';
ftWord: Result := '16-bit unsigned integer';
ftBoolean: Result := 'Boolean';
ftFloat: Result := 'Floating-point numeric';
ftCurrency: Result := 'Money';
ftBCD: Result := 'Binary-Coded Decimal';
ftDate: Result := 'Date';
ftTime: Result := 'Time';
ftDateTime: Result := 'Date and time';
ftBytes: Result := 'Fixed number of bytes';
ftVarBytes: Result := 'Variable number of bytes';
ftAutoInc: Result := 'Auto-incrementing 32-bit integer counter';
ftBlob: Result := 'Binary Large OBject';
ftMemo: Result := 'Text memo';
ftGraphic: Result := 'Bitmap';
ftFmtMemo: Result := 'Formatted text memo';
ftParadoxOle: Result := 'Paradox OLE';
ftDBaseOle: Result := 'dBASE OLE';
ftTypedBinary: Result := 'Typed binary';
ftCursor: Result :=
'Output cursor from an Oracle stored procedure (TParam only)';
ftFixedChar: Result := 'Fixed character';
ftWideString: Result := 'Wide string';
ftLargeint: Result := 'Large integer';
ftADT: Result := 'Abstract Data Type';
ftArray: Result := 'Array';
ftReference: Result := 'REF';
ftDataSet: Result := 'DataSet';
ftOraBlob: Result := 'BLOB in Oracle 8 tables';
ftOraClob: Result := 'CLOB in Oracle 8 tables';
ftVariant: Result := 'Data of unknown or undetermined type';
ftInterface: Result := 'References to interfaces';
ftIDispatch: Result := 'References to IDispatch interfaces';
ftGuid: Result := 'globally unique identifier (GUID) values';
ftTimeStamp: Result := 'Date and time field accessed through dbExpress';
ftFMTBcd: Result :=
'Binary-Coded Decimal field that is too large for ftBCD';
else
Result := '不知道';
end;
end;
function TGetDBStruInfoFrm.GetFieldBZ(ly: TFieldType): string;
begin
case ly of
ftUnknown: Result := 'Unknown or undetermined';
ftString: Result := 'Character or string field';
ftSmallint: Result := '16-bit integer field';
ftInteger: Result := '32-bit integer field';
ftWord: Result := '16-bit unsigned integer field';
ftBoolean: Result := 'Boolean field';
ftFloat: Result := 'Floating-point numeric field';
ftCurrency: Result := 'Money field';
ftBCD: Result :=
'Binary-Coded Decimal field that can be converted to Currency type without a loss of precision';
ftDate: Result := 'Date field';
ftTime: Result := 'Time field';
ftDateTime: Result := 'Date and time field';
ftBytes: Result := 'Fixed number of bytes (binary storage)';
ftVarBytes: Result := 'Variable number of bytes (binary storage)';
ftAutoInc: Result := 'Auto-incrementing 32-bit integer counter field';
ftBlob: Result := 'Binary Large OBject field';
ftMemo: Result := 'Text memo field';
ftGraphic: Result := 'Bitmap field';
ftFmtMemo: Result := 'Formatted text memo field';
ftParadoxOle: Result := 'Paradox OLE field';
ftDBaseOle: Result := 'dBASE OLE field';
ftTypedBinary: Result := 'Typed binary field';
ftCursor: Result :=
'Output cursor from an Oracle stored procedure (TParam only)';
ftFixedChar: Result := 'Fixed character field';
ftWideString: Result := 'Wide string field';
ftLargeint: Result := 'Large integer field';
ftADT: Result := 'Abstract Data Type field';
ftArray: Result := 'Array field';
ftReference: Result := 'REF field';
ftDataSet: Result := 'DataSet field';
ftOraBlob: Result := 'BLOB fields in Oracle 8 tables';
ftOraClob: Result := 'CLOB fields in Oracle 8 tables';
ftVariant: Result := 'Data of unknown or undetermined type';
ftInterface: Result := 'References to interfaces (IUnknown)';
ftIDispatch: Result := 'References to IDispatch interfaces';
ftGuid: Result := 'globally unique identifier (GUID) values';
ftTimeStamp: Result := 'Date and time field accessed through dbExpress';
ftFMTBcd: Result :=
'Binary-Coded Decimal field that is too large for ftBCD';
else
Result := '不知道';
end;
end;
procedure TGetDBStruInfoFrm.Button2Click(Sender: TObject);
begin
ADOConnection2.Connected := false;
ADOConnection2.ConnectionString := 'Provider=MSDASQL.1;' +
'Persist Security Info=False;' +
'Extended Properties="' +
'DefaultDir=' + ExtractFiledir(Application.exename) + ';' +
'Driver={Microsoft Text Driver (*.txt; *.csv)};' +
'DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;' +
'FILEDSN=' + ExtractFiledir(Application.exename) + '\misdbstrue;' +
'MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;' +
'SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"';
if not ADOTable2.Active then
ADOTable2.Active := true;
SetCurrentDir(ExtractFiledir(Application.exename));
ADOTable2.DisableControls;
ReportRunTime1.ReportFile := ExtractFilePath(Application.exename) +
'misdbstrueinfo.ept';
with ReportRunTime1 do
begin
EnableEdit := True;
SetDataSet('d1', ADOTable2); //设置报表模板中的数据集变量名
SetVarValue('DBName', dbnamevar); //设置报表模板中的字符变量内容
// SetVarValue('GSNAME',GSNameVar); //设置报表模板中的字符变量内容
// SetVarValue('code',Code1);
PrintPreview(True); //打印
end;
ADOTable2.DisableControls;
ADOTable2.Active := false;
ADOConnection2.Connected := false;
end;
procedure TGetDBStruInfoFrm.BitBtn1Click(Sender: TObject);
begin
close;
end;
procedure TGetDBStruInfoFrm.Button3Click(Sender: TObject);
var
s1, s2: string;
x: TFixedFileInfo;
begin
x := tvAPIThing1.FileInfo(Application.ExeName);
s1 := tvAPIThing1.GetFileInformation(Application.ExeName, 'InternalName'); //
s2 := Application.Title + #13#10 +
'Version: ' + IntToStr(x.wFileVersionLS) + '.'
+ IntToStr(x.wFileVersionMS) +
' (Build: ' + IntToStr(x.wProductVersionMS) + ') Release: ' +
IntToStr(x.wProductVersionLS) + #13#10 +
tvAPIThing1.GetFileInformation(Application.ExeName, 'CompanyName')
+ #13#10 + 'E-mail:xinsheng_chang@163.com';
tvAPIThing1.ShellAbout(s1, s2);
end;
end.
- Public Sub getTables()
- Dim rs As New ADODB.Recordset
- Dim conn As ADODB.Connection
- Dim sSql As String
- Set conn = CurrentProject.Connection
- sSql = "select id,Name from MSysObjects where Type=1 and flags=0"
- rs.Open sSql, conn
- Do While Not rs.EOF
- Debug.Print "****"; rs.Fields("Name").Value; "****************"
- getTableColumns rs.Fields("Name").Value
- rs.MoveNext
- Loop
- rs.Close
- Set rs = Nothing
- Set conn = Nothing
- End Sub
- Public Sub getTableColumns(sTableName As String)
- Dim rs As New ADODB.Recordset
- Dim conn As ADODB.Connection
- Dim fld As ADODB.Field
- Set conn = CurrentProject.Connection
- rs.Open sTableName, conn, , , adCmdTable
- For Each fld In rs.Fields
- Debug.Print fld.Name, sFldType(fld.Type), fld.NumericScale, fld.Precision
- Next
- rs.Close
- Set rs = Nothing
- Set conn = Nothing
- End Sub
lString,lTableName:TStrings;
//-----------获取表名信息-------------
lString:=TStringList.Create;
lTableName:=TStringList.Create;
DM_Conn.con_Access.GetTableNames(lString,True);
for i:=0 to lString.Count-1 do
begin
if (lString.Strings[i]<>'MSysACEs')
and (lString.Strings[i]<>'MSysObjects')
and (lString.Strings[i]<>'MSysQueries')
and (lString.Strings[i]<>'MSysRelationships') then
lTableName.Add(lString.Strings[i]);
end;
Access中Data_type的说名
switch (data_type) {
//case常量 值 说明//case 0x2000
// :p = AdArray //(不适用于 ADOX。) 0x2000 一个标志值,通常与另一个数据类型常量组合,指示该数据类型的数组。
case 20: p = "adBigInt 20 指示一个八字节的有符号整数 (DBTYPE_I8)。";
case 128: p = "adBinary 128 指示一个二进制值 (DBTYPE_BYTES)。";
case 11: p = "adBoolean 指示一个布尔值 (DBTYPE_BOOL)。";
case 8: p = "adBSTR 8 指示以 Null 终止的字符串 (Unicode) (DBTYPE_BSTR)。";
case 136: p = "adChapter 136 指示一个四字节的子集值,标识子行集合中的行 (DBTYPE_HCHAPTER)。";
case 129: p = "adChar129 指示一个字符串值 (DBTYPE_STR)。";
case 6: p = "adCurrency 6 指示一个货币值 (DBTYPE_CY)。货币是一个定点数字,小数点右侧有四位数字。该值存储为八字节、范围为 10,000 的有符号整数。";
case 7: p = "adDate 7 指示日期值 (DBTYPE_DATE)。日期保存为双精度数,数字的整数部分是从 1899 年 12 月 30 日算起的天数,小数部分是一天当中的片段时间。";
case 133: p = "adDBDate 133 指示日期值 (yyyymmdd) (DBTYPE_DBDATE)。";
case 134: p = "adDBTime 134 指示时间值 (hhmmss) (DBTYPE_DBTIME)。";
case 135: p = "adDBTimeStamp 135 指示日期/时间戳(yyyymmddhhmmss 加十亿分之一的小数)(DBTYPE_DBTIMESTAMP)。";
case 14: p = "adDecimal 14 指示具有固定精度和范围的确切数字值 (DBTYPE_DECIMAL)。";
case 5: p = "adDouble 5 指示一个双精度浮点值 (DBTYPE_R8)。";
case 0: p = "adEmpty 0 指定没有值 (DBTYPE_EMPTY)。";
case 10: p = "adError 10 指示一个 32 位的错误代码 (DBTYPE_ERROR)。";
case 64: p = "adFileTime 64 指示一个 64 位的值,表示从 1601 年 1 月 1 日开始的 100 个十亿分之一秒间隔的数量 (DBTYPE_FILETIME)。";
case 72: p = "adGUID 72 指示全局唯一标识符 (GUID) (DBTYPE_GUID)。";
case 9: p = "adIDispatch 9 指示指向 COM 对象上 IDispatch 接口的指针 (DBTYPE_IDISPATCH)。";
//注意 ADO 目前不支持这种数据类型。使用它可能导致不可预料的结果。 case 3: p = " adInteger 3 指示一个四字节的有符号整数 (DBTYPE_I4)。";
case 13: p = "adIUnknown 13 指示指向 COM 对象上 IUnknown 接口的指针 (DBTYPE_IUNKNOWN)。";
//注意 ADO 目前不支持这种数据类型。使用它可能导致不可预料的结果。 case 205: p = "adLongVarBinary 205 指示一个长二进制值(仅限于 Parameter 对象)。";
case 201: p = " adLongVarChar 201 指示一个长字符串值(仅限于 Parameter 对象)。";
case 203: p = "adLongVarWChar 203 指示一个以 Null 终止的长 Unicode 字符串值(仅限于 Parameter 对象)。";
case 131: p = "adNumeric 131 指示具有固定精度和范围的确切数字值 (DBTYPE_NUMERIC)。";
case 138: p = "adPropVariant 138 指示一个 Automation PROPVARIANT (DBTYPE_PROP_VARIANT)。";
case 4: p = "adSingle 4 指示一个单精度浮点值 (DBTYPE_R4)。";
case 2: p = "adSmallInt 2 指示一个双字节的有符号整数 (DBTYPE_I2)。";
case 16: p = "adTinyInt 16 指示一个单字节的有符号整数 (DBTYPE_I1)。";
case 21: p = " adUnsignedBigInt 21 指示一个八字节的无符号整数 (DBTYPE_UI8)。";
case 19: p = "adUnsignedInt 19 指示一个四字节的无符号整数 (DBTYPE_UI4)。";
case 18: p = "adUnsignedSmallInt 18 指示一个双字节的无符号整数 (DBTYPE_UI2)。";
case 17: p = "adUnsignedTinyInt 17 指示一个单字节的无符号整数 (DBTYPE_UI1)。";
case 132: p = "adUserDefined 132 指示一个用户定义的变量 (DBTYPE_UDT)。";
case 204: p = "adVarBinary 204 指示一个二进制值(仅限于 Parameter 对象)。";
case 200: p = "adVarChar 200 指示一个字符串值(仅限于 Parameter 对象)。";
case 12: p = "adVariant 12 指示一个 Automation Variant (DBTYPE_VARIANT)。";
//注意 ADO 目前不支持这种数据类型。使用它可能导致不可预料的结果。 case 139: p = "adVarNumeric 139 指示一个数字值(仅限于 Parameter 对象)。";
case 202: p = "adVarWChar 202 指示一个以 Null 终止的 Unicode 字符串(仅限于 Parameter 对象)。";
case 130: p = "adWChar 130 指示一个以 Null 终止的 Unicode 字符串 (DBTYPE_WSTR)。";
}
DELPHI中操作ACCESS数据库(建立.mdb文件,压缩数据库) 以下代码在WIN2K,D6,MDAC2.6下测试通过, 编译好的程序在WIN98第二版无ACCESS环境下运行成功. // 动态创建.mdb数据库文件 procedure TMainForm.CreateDynamicDatabase(filename: string); var CreateAccess: OleVariant; begin CreateAccess := CreateOleObject( 'ADOX.Catalog '); CreateAccess.Create( 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source= ' + filename + '; Jet OLEDB:Database Password=xyz '); end; // 创建场地信息表 procedure TMainForm.CreateTableCDXX; begin with ADOQuery1 do begin Close; SQL.Clear; SQL.Add( 'Create Table CDXX( '); SQL.Add( 'CD char(5) not null, '); SQL.Add( 'RQ date not null, '); SQL.Add( 'ZSR money default 0 not null, '); SQL.Add( 'ZZC money default 0 not null, '); SQL.Add( 'ZYY money default 0 not null, '); SQL.Add( 'PRIMARY KEY (CD)) '); ExecSQL; end; end; //在之前uses ComObj,ActiveX //声明连接字符串 Const SConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;' +'Jet OLEDB:Database Password=%s;'; //============================================================================= // Procedure: GetTempPathFileName // Author : ysai // Date : 2003-01-27 // Arguments: (None) // Result : string //============================================================================= function GetTempPathFileName():string; //取得临时文件名 var SPath,SFile:array [0..254] of char; begin GetTempPath(254,SPath); GetTempFileName(SPath,'~SM',0,SFile); result:=SFile; DeleteFile(result); end; //============================================================================= // Procedure: CreateAccessFile // Author : ysai // Date : 2003-01-27 // Arguments: FileName:String;PassWord:string='' // Result : boolean //============================================================================= function CreateAccessFile(FileName:String;PassWord:string=''):boolean; //建立Access文件,如果文件存在则失败 var STempFileName:string; vCatalog:OleVariant; begin STempFileName:=GetTempPathFileName; try vCatalog:=CreateOleObject('ADOX.Catalog'); vCatalog.Create(format(SConnectionString,[STempFileName,PassWord])); result:=CopyFile(PChar(STempFileName),PChar(FileName),True); DeleteFile(STempFileName); except result:=false; end; end; //============================================================================= // Procedure: CompactDatabase // Author : ysai // Date : 2003-01-27 // Arguments: AFileName,APassWord:string // Result : boolean //============================================================================= function CompactDatabase(AFileName,APassWord:string):boolean; //压缩与修复数据库,覆盖源文件 var STempFileName:string; vJE:OleVariant; begin STempFileName:=GetTempPathFileName; try vJE:=CreateOleObject('JRO.JetEngine'); vJE.CompactDatabase(format(SConnectionString,[AFileName,APassWord]), format(SConnectionString,[STempFileName,APassWord])); result:=CopyFile(PChar(STempFileName),PChar(AFileName),false); DeleteFile(STempFileName); except result:=false; end; end;
DELPHI对ACCESS表结构操作 |
|
- 新建表: create table [表名] ( [自动编号字段] int IDENTITY (1,1) PRIMARY KEY , [字段1] nVarChar(50) default ''默认值'' null , [字段2] ntext null , [字段3] datetime, [字段4] money null , [字段5] int default 0, [字段6] Decimal (12,4) default 0, [字段7] image null , ) 删除表: Drop table [表名] 插入数据: INSERT INTO [表名] (字段1,字段2) VALUES (100,''51WINDOWS.NET'') 删除数据: DELETE FROM [表名] WHERE [字段名]>100 更新数据: UPDATE [表名] SET [字段1] = 200,[字段2] = ''51WINDOWS.NET'' WHERE [字段三] = ''HAIWA'' 新增字段: ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL 删除字段: ALTER TABLE [表名] DROP COLUMN [字段名] 修改字段: ALTER TABLE [表名] ALTER COLUMN [字段名] NVARCHAR (50) NULL 重命名表:(Access 重命名表,请参考文章:在Access数据库中重命名表) sp_rename ''表名'', ''新表名'', ''OBJECT'' 新建约束: ALTER TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= ''2000-1-1'') 删除约束: ALTER TABLE [表名] DROP CONSTRAINT 约束名新建默认值 ALTER TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT ''51WINDOWS.NET'' FOR [字段名] 删除默认值 ALTER TABLE [表名] DROP CONSTRAINT 默认值名另外以上的只是SQL的语法在 ACCESS 下大部份也都是一样的项一项查阅,并经自己使用验证,确认在access 数据库中添加自动编号字段使用以下方法比较合适: create table 数据表名称 (id counter constraint primarykey primary key) 需要注意的地方是:第二个primary中间有空格,另外,关键字不区分大小写. 另外自己最近发现的一种方法是: sql="create table mytb (id autoincrement(25,1) primary key,age int)" sql2="create table testtb (id autoincrement,age int,email char, primary key (id))" 其中在access中,autoincrement为自动编号类型字段,(25,1)分别为初始值及步长值,如果不写的话,默认是1,1,primary key指定了主键,以上示例,两种指定方法都可以 |