提取SQL語句中的主表名

VERSION 5.00
Begin VB.Form Form1
   Caption         =   "Form1"
   ClientHeight    =   6645
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   8865
   LinkTopic       =   "Form1"
   LockControls    =   -1  'True
   ScaleHeight     =   6645
   ScaleWidth      =   8865
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton Command1
      Caption         =   "Command1"
      Height          =   1215
      Left            =   3600
      TabIndex        =   2
      Top             =   3660
      Width           =   1950
   End
   Begin VB.TextBox Text2
      Appearance      =   0  'Flat
      Height          =   3705
      Left            =   120
      MultiLine       =   -1  'True
      ScrollBars      =   2  'Vertical
      TabIndex        =   1
      Top             =   2790
      Width           =   2175
   End
   Begin VB.TextBox Text1
      Appearance      =   0  'Flat
      Height          =   2520
      Left            =   90
      MultiLine       =   -1  'True
      ScrollBars      =   2  'Vertical
      TabIndex        =   0
      Top             =   105
      Width           =   8640
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim strTemp As String
Dim strTemp0 As String
Dim strTemp1 As String
Dim strOne As String '第一个"(" 前的字符串
'vic maio in 060414
'提取SQL語句中的主表
Private Sub Command1_Click()
    Me.Text2 = ""
    strTemp = Me.Text1.Text
    If strTemp = "" Then Exit Sub
    If InStr(1, strTemp, "select") = 0 Or InStr(1, strTemp, "from") = 0 Then
        MsgBox "語法錯誤！", vbInformation + vbOKOnly, "Error"
        Exit Sub
    End If
    '1,看有没有（）
    If InStr(strTemp, "(") > 0 Then
        '2看一下from 前没有（）
        strOne = Mid(strTemp, 1, InStr(strTemp, "("))
        If InStr(1, strOne, "select") > 0 And InStr(1, strOne, "from") > 0 Then
            '如果form前没有(),那直接就取出表名
            strTemp = strOne
        '3，看一下from 后没有（）
        ElseIf InStrRev(strTemp, "from") > InStrRev(strTemp, ")") Then
            strTemp = Mid(strTemp, InStrRev(strTemp, "from"), Len(strTemp))
        Else
            While InStr(1, strTemp, ")") > 0
                    '当from前有括号时
                    strTemp0 = Mid(strTemp, InStr(strTemp, ")") + 1, Len(strTemp)) '去正倒數第一個"）"前面的内容
                    Debug.Print strTemp
                    Debug.Print strTemp0
                    strTemp1 = Mid(strTemp0, 1, InStr(strTemp0, "(") - 1) '去掉倒數第一個"）"前面的内容
                    Debug.Print strTemp1
                    If InStr(strTemp1, ")") > 0 Then
                        strTemp = strTemp0
                    Else
                        If InStr(strTemp1, "from") > 0 Then
                            Debug.Print "end string"; strTemp1
                            strTemp = strTemp1
                        Else
                            strTemp = strTemp0
                        End If
                    End If
                Wend
            End If
    End If '1
    strTemp = Mid(strTemp, InStr(strTemp, "from"))
    strTemp = Mid(strTemp, InStr(strTemp, Space(1)) + 1) 'from 与表名前的空格
    '如果存在多个空格时
    While Mid(strTemp, 1, 1) = Space(1)
        strTemp = Mid(strTemp, InStr(strTemp, Space(1)) + 1) ' 去掉表名前的空格
    Wend
    Debug.Print Mid(strTemp, 1, InStr(1, strTemp, Space(1)))
    Text2.Text = Mid(strTemp, 1, InStr(1, strTemp, Space(1)))
End Sub

Private Sub Form_Load()
    '最复杂的
    Me.Text1.Text = LCase("select distinct a.ProductID,a.ProductNum,a.SupplyOfProductsType,a.ProductName,b.UnitName,isnull((select sum(e.CurrentQty) from ProductInit e where e.deleted=0  and e.ProductID=a.ProductID and e.CompanyID='C002'),0) as CurrentQty,isnull((select min(c.unitprice) from PurchaseItem c where c.deleted=0  and c.ProductID=a.ProductID),0)as UnitPrice,isnull((select d.ShopAvgCost from ProductPriceInfo d  where d.deleted=0 and d.ProductID=a.ProductID and d.ChildCompanyID='C002'),0) as ShopAvgCost,isnull((select min(e.CurrentCostPrice) from ProductInit e where e.deleted=0 and e.ProductID=a.ProductID and e.CompanyID='C002'),0) as CurrentCostPrice " _
    & "from ProductInfo a left join unit b on a.unitid=b.unitid where a.deleted=0 and b.deleted=0 and isnull(a.IsSet,'')='' and a.StopSale=0 and a.ProductID not in(Select ProductID from ProductPurview where deleted=0 and canview<>0 and ChildCompanyID='C002')")
    'from前没有（）
    'Me.Text1 = LCase("select distinct a.ProductID,a.ProductNum,a.SupplyOfProductsType, from ProductInfo a left join unit b on a.unitid=b.unitid where a.deleted=0 and b.deleted=0 and isnull(a.IsSet,'')='' and a.StopSale=0 and a.ProductID not in(Select ProductID from ProductPurview where deleted=0 and canview<>0 and ChildCompanyID='C002')")
    'from后没有（）
    'Me.Text1 = LCase("select distinct a.productid,a.productnum,a.supplyofproductstype,a.productname,b.unitname,isnull((select sum(e.currentqty) from productinit e where e.deleted=0  and e.productid=a.productid and e.companyid='c002'),0) as currentqty,isnull((select min(c.unitprice) from purchaseitem c where c.deleted=0  and c.productid=a.productid),0)as unitprice,isnull((select d.shopavgcost from productpriceinfo d  where d.deleted=0 and d.productid=a.productid and d.childcompanyid='c002'),0) as shopavgcost,isnull((select min(e.currentcostprice) from productinit e where e.deleted=0 and e.productid=a.productid and e.companyid='c002'),0) as currentcostprice from productinfo a left join unit b on a.unitid=b.unitid where a.deleted=0 and b.deleted=0 and isnul")
End Sub