数据库还原
'引用sqldmo object library
Dim gSQLServer As SQLDMO.SQLServer
On Error GoTo ErrHandler:
If gSQLServer Is Nothing Then
Set gSQLServer = New SQLDMO.SQLServer
End If
gSQLServer.LoginTimeout = 15
frmSelectServer.Show 1
If frmSelectServer.connecString = "" Then Exit Sub
gSQLServer.Connect frmSelectServer.connecString, "sa", "sa"
Dim oRestore As SQLDMO.Restore
Dim Msg As String
Dim Response As String
Set oRestore = New SQLDMO.Restore
oRestore.DATABASE = "data"
CommonDialog1.CancelError = True
On Error GoTo Errhandler1
' 设置标志
CommonDialog1.Flags = cdlOFNHideReadOnly
' 设置过滤器
CommonDialog1.Filter = "All Files (data*.*)|data*.*"
' 指定缺省的过滤器
CommonDialog1.FilterIndex = 2
' 显示“打开”对话框
'
Dim riqi As String
CommonDialog1.FileName = riqi
CommonDialog1.ShowOpen
' 显示选定文件的名字
riqi = CommonDialog1.FileName
oRestore.Files = riqi
'当连接的时候,鼠标变化
Screen.MousePointer = vbHourglass
'恢复前断开所有的数据库连接
Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
Set iDb = New ADODB.Connection
Set iRe = New ADODB.Recordset
Dim iConcStr As String
'连接数据库服务器
iConcStr = "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=master;" & _
"User Id=myUsername;" & _
"Password=myPassword"
iDb.Open iConcStr
Dim iSql As String
iSql = "select spid from master..sysprocesses where dbid=db_id('data')"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
frmMoive.Label1.Caption = "数据库在还原过程中,请等候。"
frmMoive.Caption = "数据库恢复"
frmMoive.Show vbModeless, Me
DoEvents
Me.Enabled = False
Dim Spath As String
If Right(App.Path, 1) <> "/" Then
Spath = App.Path & "/"
Else
Spath = App.Path
End If
frmMoive.Animation1.Open Spath & "working.avi"
frmMoive.Animation1.Play
While iRe.EOF = False
iSql = "kill " & iRe(0)
iDb.Execute iSql
iRe.MoveNext
Wend
iRe.Close
iDb.Close
'恢复数据库
oRestore.SQLRestore gSQLServer
'恢复后鼠标返回默认的状态
Me.Enabled = True
frmMoive.Animation1.Stop
Unload frmMoive
Set oRestore = Nothing
Screen.MousePointer = vbDefault
MsgBox "数据库还原成功!"
Exit Sub
ErrHandler:
MsgBox "Error " & Err.Description
Exit Sub
Errhandler1:
' 用户按了“取消”按钮
Exit Sub
将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/Finance/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
给一个vb的例子给你
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stm As ADODB.Stream
Private Sub SavePictureToDB(cn As ADODB.Connection)
'将BMP图片存入数据库
On Error GoTo EH
Set stm = New ADODB.Stream
rs.Open "select ImagePath,ImageValue from tbl_Image", cn, adOpenKeyset, adLockOptimistic
CommonDialog1.ShowOpen
Text1.Text = CommonDialog1.FileName
With stm
.Type = adTypeBinary
.Open
.LoadFromFile CommonDialog1.FileName
End With
With rs
.AddNew
.Fields("ImagePath") = Text1.Text
.Fields("ImageValue") = stm.Read
.Update
End With
rs.Close
Set rs = Nothing
Exit Sub
EH: MsgBox Err.Description, vbInformation, "Error"
End Sub
Private Sub LoadPictureFromDB(cn As ADODB.Connection)
'载数据库中读出BMP图片
On Error GoTo EH
Dim strTemp As String
Set stm = New ADODB.Stream
strTemp = "c:/temp.tmp" '临时文件,用来保存读出的图片
rs.Open "select ImagePath,ImageValue from tbl_image", cn, , , adCmdText
With stm
.Type = adTypeBinary
.Open
.Write rs("ImageValue")
.SaveToFile strTemp, adSaveCreateOverWrite
.Close
End With
Image1.Picture = LoadPicture(strTemp)
Set stm = Nothing
rs.Close
Set rs = Nothing
Exit Sub
EH: MsgBox Err.Description, vbInformation, "Error"
End Sub