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

[James]

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

[Michael Cole]

"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>

[Arthur Wood]

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
>
>
>
>
>
>