如何建立SQL SERVER數據庫

QQ1355017510
 電話  0371-63322099



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

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]