VB Quick View of table structure and table data
The younger brother often views the data in the database to view the table data. It is really troublesome to switch back and forth to use SQL plus for SQL server to have an Enterprise Manager or query analyzer to use SQL plus for oracle, so compiled a database viewer only for the MS SQL server and oracle database, using oledb to connect to the database this program for the VB program, the control libraries that use Microsoft Internet Controls and Microsoft Windows Common Controls 6.0 also reference Microsoft ActiveX Data Objects 2.5 Library. The reference program user interface of Microsoft ole db Service Component 1.0 Type Library isVERSION 5.00 Object = "{831fdd16-0c5c-11d2-a9fc-109f8754da1} #2.0 #0"; "MSCOMCTL. OCX "Object =" {EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B} #1.1 #0 ";" shdocvw. dll "Begin VB. form frmViewData Caption = "Form1" ClientHeight = 6780 ClientLeft = 60 ClientTop = 345 ClientWidth = 9630 Icon = "frmViewData. frx ": 0000 LinkTopic =" Form1 "ScaleHeight = 6780 ScaleWidth = 9630 StartUpPosition = 2' CenterScreen Begin VB. commandButton limit min Caption = "minimum" Height = 390 Left = 7680 TabIndex = 11 Top = 0 Width = 885 End Begin VB. commandButton export Max Caption = "maximum" Height = 390 Left = 6735 TabIndex = 10 Top = 0 Width = 930 End Begin VB. commandButton history count Caption = "Number of query records" Height = 390 Left = 5325 TabIndex = 9 Top = 0 Width = 1380 End Begin SHDocVwCtl. webBrowser myGrid Height = 3525 Left = 3330 TabIndex = 8 Top = 3060 Width = 5070 ExtentX = 8943 ExtentY = 6218 ViewMode = 0 Offline = 0 Silent = 0 RegisterAsBrowser = 0 RegisterAsDropTarget = 1 AutoArrange = 0 'false NoClientEdge = 0' False AlignLeft = 0' False NoWebView = 0' False HideFileNames = 0' False SingleClick = 0' False SingleSelection = 0' False NoFolders = 0' False Transparent = 0 'false ViewID = "{0057D0E0-3573-11CF-AE69-08002B2E1262}" Location = "http: /// "End Begin VB. commandButton cmdOpenTable Caption = "Open Table" Height = 390 Left = 4170 TabIndex = 7 Top = 0 Width = 1110 End Begin VB. commandButton Optimize Query Caption = "query" Height = 390 Left = 2895 TabIndex = 6 Top = 0 Width = 1230 End Begin VB. commandButton cmdRefreshSQL Caption = "Refresh SQL statement" Height = 390 Left = 1260 TabIndex = 5 Top = 0 Width = 1590 End Begin VB. pictureBox picUpDown Height = 105 Left = 3360 MousePointer = 7' Size n s ScaleHeight = 45 ScaleWidth = 4875 TabIndex = 4 Top = 2850 Width = 4935 End Begin VB. textBox txtSQL BeginProperty Font Name = "Fixedsys" Size = 12 Charset = 134 Weight = 400 Underline = 0' False Italic = 0' False Strikethrough = 0' False EndProperty Height = 1935 Left = 3525 multiLine =-1 'true ScrollBars = 2' Vertical TabIndex = 3 Top = 750 Width = 4815 End Begin VB. commandButton export conn Caption = "connect to Database" Height = 390 Left = 0 TabIndex = 2 Top = 0 Width = 1215 End Begin VB. pictureBox picLeftRight Height = 5625 Left = 3030 MousePointer = 9 'size w e ScaleHeight = 5565 ScaleWidth = 30 TabIndex = 1 Top = 570 Width = 90 End Begin MSComctlLib. treeView tvwTable Height = 6015 Left =-15 TabIndex = 0 Top = 405 Width = 2895 _ ExtentX = 5106 _ ExtentY = 10610 _ Version = 393217 HideSelection = 0' False Indentation = 0 LabelEdit = 1 LineStyle = 1 Style = 7 Checkboxes =-1 'true Appearance = 1 Export VB_Name = "frmViewData" Attribute VB_GlobalNameSpace = FalseAttribute VB_Creatable = FalseAttribute attributes = TrueAttribute attributes = FalseOption ExplicitPrivate myConn As ADODB. connectionPrivate myRecordSet As ADODB. recordsetPrivate strConn As StringPrivate bolDraging As BooleanPrivate lngLastPos As LongPrivate Sub SetControlSize () On Error Resume Next tvwTable. width = picLeftRight. left-tvwTable. left tvwTable. height = Me. scaleHeight-tvwTable. top picLeftRight. top = tvwTable. top picLeftRight. height = tvwTable. height txtSQL. left = picLeftRight. left + picLeftRight. width txtSQL. top = tvwTable. top txtSQL. width = Me. scaleWidth-txtSQL. left txtSQL. height = picUpDown. top-txtSQL. top picUpDown. left = txtSQL. left picUpDown. width = txtSQL. width myGrid. left = txtSQL. left myGrid. top = picUpDown. top + picUpDown. height myGrid. width = txtSQL. width myGrid. height = Me. scaleHeight-myGrid. topEnd SubPrivate Sub into conn_click () Dim dlg As New MSDASC. dataLinks Dim py As New ADODB. connection On Error GoTo ConnErr dlg. hWnd = Me. hWnd ACC. connectionString = strConn If dlg. promptEdit (ACC) = True Then strConn = ACC. connectionString If myConn. state = 1 Then myConn. close End If myConn. connectionString = strConn myConn. open RefreshView txtSQL. text = strConn End If Set YY = Nothing Set dlg = Nothing Exit SubConnErr: MsgBox Err. description, vbCritical, "system error" Set YY = Nothing Set dlg = NothingEnd SubPrivate Sub into count_click () Dim strSQL As String If Not tvwTable. selectedItem Is Nothing Then If tvwTable. selectedItem. parent Is Nothing Then strSQL = "select count (*) from" & tvwTable. selectedItem. text Else strSQL = "select count (*) from" & tvwTable. selectedItem. parent. text End If txtSQL. text = strSQL into query_click End IfEnd SubPrivate Sub into max_click () If Not tvwTable. selectedItem Is Nothing Then If Not tvwTable. selectedItem. parent Is Nothing Then txtSQL. text = "select max (" & tvwTable. selectedItem. text & ") From" & tvwTable. selectedItem. parent. text into query_click End If End IfEnd SubPrivate Sub into min_click () If Not tvwTable. selectedItem Is Nothing Then If Not tvwTable. selectedItem. parent Is Nothing Then txtSQL. text = "select min (" & tvwTable. selectedItem. text & ") From" & tvwTable. selectedItem. parent. text when query_click End If End IfEnd SubPrivate Sub cmdOpenTable_Click () Dim strSQL As String Dim strProvider As String strProvider = VBA. strings. LCase (myConn. provider) If Not tvwTable. selectedItem Is Nothing Then If tvwTable. selectedItem. parent Is Nothing Then strSQL = tvwTable. selectedItem. text Else strSQL = tvwTable. selectedItem. parent. text End If VBA. strings. inStr (1, strProvider, "oracle")> 0 Then strSQL = "Select * From" & strSQL & "Where rownum <200" Else strSQL = "select top 200 * From" & strSQL End If txtSQL. text = strSQL into query_click End IfEnd SubPrivate Sub into query_click () Dim myRS As New ADODB. recordset Dim strData As String Dim intFH As Integer Dim lCount As Long Dim lRecordCount As Long intFH = VBA. freeFile () On Error GoTo QueryErr myRS. open txtSQL. text, myConn, adOpenStatic, adLockReadOnly, ad1_text Open App. path & "\ temp.htm" For Output As # intFH Print # intFH, "