Option Explicit Dim Qy As New ADODB.Command Dim fsys As New FileSystemObject Dim ls_sql_path As String Dim lb_active As Boolean Dim ls_src_path As StringPrivate Sub Form_Activate()If lb_active = True Then Exit Sub lb_active = TrueDim ls_return As String Dim winHwnd As Long Dim RetVal As Long Dim li_count As Integerli_count = 0'启动服务 Label1.Caption = "连接SQL SERVER......"
'检测SQl Do While Len(ls_sql_path) < 5 ls_sql_path = fReadSvcStatusSql("BackupDirectory") If ls_sql_path = "" Then MsgBox "未在本计算机上发现SQL Server,请重新运行本安装程序,选择安装MSDE" End End If li_count = li_count + 1 delay 1 If li_count > 90 Then MsgBox "未在本计算机上发现SQL Server,请重新运行本安装程序,选择安装MSDE" End End If Loop
If fsys.FileExists(Left(ls_sql_path, Len(ls_sql_path) - 6) & "binn\scm.exe") Then Me.Hide Shell Left(ls_sql_path, Len(ls_sql_path) - 6) & "binn\scm.exe -action 1" Else MsgBox "未在本计算机上发现SQL Server,请重新运行本安装程序,选择安装MSDE" End End IfwinHwnd = 0 Do While winHwnd = 0 winHwnd = FindWindow(vbNullString, "SQL Server Service Control Manger Utility") DoEvents Loop RetVal = PostMessage(winHwnd, WM_CLOSE, 0&, 0&) Me.Show 0, frmmain Label1.Caption = "导入初始化数据库......"DoEvents Dim ls_dbname As String ls_dbname = SDBNAME'导入数据库,建立LOGIN ls_return = sCopyMDF(sComputerName, Text1.Text, Text2.Text, ls_s_log, ls_s_mdf, SDBNAME)delay 3 '安装结束 Unload Me
If frmselectsetup.Check2.Value = 1 Then frmselectfolder.Show 0, frmmain Else Unload Me frmend.Show 0, frmmain End IfEnd SubPrivate Sub Form_Load() ls_src_path = Of_GetAppPath Label1.Caption = "正在导入初始化数据库......" End Sub Public Function sCopyMDF(sSvrName As String, sUID As String, sPWD As String, sLDFName As String, sMDFName As String, SDBNAME As String) As String'******************************************************************** '此函数将检查 sDBName 是否存在于 'MSDE 服务器。如果数据库存在,此函数将 '从 ADP 所在的位置把 adp1 'sql.mdf 复制到 MSDE Data '目录。接着,附加 adp1sql.mdf。 ' '输入: ' sSvrName 要启动的服务器 ' sUID 启动服务器的用户 ' sPWD 该用户的口令 ' sMDFName 要复制的 MSDE 数据库名称 ' '输出: ' 复制解决方案 ' '引用: ' SQLDMO ' Scripting Runtime '******************************************************************** Dim FSO As Scripting.FileSystemObject Dim osvr As SQLDMO.SQLServer Dim strMessage As String Dim db As Variant Dim fDataBaseFlag As Boolean Dim XDoEventsOn Error GoTo sCopyMDFTrap 'FSO.Copyfile 和 oSvr.AttachDBWithSingleFile '中使用的驱动器名称需要与最终用户计算机上的 'Program Files 和 MSDE 的位置相匹配。 '初始化返回值 sCopyMDF = "" fDataBaseFlag = False Set FSO = CreateObject("Scripting.FileSystemObject") Set osvr = CreateObject("SQLDMO.SQLServer") '登录到数据库 osvr.Connect sSvrName, sUID, sPWD X = osvr.Databases.Count '如果失败,DMO 需要进行初始化 '通过在本地 MSDE 服务器上依次检查所有数据库名称, '检查 sDBName 是否存在于该服务器。 For Each db In osvr.Databases
If db.name = SDBNAME Then '该数据库存在 fDataBaseFlag = True Exit For '退出循环 End If
Next
If Not fDataBaseFlag Then '不存在名为 DemoDatabase 的数据库 '.建数据库SDBNAME Dim transql As String Dim ls_primarypath As String If fsys.FileExists(osvr.Databases("master").PrimaryFilePath & sLDFName) = True Then fsys.DeleteFile osvr.Databases("master").PrimaryFilePath & sLDFName End If
sCopyMDFTrap: If Err.Number = -2147216399 Then 'DMO 需进行初始化 Resume Next Else sCopyMDF = Err.Description MsgBox sCopyMDF End End If Resume ExitCopyMDF Exit Function
End Function Public Function sExec_sql() As StringDim ls_sql_name As String Dim FileString As String Dim strcnntodb As String Dim cnSql As ADODB.ConnectionOn Error GoTo sExecSqlTrap
cn.ConnectionString = "Driver={SQL Server};Server=Develop;Uid=SA;Pwd=;Database=Master"cn.execute "Create Database MyDB"
cn.execute "USE MyDB"
cn.execute "Create Table ..."
...
MDbConn.Open "Provider=SQLOLEDB.1;Password='" & MPassWD & "';Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source='" & MServer & "'"
MDbConn.CursorLocation = adUseClient
Command1.Caption = "正在创建库..."
s = "CREATE DATABASE [StarSYS] ON PRIMARY (NAME = N'StarsoSYS_Data', FILENAME = N'C:\MSSQL7\data\StarSYS_Data.MDF' , SIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = N'StarSYS_Log', FILENAME = N'C:\MSSQL7\data\StarSYS_Log.LDF' , SIZE = 10, FILEGROWTH = 10%)
Set MRecoRs = New ADODB.Recordset
With MRecoRs
.ActiveConnection = MDbConn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = s
.Open
End With ‖天天写程序‖ ‖夜夜泡小妞‖
‖身兼数职做代码‖‖晚晚工作到天明‖
‖为何人生如此苦‖‖泡妞消费数目高‖
‖我看世俗本无趣‖‖程序伤神妞伤人‖
‖不再见女人‖ ‖不想写程序‖
从原理上将,这个接口可以实现SQL管理器的所有功能,非常强大,而且是专门为管理SQL SERVER而提供的,推荐你使用这种标准的方式好一些.
而且可以从中更多的了解SQL SERVER提供的功能.
Dim Qy As New ADODB.Command
Dim fsys As New FileSystemObject
Dim ls_sql_path As String
Dim lb_active As Boolean
Dim ls_src_path As StringPrivate Sub Form_Activate()If lb_active = True Then Exit Sub
lb_active = TrueDim ls_return As String
Dim winHwnd As Long
Dim RetVal As Long
Dim li_count As Integerli_count = 0'启动服务
Label1.Caption = "连接SQL SERVER......"
ls_sql_path = fReadSvcStatusSql("BackupDirectory")
'检测SQl
Do While Len(ls_sql_path) < 5
ls_sql_path = fReadSvcStatusSql("BackupDirectory")
If ls_sql_path = "" Then
MsgBox "未在本计算机上发现SQL Server,请重新运行本安装程序,选择安装MSDE"
End
End If
li_count = li_count + 1
delay 1
If li_count > 90 Then
MsgBox "未在本计算机上发现SQL Server,请重新运行本安装程序,选择安装MSDE"
End
End If
Loop
If fsys.FileExists(Left(ls_sql_path, Len(ls_sql_path) - 6) & "binn\scm.exe") Then
Me.Hide
Shell Left(ls_sql_path, Len(ls_sql_path) - 6) & "binn\scm.exe -action 1"
Else
MsgBox "未在本计算机上发现SQL Server,请重新运行本安装程序,选择安装MSDE"
End
End IfwinHwnd = 0
Do While winHwnd = 0
winHwnd = FindWindow(vbNullString, "SQL Server Service Control Manger Utility")
DoEvents
Loop
RetVal = PostMessage(winHwnd, WM_CLOSE, 0&, 0&)
Me.Show 0, frmmain
Label1.Caption = "导入初始化数据库......"DoEvents
Dim ls_dbname As String
ls_dbname = SDBNAME'导入数据库,建立LOGIN
ls_return = sCopyMDF(sComputerName, Text1.Text, Text2.Text, ls_s_log, ls_s_mdf, SDBNAME)delay 3
'安装结束
Unload Me
If frmselectsetup.Check2.Value = 1 Then
frmselectfolder.Show 0, frmmain
Else
Unload Me
frmend.Show 0, frmmain
End IfEnd SubPrivate Sub Form_Load()
ls_src_path = Of_GetAppPath
Label1.Caption = "正在导入初始化数据库......"
End Sub
Public Function sCopyMDF(sSvrName As String, sUID As String, sPWD As String, sLDFName As String, sMDFName As String, SDBNAME As String) As String'********************************************************************
'此函数将检查 sDBName 是否存在于
'MSDE 服务器。如果数据库存在,此函数将
'从 ADP 所在的位置把 adp1
'sql.mdf 复制到 MSDE Data
'目录。接着,附加 adp1sql.mdf。
'
'输入:
' sSvrName 要启动的服务器
' sUID 启动服务器的用户
' sPWD 该用户的口令
' sMDFName 要复制的 MSDE 数据库名称
'
'输出:
' 复制解决方案
'
'引用:
' SQLDMO
' Scripting Runtime
'********************************************************************
Dim FSO As Scripting.FileSystemObject
Dim osvr As SQLDMO.SQLServer
Dim strMessage As String
Dim db As Variant
Dim fDataBaseFlag As Boolean
Dim XDoEventsOn Error GoTo sCopyMDFTrap 'FSO.Copyfile 和 oSvr.AttachDBWithSingleFile
'中使用的驱动器名称需要与最终用户计算机上的
'Program Files 和 MSDE 的位置相匹配。 '初始化返回值
sCopyMDF = ""
fDataBaseFlag = False
Set FSO = CreateObject("Scripting.FileSystemObject")
Set osvr = CreateObject("SQLDMO.SQLServer")
'登录到数据库
osvr.Connect sSvrName, sUID, sPWD X = osvr.Databases.Count '如果失败,DMO 需要进行初始化 '通过在本地 MSDE 服务器上依次检查所有数据库名称,
'检查 sDBName 是否存在于该服务器。
For Each db In osvr.Databases
If db.name = SDBNAME Then '该数据库存在
fDataBaseFlag = True
Exit For '退出循环
End If
Next
If Not fDataBaseFlag Then '不存在名为 DemoDatabase 的数据库
'.建数据库SDBNAME
Dim transql As String
Dim ls_primarypath As String
If fsys.FileExists(osvr.Databases("master").PrimaryFilePath & sLDFName) = True Then
fsys.DeleteFile osvr.Databases("master").PrimaryFilePath & sLDFName
End If
FSO.CopyFile ls_src_path & "\Database File\" & sMDFName, _
osvr.Databases("master").PrimaryFilePath & sMDFName, True
SetAttr osvr.Databases("master").PrimaryFilePath & sMDFName, vbNormal
'attach db
transql = "sp_attach_single_file_db @dbname = '" & SDBNAME & "', " _
& "@physname = '" & osvr.Databases("master").PrimaryFilePath & sMDFName & "'"
osvr.ExecuteImmediate transql
Else
sCopyMDF = "数据库 " & sMDFName & " 已经存在于 MSDE 服务器"
MsgBox sCopyMDF & Chr(10) & Chr(13) & "请确定SFA数据库不存在于安装的计算机上", , "安装失败"
End
End If
sCopyMDF = sExec_sql
ExitCopyMDF:
osvr.DisConnect
Set osvr = Nothing
Exit Function
sCopyMDFTrap: If Err.Number = -2147216399 Then 'DMO 需进行初始化
Resume Next
Else
sCopyMDF = Err.Description
MsgBox sCopyMDF
End
End If
Resume ExitCopyMDF
Exit Function
End Function
Public Function sExec_sql() As StringDim ls_sql_name As String
Dim FileString As String
Dim strcnntodb As String
Dim cnSql As ADODB.ConnectionOn Error GoTo sExecSqlTrap
'************************************************************
strcnntodb = "PROVIDER=MSDASQL;Driver=SQL Server;Server=" & sComputerName & ";UID=" _
& Text1.Text & ";PWD=" & Text2.Text & ";" & "DATABASE=" & SDBNAME
'************************************************************
'开始连库
Set cnSql = New ADODB.Connection
cnSql.Open strcnntodbls_sql_name = ls_src_path & "\Database File\causer.sql"Open ls_sql_name For Binary As #1
FileString = Space(FileLen(ls_sql_name))
MsgBox FileLen(ls_sql_name)
Get #1, , FileString
Close #1
MsgBox FileString
'为执行SQL语句做好准备
Set Qy.ActiveConnection = cnSql
Qy.CommandType = adCmdText
Qy.CommandText = FileString
Qy.Execute FileString
ls_sql_name = ls_src_path & "\Database File\spm.sql"
'ls_sql_name = App.Path & "\sql_xoa.sql"Open ls_sql_name For Binary As #1
FileString = Space(FileLen(ls_sql_name))
Get #1, , FileString
Close #1
MsgBox FileString
Qy.CommandText = FileString
Qy.Execute FileString
sExec_sql = "建立用户成功!"
Exit FunctionsExecSqlTrap:
sExec_sql = Err.Description
End Function