字段.frm

用vb编的一个数据库检索程序

VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "提取字段的信息"
   ClientHeight    =   4065
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   7695
   LinkTopic       =   "Form1"
   ScaleHeight     =   4065
   ScaleWidth      =   7695
   StartUpPosition =   3  '窗口缺省
   Begin VB.Frame Frame2 
      Caption         =   "提取的字段信息"
      Height          =   3975
      Left            =   3960
      TabIndex        =   1
      Top             =   0
      Width           =   3615
      Begin VB.ListBox List2 
         Height          =   3480
         Left            =   120
         TabIndex        =   5
         Top             =   360
         Width           =   3255
      End
   End
   Begin VB.Frame Frame1 
      Height          =   3975
      Left            =   120
      TabIndex        =   0
      Top             =   0
      Width           =   3735
      Begin VB.CommandButton Command1 
         Caption         =   "提取表"
         Height          =   375
         Left            =   2640
         TabIndex        =   6
         Top             =   600
         Width           =   975
      End
      Begin VB.ListBox List1 
         Height          =   2760
         Left            =   120
         TabIndex        =   4
         Top             =   1080
         Width           =   3495
      End
      Begin VB.TextBox Text1 
         Height          =   375
         Left            =   360
         TabIndex        =   2
         Text            =   "Text1"
         Top             =   600
         Width           =   1575
      End
      Begin VB.Label Label1 
         Caption         =   "输入你想要提取表的数据库名"
         Height          =   255
         Left            =   120
         TabIndex        =   3
         Top             =   240
         Width           =   2415
      End
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim s As String
Private Sub Command1_Click()
'连接所在提取表的数据库
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog= '" & Trim(Text1.Text) & "'"
'提取表的结构
Set rs = cn.OpenSchema(adSchemaTables)
'表的信息添加到列表框中
List1.AddItem "  " & "表的名称"
Do While Not rs.EOF()
List1.AddItem rs!TABLE_NAME
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
Private Sub List1_Click()
'连接数据库
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog='" & Trim(Text1.Text) & "'"
cn.Open
'提取数据表的信息
Set rs = cn.OpenSchema(adSchemaColumns)
List2.Clear
List2.AddItem "字段的名称" & "  " & "字段的类型" & "  " & "字段的长度"
'浏览整个表
Do While Not rs.EOF
'字段类型对应值
If rs!TABLE_NAME = Trim(List1.Text) Then
     If rs!data_type = "2" Then
     s = "smallint"
     ElseIf rs!data_type = "3" Then
     s = "int "
     ElseIf rs!data_type = "4" Then
     s = "real"
     ElseIf rs!data_type = "5" Then
     s = "float"
     ElseIf rs!data_type = "6" Then
     s = "money"
     ElseIf rs!data_type = "11" Then
     s = "bit"
     ElseIf rs!data_type = "12" Then
     s = "sql_variant"
     ElseIf rs!data_type = "17" Then
     s = "tinyint"
     ElseIf rs!data_type = "20" Then
     s = "bigint"
     ElseIf rs!data_type = "72" Then
     s = "uniqueidentifier"
     ElseIf rs!data_type = "128" Then
     s = "varbinary"
     ElseIf rs!data_type = "129" Then
     s = "varchar"
     ElseIf rs!data_type = "131" Then
     s = "nvarchar"
     ElseIf rs!data_type = "135" Then
     s = "datetime"
     End If
'把字段的名称、类型和宽度都添加到列表框中
List2.AddItem rs!COLUMN_NAME & "           " & s & "         " & rs!character_maximum_length
End If
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub