Acess数据库的一点总结
1. 如何查看数据库主键和索引:
打开.mdb文件->选中表->右键->设计视图->可以看到表的所有字段,索引和主键信息:
a. 上面钥匙图标是主键快捷键,闪电图标是索引快捷键,或者通过菜单:编辑->主键,视图->索引,查看和修改。
b. 选中字段,下面有该字段的属性,可以设置;
2. 修改主键,索引和字段长度的脚本,vbs写的:
=======================================================
On Error Resume next
Dim errInfo
errInfo=""
'备份数据库
set fso = createobject("Scripting.FileSystemObject")
fso.CopyFile "D:/test.mdb","D:/test_bak.mdb",True
'打开数据库
Set Conn = CreateObject("ADODB.Connection")
myconn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.mdb"
Conn.Open MyConn
If Err.Number<>0 Then
errInfo=errInfo&"open DB error: " & Err.Description & vbCrLf
else
'修改数据库
AlterDB()
End if
'输出执行结果
If len(errInfo)>10 then
WScript.Echo "err" & vbcrlf
WScript.Echo errInfo
Else
WScript.Echo "ok" & vbcrlf
End If
Sub AlterDB()
'修改字段长度,改为200
conn.execute "ALTER TABLE name_t ALTER COLUMN stu_name varchar(200) "
If Err.Number <>0 Then
errInfo=errInfo&" ALTER TABLE name_t error:" & Err.Description & vbCrLf
End If
'name_t,修改索引stu_name 为不唯一
conn.execute "ALTER TABLE name_t DROP CONSTRAINT stu_name "
If Err.Number <>0 Then
errInfo=errInfo&" alter name_t drop index error:" & Err.Description &vbCrLf
End if
conn.execute "CREATE INDEX stu_name ON name_t(stu_name asc)"
If Err.Number <>0 Then
errInfo=errInfo&" alter name_t add index error:" & Err.Description & vbCrLf
End If
'sumbrowser_t,修改主键
conn.execute "ALTER TABLE name_t drop CONSTRAINT PrimaryKey "
If Err.Number <>0 Then
errInfo=errInfo&" drop pk of name_t error:" & Err.Description & vbCrLf
End If
conn.execute "ALTER TABLE name_t ADD CONSTRAINT PrimaryKey PRIMARY KEY( id,stu_name) "
If Err.Number <>0 Then
errInfo=errInfo&" add pk of name_t error:" & Err.Description & vbCrLf
End If
end sub
===================================================================================