菖蒲盆景可以放家里吗:如何建立SQL SERVER数据库
只是想学学。。。。。
这就是我曾经写的初始化数据库组件源代码。看看用得上没有。
Option Explicit
Private mdbPassword As String
Private mdbPath As String
Private Server As String
Private UserID As String
Private Password As String
Private DatabaseName As String
Public Property Let l_mdbPath(str_mdbPath As String)
mdbPath = str_mdbPath
End Property
Public Property Let l_mdbPassword(str_mdbPassword As String)
mdbPassword = str_mdbPassword
End Property
Public Property Let l_Server(str_Server As String)
Server = str_Server
End Property
Public Property Let l_LoginID(str_LoginID As String)
UserID = str_LoginID
End Property
Public Property Let l_Password(str_Password As String)
Password = str_Password
End Property
Public Property Let l_DatabaseName(str_DatabaseName As String)
DatabaseName = str_DatabaseName
End Property
'建立数据库
Public Function CreateDatabase() As Integer
Dim sql_connString As String
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
sql_connString = "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" & DatabaseName & "')"
sql_connString = sql_connString + " DROP DATABASE [" & DatabaseName & "]"
sql_connString = sql_connString + " CREATE DATABASE [" & DatabaseName & "]"
conn.ConnectionString = "driver={SQL Server};" & _
"server=" & Server & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=master"
conn.Open
conn.BeginTrans
conn.Execute sql_connString
If conn.Errors.Count > 0 Then
conn.RollbackTrans
Else
conn.CommitTrans
CreateDatabase = 1
End If
conn.Close
Set conn = Nothing
End Function
'建立数据库登录用户
Public Function CreateLoginUser(LoginName As String, UserLoginPassword As String) As Integer
Dim tmp_Str As String
Dim conn_mdb As New ADODB.Connection
Dim conn_Sql As New ADODB.Connection
Dim rs_mdb As New ADODB.Recordset
'打开SQL SERVER数据库
conn_Sql.ConnectionString = "driver={SQL Server};" & _
"server=" & Server & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=" & DatabaseName
conn_Sql.Open
'打开MDB数据库
conn_mdb.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & mdbPath & ";password=" & mdbPassword
rs_mdb.Open "CreateLoginUser", conn_mdb, adOpenDynamic
If Not (rs_mdb.EOF And rs_mdb.BOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
tmp_Str = CStr(rs_mdb("CreateLoginUserSqlLine"))
If InStr(1, tmp_Str, "DefaultDatabase") > 0 Then
tmp_Str = Replace(tmp_Str, "DefaultDatabase", DatabaseName)
End If
If InStr(1, tmp_Str, "DefaultPassword") > 0 Then
tmp_Str = Replace(tmp_Str, "DefaultPassword", UserLoginPassword)
End If
If InStr(1, tmp_Str, "DefaultUser") > 0 Then
tmp_Str = Replace(tmp_Str, "DefaultUser", LoginName)
End If
conn_Sql.Execute tmp_Str
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
CreateLoginUser = 0
rs_mdb.Close
Exit Function
Else
conn_Sql.CommitTrans
CreateLoginUser = 1
rs_mdb.Close
End If
Else
CreateLoginUser = 0
End If
conn_mdb.Close
conn_Sql.Close
Set conn_Sql = Nothing
Set conn_mdb = Nothing
End Function
'建立数据库相关结构内容
Public Function CreateTable() As Integer
Dim conn_mdb As New ADODB.Connection
Dim conn_Sql As New ADODB.Connection
Dim rs_mdb As New ADODB.Recordset
'打开SQL SERVER数据库
conn_Sql.ConnectionString = "driver={SQL Server};" & _
"server=" & Server & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=" & DatabaseName
conn_Sql.Open
'打开MDB数据库
conn_mdb.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & mdbPath & ";password=" & mdbPassword
'第一步,从FirstDropContent表中取得SQL语句,删除库中已经存在的内容
rs_mdb.Open "FirstDropContent", conn_mdb, adOpenDynamic
If Not (rs_mdb.EOF And rs_mdb.BOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
conn_Sql.Execute CStr(rs_mdb("DropContent"))
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
CreateTable = 0
rs_mdb.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
End If
Else
CreateTable = 0
End If
'第二步,从CreateTable表中取得SQL语句,建立数据库的表
rs_mdb.Open "CreateTable", conn_mdb, adOpenDynamic
If Not (rs_mdb.EOF And rs_mdb.BOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
conn_Sql.Execute CStr(rs_mdb("CreateTable"))
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
CreateTable = 0
rs_mdb.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
End If
Else
CreateTable = 0
End If
'第三步,从AlertTable表中取得SQL语句,修改数据库的表
rs_mdb.Open "AlertTable", conn_mdb, adOpenDynamic
If Not (rs_mdb.EOF And rs_mdb.BOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
conn_Sql.Execute CStr(rs_mdb("AlertTable"))
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
CreateTable = 0
rs_mdb.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
End If
Else
CreateTable = 0
End If
'第四步,从CreateView表中取得SQL语句,建立数据库的视图
rs_mdb.Open "CreateView", conn_mdb, adOpenDynamic
If Not (rs_mdb.EOF And rs_mdb.BOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
conn_Sql.Execute CStr(rs_mdb("CreateView"))
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
CreateTable = 0
rs_mdb.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
End If
Else
CreateTable = 0
End If
'第五步,从CreateProcedure表中取得SQL语句,建立数据库的存储过程
rs_mdb.Open "CreateProcedure", conn_mdb, adOpenDynamic
If Not (rs_mdb.EOF And rs_mdb.BOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
conn_Sql.Execute CStr(rs_mdb("CreateProcedure"))
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
CreateTable = 0
rs_mdb.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
End If
Else
CreateTable = 0
End If
'第六步,从CreateTrigger表中取得SQL语句,建立数据库的触发过程
rs_mdb.Open "CreateTrigger", conn_mdb, adOpenDynamic
If Not (rs_mdb.EOF And rs_mdb.BOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
conn_Sql.Execute CStr(rs_mdb("CreateTrigger"))
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
CreateTable = 0
rs_mdb.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
End If
Else
CreateTable = 0
End If
CreateTable = 1
conn_mdb.Close
conn_Sql.Close
Set conn_mdb = Nothing
Set conn_Sql = Nothing
End Function
'删除数据库
Public Function DropDatabase() As Integer
Dim sql_connString As String
Dim conn As New ADODB.Connection
conn.ConnectionString = "driver={SQL Server};" & _
"server=" & Server & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=master"
conn.Open
conn.BeginTrans
sql_connString = "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" & DatabaseName & "')"
sql_connString = sql_connString + " DROP DATABASE [" & DatabaseName & "]"
conn.Execute sql_connString
If conn.Errors.Count > 0 Then
conn.RollbackTrans
Else
conn.CommitTrans
DropDatabase = 1
End If
conn.Close
Set conn = Nothing
End Function
'填充默认数据表内容
Public Function FillTable()
Dim conn_mdb As New ADODB.Connection
Dim conn_Sql As New ADODB.Connection
Dim rs_mdb As New ADODB.Recordset
Dim rs_Sql As New ADODB.Recordset
'打开SQL SERVER数据库
conn_Sql.ConnectionString = "driver={SQL Server};" & _
"server=" & Server & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=" & DatabaseName
conn_Sql.Open
'打开MDB数据库
conn_mdb.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & mdbPath & ";password=" & mdbPassword
'第一步,将MDB库GeneralCode表中的内容导入到SQL SERVEER中的GeneralCode表中。
rs_mdb.Open "GeneralCode", conn_mdb, adOpenDynamic
rs_Sql.Open "dbo.GeneralCode", conn_Sql, adOpenDynamic, adLockOptimistic
If Not rs_Sql.EOF Then
rs_Sql.MoveFirst
While Not rs_Sql.EOF
rs_Sql.Delete
rs_Sql.MoveNext
Wend
End If
If Not (rs_mdb.BOF And rs_mdb.EOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
rs_Sql.AddNew
rs_Sql("Catalog") = rs_mdb("Catalog").Value
rs_Sql("Code") = rs_mdb("Code").Value
rs_Sql("Description") = rs_mdb("Description").Value
rs_Sql("FriendKeyID") = rs_mdb("FriendKeyID").Value
rs_Sql("ModifyBy") = rs_mdb("ModifyBy").Value
rs_Sql("ModifyDate") = rs_mdb("ModifyDate").Value
rs_Sql("UsedBy") = rs_mdb("UsedBy").Value
rs_Sql("TransferFlag") = rs_mdb("TransferFlag").Value
rs_Sql.Update
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
FillTable = 0
rs_mdb.Close
rs_Sql.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
rs_Sql.Close
End If
Else
FillTable = 0
rs_mdb.Close
rs_Sql.Close
End If
'第二步,将MDB库Program表中的内容导入到SQL SERVEER中的Program表中。
rs_mdb.Open "Program", conn_mdb, adOpenDynamic
rs_Sql.Open "dbo.Program", conn_Sql, adOpenDynamic, adLockOptimistic
If Not rs_Sql.EOF Then
rs_Sql.MoveFirst
While Not rs_Sql.EOF
rs_Sql.Delete
rs_Sql.MoveNext
Wend
End If
If Not (rs_mdb.BOF And rs_mdb.EOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
rs_Sql.AddNew
rs_Sql("ProgramID") = rs_mdb("ProgramID").Value
rs_Sql("ProgramName") = rs_mdb("ProgramName").Value
rs_Sql.Update
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
FillTable = 0
rs_mdb.Close
rs_Sql.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
rs_Sql.Close
End If
Else
FillTable = 0
rs_mdb.Close
rs_Sql.Close
End If
'第三步,将MDB库UserAuthorization表中的内容导入到SQL SERVEER中的UserAuthorization表中。
rs_mdb.Open "UserAuthorization", conn_mdb, adOpenDynamic
rs_Sql.Open "dbo.UserAuthorization", conn_Sql, adOpenDynamic, adLockOptimistic
If Not rs_Sql.EOF Then
rs_Sql.MoveFirst
While Not rs_Sql.EOF
rs_Sql.Delete
rs_Sql.MoveNext
Wend
End If
If Not (rs_mdb.BOF And rs_mdb.EOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
rs_Sql.AddNew
rs_Sql("UserID") = rs_mdb("UserID").Value
rs_Sql("UserName") = rs_mdb("UserName").Value
rs_Sql("PositionID") = rs_mdb("PositionID").Value
rs_Sql("GroupID") = rs_mdb("GroupID").Value
rs_Sql("Password") = rs_mdb("Password").Value
rs_Sql("Authoration") = rs_mdb("Authoration").Value
rs_Sql("ModifyBy") = rs_mdb("ModifyBy").Value
rs_Sql("ModifyDate") = Now
rs_Sql("TransferFlag") = rs_mdb("TransferFlag").Value
rs_Sql.Update
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
FillTable = 0
rs_mdb.Close
rs_Sql.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
rs_Sql.Close
End If
Else
FillTable = 0
rs_mdb.Close
rs_Sql.Close
End If
'第四步,将MDB库QueryView表中的内容导入到SQL SERVEER中的QueryView表中。
rs_mdb.Open "QueryView", conn_mdb, adOpenDynamic
rs_Sql.Open "dbo.QueryView", conn_Sql, adOpenDynamic, adLockOptimistic
If Not rs_Sql.EOF Then
rs_Sql.MoveFirst
While Not rs_Sql.EOF
rs_Sql.Delete
rs_Sql.MoveNext
Wend
End If
If Not (rs_mdb.BOF And rs_mdb.EOF) Then
rs_mdb.MoveFirst
conn_Sql.BeginTrans
While Not rs_mdb.EOF
rs_Sql.AddNew
rs_Sql("ProgramCode") = rs_mdb("ProgramCode").Value
rs_Sql("ViewName") = rs_mdb("ViewName").Value
rs_Sql.Update
rs_mdb.MoveNext
Wend
If conn_Sql.Errors.Count > 0 Then
conn_Sql.RollbackTrans
FillTable = 0
rs_mdb.Close
rs_Sql.Close
Exit Function
Else
conn_Sql.CommitTrans
rs_mdb.Close
rs_Sql.Close
End If
Else
FillTable = 0
rs_mdb.Close
rs_Sql.Close
End If
FillTable = 1
conn_mdb.Close
conn_Sql.Close
Set conn_mdb = Nothing
Set conn_Sql = Nothing
End Function
首先安装sql server这个你肯定知道了
然后打开企业管理器,
直接建立数据库即可!
首先在一台是sever操作系统的计算机上安装一个SQL SERVR,当然,如果你能访问别的SQL服务器就不用了,然后用SQL中的企业管理器连接到服务器上,自己的机器就默认连接上,然后你可以新建数据库。