Need some help with this code. Worked in VB3, but not VB6


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Need some help with this code. Worked in VB3, but not VB6

  1. #1
    James Guest

    Need some help with this code. Worked in VB3, but not VB6


    Hi,

    I used this code to search an Access 2.0 Database in Visual Basic 3.0 for
    keywords. But, now that I am migrating this software to VB6, it does not
    work. I get an error on the, Dim ss As snapshot line - among others.

    The code searches an Access Table for key words, and returns records that
    contain any keyword that is entered. Phil Weber originally helped me with
    this back in 1997, but as you can see I have not gotten any smarter since
    then. Any help would be appreciated. Thank you - James


    Dim I As Integer
    Dim sQ As String
    Dim sResult As String
    Dim sSearch As String
    Dim sWhere As String
    Dim db As Database
    Dim ss As snapshot
    Const DB_Memo = 12

    Dim TestSearch As String
    TestSearch = InputBox("Enter Keyword To Search Question Banks.", "Search
    Question Banks For Keywords")


    If TestSearch = "" Then
    Exit Sub
    End If

    If TestSearch = "0" Then
    Exit Sub
    End If



    Set db = OpenDatabase(App.Path & "\fr.mdb")
    sWhere = "SELECT * FROM Table1 WHERE False"
    ' The WHERE false will return an empty recordset.
    ' You can get the table structure from it, field
    ' names, data types, etc. without the overhead
    ' of records

    Set ss = db.CreateSnapshot(sWhere)
    ' Empty the variable so we can reuse it
    sWhere = ""

    sQ = Chr$(34)
    ' Replace "database" with the string for
    ' which you want to search
    sSearch = TestSearch
    For I = 0 To ss.Fields.Count - 1
    If ss(I).Type = DB_Memo Then
    ' The following should be on a single line

    sWhere = sWhere & "[" & ss(I).Name & "] Like " & sQ & "*" &
    sSearch & "*" & sQ & " OR "

    End If
    Next

    ' Parse off last " OR "
    sWhere = Left$(sWhere, Len(sWhere) - 4)
    ' Build your your SQL string
    sResult = "SELECT * FROM Table1 WHERE " & sWhere
    ' Close and dispose of Snapshot object;
    ' we're done with it
    ss.Close : Set ss = Nothing

    ' Use sResult as data control's
    ' RecordSource
    Data1.RecordSource = sResult
    Data1.Refresh

    If txtChapter.Text = "" Then
    Message% = MsgBox(TestSearch & " - Does not appear anywhere in your question
    database. Would you like to search again?", 36, "MsgBox")
    If Message% = 7 Then
    Unload Me
    Exit Sub
    Else mnuPhil_click
    End If
    End If







  2. #2
    Michael Cole Guest

    Re: Need some help with this code. Worked in VB3, but not VB6

    "James" <Knightlite@redway.com> wrote in message
    news:3a92f1bd$1@news.devx.com...
    >
    > Hi,
    >
    > I used this code to search an Access 2.0 Database in Visual Basic 3.0 for
    > keywords. But, now that I am migrating this software to VB6, it does not
    > work. I get an error on the, Dim ss As snapshot line - among others.


    > Dim ss As snapshot

    Dim ss a Recordset

    > If TestSearch = "" Then

    If Len(TestSearch) = 0 Then

    > Set ss = db.CreateSnapshot(sWhere)

    Set ss = db.OpenRecordset(sWhere, dbOpenSnapshot)

    > If txtChapter.Text = "" Then

    If Len(txtChapter.Text) = 0 Then

    > Message% = MsgBox(TestSearch & " - Does not appear anywhere in your

    question
    > database. Would you like to search again?", 36, "MsgBox")
    > If Message% = 7 Then


    Use message box constants instead of numbers, ie, vbYesNo and vbYes.


    HTH


    --
    Regards,

    Michael D. Cole
    Mercury Computer Systems
    <www.mercsys.com.au>



  3. #3
    Arthur Wood Guest

    Re: Need some help with this code. Worked in VB3, but not VB6


    James,
    VB 6.0 no longer has a separate datatype called Snapshot. You can open
    a DAO recordset as a SnapShot type. You will need to make the necessary
    changes to use the DAO object model which is now available with VB 6.0.

    Similary, the db object no longer has a CreateSnapshot method.

    Arthur Wood


    "James" <Knightlite@redway.com> wrote:
    >
    >Hi,
    >
    >I used this code to search an Access 2.0 Database in Visual Basic 3.0 for
    >keywords. But, now that I am migrating this software to VB6, it does not
    >work. I get an error on the, Dim ss As snapshot line - among others.
    >
    >The code searches an Access Table for key words, and returns records that
    >contain any keyword that is entered. Phil Weber originally helped me with
    >this back in 1997, but as you can see I have not gotten any smarter since
    >then. Any help would be appreciated. Thank you - James
    >
    >
    > Dim I As Integer
    > Dim sQ As String
    > Dim sResult As String
    > Dim sSearch As String
    > Dim sWhere As String
    > Dim db As Database
    > Dim ss As snapshot
    > Const DB_Memo = 12
    >
    >Dim TestSearch As String
    > TestSearch = InputBox("Enter Keyword To Search Question Banks.", "Search
    >Question Banks For Keywords")
    >
    >
    > If TestSearch = "" Then
    > Exit Sub
    > End If
    >
    > If TestSearch = "0" Then
    > Exit Sub
    > End If
    >
    >
    >
    > Set db = OpenDatabase(App.Path & "\fr.mdb")
    > sWhere = "SELECT * FROM Table1 WHERE False"
    > ' The WHERE false will return an empty recordset.
    > ' You can get the table structure from it, field
    > ' names, data types, etc. without the overhead
    > ' of records
    >
    > Set ss = db.CreateSnapshot(sWhere)
    > ' Empty the variable so we can reuse it
    > sWhere = ""
    >
    > sQ = Chr$(34)
    > ' Replace "database" with the string for
    > ' which you want to search
    > sSearch = TestSearch
    > For I = 0 To ss.Fields.Count - 1
    > If ss(I).Type = DB_Memo Then
    > ' The following should be on a single line
    >
    > sWhere = sWhere & "[" & ss(I).Name & "] Like " & sQ & "*" &
    >sSearch & "*" & sQ & " OR "
    >
    > End If
    > Next
    >
    > ' Parse off last " OR "
    > sWhere = Left$(sWhere, Len(sWhere) - 4)
    > ' Build your your SQL string
    > sResult = "SELECT * FROM Table1 WHERE " & sWhere
    > ' Close and dispose of Snapshot object;
    > ' we're done with it
    > ss.Close : Set ss = Nothing
    >
    > ' Use sResult as data control's
    > ' RecordSource
    > Data1.RecordSource = sResult
    > Data1.Refresh
    >
    > If txtChapter.Text = "" Then
    > Message% = MsgBox(TestSearch & " - Does not appear anywhere in your question
    >database. Would you like to search again?", 36, "MsgBox")
    > If Message% = 7 Then
    > Unload Me
    > Exit Sub
    > Else mnuPhil_click
    > End If
    > End If
    >
    >
    >
    >
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center