Database reduction
'Reference sqldmo object libraryDim gSQLServer As SQLDMO.SQLServerOn 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 'provided Sign CommonDialog1.Flags = CDLOFNHIDEREADONLY 'Settings Filter Commondialog1.filter = "All Files (Data *. *) | DATA *. *" Specifies the default filter commonDialog1.filterIndex = 2' Show "Open" Dialog 'DIM riqi as String CommonDialog1.FileName = riqi CommonDialog1.ShowOpen 'riqi display the name of the selected file = CommonDialog1.FileName oRestore.Files = riqi' time when connected, the mouse changes Screen.MousePointer = vbHourglass' disconnect all database connections before resuming DIM IDB As Adodb.Connection, IRE AS ADODB.Recordset Set iDb = New ADODB.Connection Set iRe = New ADODB.Recordset Dim iConcStr As String 'connect to the database server 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 = "database during the restore process, please wait." frmMoive.Caption = "Database Recovery" frmMoive.Show vbModeless, Me DoEvents Me.Enabled = False Dim Spath As String if right (app.path, 1) <> "/" = app.path "/" else spath = app.path endiffmoive.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 'recovery database Ostore.sqlrestore GSQLServer' Restore Mouse Returns Default Status ME.Enabled = True frmMoive.Animation1.Stop Unload frmMoive Set oRestore = Nothing Screen.MousePointer = vbDefault MsgBox "database restore successful!" Exit SubErrHandler: MsgBox "Error" & Err.Description Exit SubErrhandler1: 'user presses the "cancel" button Exit Sub
Import Excel's data into SQL Server: - ========================================= ============== Select * INTO newTableFrom OpenDataSource ('microsoft.jet.OleDb.4.0', 'data source = "c: /book1.xls"; user ID = admin; password = Extended Properties = Excel 5.0 ') ... [Sheet1 $] Instance: Select * INTO NewTableFrom OpenDataSource (' Microsoft.jet.OleDb.4.0 ',' Data Source = "C: /FINance/account.xls"; User ID = Admin; password =; extended Properties = Excel 5.0 ') ... XActions
To give you an example of a vb Dim cn As New ADODB.ConnectionDim rs As New ADODB.RecordsetDim stm As ADODB.StreamPrivate Sub SavePictureToDB (cn As ADODB.Connection) 'The BMP images into a database 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 endh rs.close set = nothingxit subeh: msgBox err.description, vbinformation, "error" End Sub
PRIVATE SUB LOADB.CONNECEFROMDB (CN As Adodb.connection) 'Database Database Reads BMP Picture On Error Goto EH DIM STRTEMP AS STRING SETS = New AdoDb.Stream Strtemp = "C: /TEMP.TMP" Temporary file, used to save read picture 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 = nothingExit subeh: msgBox err.description, vbinformation, "error" End Sub
