创建数据库、表及字段 Sub CreateDatabase() Dim cat As New ADOX.Catalog cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb" End SubSub CreateTable() Dim tbl As New Table Dim cat As New ADOX.Catalog'Open the catalog. ' Open the Catalog. cat.ActiveConnection = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\new.mdb;" tbl.Name = "MyTable" tbl.Columns.Append "Column1", adInteger tbl.Columns.Append "Column2", adInteger tbl.Columns.Append "Column3", adVarWChar, 50 cat.Tables.Append tblEnd Sub
'引用Microsoft Activex Data Object2.x Library Dim cn As New ADODB.Connection '连接 cn.Open "Provider=SQLOLEDB;Data Source=服务器名;User Id=sa;PassWord=****;Initial Catalog=master" 'Microsoft OLE DB Provider for SQL Server '建立数据库 cn.Execute "create database 数据库名" cn.Execute "use 数据库名" '建立新表 cn.Execute "create table 表名(列1 int, 列2 varchar(10))" '往表中添加纪录 cn.Execute "insert into 表名(列1,列2) select 1111,'abcdeflkj'" cn.Execute "insert into 表名(列1,列2) select 22,'add'" '更新纪录 cn.Execute "update 表名 set 列2='xxxx' where 列1=22" '删除纪录 cn.Execute "delete 表名 where 列2='abcdeflkj'"
cn.Close Set cn = Nothing
代码加在模块中 ADO对象的使用例程Public Sub main() Dim conn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset ' Step 1 conn.Open "DSN=pubs;uid=sa;pwd=;database=数据库名" ' Step 2 Set cmd.ActiveConnection = conn cmd.CommandText = "SELECT * from 表名" ' Step 3 rs.CursorLocation = adUseClient rs.Open cmd, , adOpenStatic, adLockBatchOptimistic ' Step 4 rs("au_lname").Properties("Optimize") = True rs.Sort = "au_lname" rs.Filter = "phone LIKE '415 5*'" rs.MoveFirst Do While Not rs.EOF Debug.Print "Name: " & rs("au_fname") & " "; rs("au_lname") & _ "Phone: "; rs("phone") & vbCr rs("phone") = "777" & Mid(rs("phone"), 5, 11) rs.MoveNext Loop' Step 5 conn.BeginTrans'Step 6, part A On Error GoTo ConflictHandler rs.UpdateBatch On Error GoTo 0conn.CommitTransExit Sub'Step 6, part B ConflictHandler:rs.Filter = adFilterConflictingRecords rs.MoveFirst Do While Not rs.EOF Debug.Print "Conflict: Name: " & rs("au_fname"); " " & rs("au_lname") rs.MoveNext Loop conn.Rollback Resume NextEnd Sub
Sub CreateTable() Dim tbl As New Table Dim cat As New ADOX.Catalog'Open the catalog. ' Open the Catalog. cat.ActiveConnection = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\new.mdb;" tbl.Name = "MyTable" tbl.Columns.Append "Column1", adInteger tbl.Columns.Append "Column2", adInteger tbl.Columns.Append "Column3", adVarWChar, 50 cat.Tables.Append tblEnd Sub
'如果你有脚本文件,则可以按如下过程建立Private Sub CreateDataBase(cnDataBase as connect,sqlFile As String) Dim strSql As String, strTmp As String Open sqlFile For Input As #1 strSql = "" Do While Not EOF(1) Line Input #1, strTmp If UCase(strTmp) = "GO" Then cnDataBase.Execute strSql strSql = "" Else strSql = strSql & strTmp & vbCrLf End If Loop If strSql <> "" Then cnDataBase.Execute strSql Close #1 End Sub
cn.execute "use TestDb"
cn.execute "create table testtable(a int,b varchar(10))"
cn.execute "insert into testtable(a,b) select 12,'dddddd'"
Sub CreateDatabase()
Dim cat As New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"
End SubSub CreateTable()
Dim tbl As New Table
Dim cat As New ADOX.Catalog'Open the catalog.
' Open the Catalog.
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\new.mdb;" tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tblEnd Sub
Dim cn As New ADODB.Connection
'连接
cn.Open "Provider=SQLOLEDB;Data Source=服务器名;User Id=sa;PassWord=****;Initial Catalog=master" 'Microsoft OLE DB Provider for SQL Server
'建立数据库
cn.Execute "create database 数据库名"
cn.Execute "use 数据库名"
'建立新表
cn.Execute "create table 表名(列1 int, 列2 varchar(10))"
'往表中添加纪录
cn.Execute "insert into 表名(列1,列2) select 1111,'abcdeflkj'"
cn.Execute "insert into 表名(列1,列2) select 22,'add'"
'更新纪录
cn.Execute "update 表名 set 列2='xxxx' where 列1=22"
'删除纪录
cn.Execute "delete 表名 where 列2='abcdeflkj'"
cn.Close
Set cn = Nothing
ADO对象的使用例程Public Sub main()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
' Step 1
conn.Open "DSN=pubs;uid=sa;pwd=;database=数据库名"
' Step 2
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * from 表名"
' Step 3
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
' Step 4
rs("au_lname").Properties("Optimize") = True
rs.Sort = "au_lname"
rs.Filter = "phone LIKE '415 5*'"
rs.MoveFirst
Do While Not rs.EOF
Debug.Print "Name: " & rs("au_fname") & " "; rs("au_lname") & _
"Phone: "; rs("phone") & vbCr
rs("phone") = "777" & Mid(rs("phone"), 5, 11)
rs.MoveNext
Loop' Step 5
conn.BeginTrans'Step 6, part A
On Error GoTo ConflictHandler
rs.UpdateBatch
On Error GoTo 0conn.CommitTransExit Sub'Step 6, part B
ConflictHandler:rs.Filter = adFilterConflictingRecords
rs.MoveFirst
Do While Not rs.EOF
Debug.Print "Conflict: Name: " & rs("au_fname"); " " & rs("au_lname")
rs.MoveNext
Loop
conn.Rollback
Resume NextEnd Sub
Dim tbl As New Table
Dim cat As New ADOX.Catalog'Open the catalog.
' Open the Catalog.
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\new.mdb;" tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tblEnd Sub
Dim strSql As String, strTmp As String
Open sqlFile For Input As #1
strSql = ""
Do While Not EOF(1)
Line Input #1, strTmp
If UCase(strTmp) = "GO" Then
cnDataBase.Execute strSql
strSql = ""
Else
strSql = strSql & strTmp & vbCrLf
End If
Loop
If strSql <> "" Then cnDataBase.Execute strSql
Close #1
End Sub