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
'建立數(shù)據(jù)庫
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
'建立數(shù)據(jù)庫登錄用戶
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數(shù)據(jù)庫
conn_Sql.ConnectionString = "driver={SQL Server};" & _
"server=" & Server & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=" & DatabaseName
conn_Sql.Open
'打開MDB數(shù)據(jù)庫
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
'建立數(shù)據(jù)庫相關(guān)結(jié)構(gòu)內(nèi)容
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數(shù)據(jù)庫
conn_Sql.ConnectionString = "driver={SQL Server};" & _
"server=" & Server & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=" & DatabaseName
conn_Sql.Open
'打開MDB數(shù)據(jù)庫
conn_mdb.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & mdbPath & ";password=" & mdbPassword
'第一步，從FirstDropContent表中取得SQL語句，刪除庫中已經(jīng)存在的內(nèi)容
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語句，建立數(shù)據(jù)庫的表
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語句，修改數(shù)據(jù)庫的表
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語句，建立數(shù)據(jù)庫的視圖
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語句，建立數(shù)據(jù)庫的存儲過程
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語句，建立數(shù)據(jù)庫的觸發(fā)過程
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
'刪除數(shù)據(jù)庫
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
'填充默認數(shù)據(jù)表內(nèi)容
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數(shù)據(jù)庫
conn_Sql.ConnectionString = "driver={SQL Server};" & _
"server=" & Server & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & Password & ";" & _
"database=" & DatabaseName
conn_Sql.Open
'打開MDB數(shù)據(jù)庫
conn_mdb.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & mdbPath & ";password=" & mdbPassword
'第一步，將MDB庫GeneralCode表中的內(nèi)容導(dǎo)入到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表中的內(nèi)容導(dǎo)入到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表中的內(nèi)容導(dǎo)入到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表中的內(nèi)容導(dǎo)入到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
本文出自：億恩科技【www.23lll.com】
服務(wù)器租用/服務(wù)器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]
|
0371-60135900
京公網(wǎng)安備41019702002023號
香港服務(wù)器租用
美國服務(wù)器租用
電信骨干機房
聯(lián)通骨干機房
