Skip to main content
gamedev.net
🔒 Locked

Access2000 ADO problem

Started by Monder 6 replies 1.8k views
Original Post
Monder
Monder
Ok in Acess2000 VB I want to run an SQL query and find out if it returns any rows. So I''m using this code 
  
dim Rs as New Recordset
....
Rs.Open "SELECT * FROM [tManufacturers] " + SQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    If (Rs.EOF <> True) Then
The access help file says you can use an sql statement as the source argument for the open method in a recordset object, which is what I have done above. The SQL variable added to the "SELECT * FROM [tManufacturers] " is just a load of filtering stuff (WHERE [Name] LIKE ''A*'' etc) which gets worked out automatically. When there is no filtering stuff to add on so the SQL statment passed to the open function is just "SELECT * FROM [tManufacturers] " it returns all the rows from tManufacturers as it should and so Rs.EOF = false. Now when it adds in the filtering stuff, the SQL string, it always returns 0 rows, Rs.EOF = true. Using the debugger I got the SQL statement it was passing and put in a query using the SQL view and the query works fine so it is obviously valid SQL. So if it is valid SQL that returns rows why doesn''t it when I try to use it to open I recordset like that?
Monder
Monder
*bump*
Pepe
Pepe
I don''t know if this will be useful because I''m only know DAO but try it.

dim Rs as New Recordset
....
Rs.Open "SELECT * FROM [tManufacturers] " + SQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
Rs.MoveFirst
If (Rs.EOF <> True) Then
Monder
Monder
No that doesn''t help, thanks though.

The most annoying thing about this is that on MSDN there''s code examples where an SQL statement using ''WHERE'' stuff is used in the source argument of the RecordSet Open command. Does anyone have any idea why it wouldn''t be working?
WayfarerX
WayfarerX
Your ADO looks fine and I know for a fact that a WHERE clause is perfectly valid with Access 2000. I suggest posting the code that builds your query as well as what your debugger was saying you had built.
"There is no reason good should not triumph at least as often as evil. The triumph of anything is a matter of organization. If there are such things as angels, I hope that they're organized along the lines of the mafia." -Kurt Vonnegut
Monder
Monder
Well here it is


        
Function FilterCombo(CBox As ComboBox)
    Dim SQL As String
    Dim Rs As New Recordset
    Dim Cmd As New Command
    SQL = UpdateQuery
    Cmd.ActiveConnection = CurrentProject.Connection
    Cmd.CommandText = "SELECT * FROM [tManufacturers] " + SQL
    Cmd.Execute
    Rs.Open Cmd
    If (Rs.EOF <> True) Then
        Form.RecordSource = "SELECT * FROM tManufacturers " + SQL
    End If
    CBox.RowSource = "SELECT [ManufacturerID], [" + CBox.Tag + "] FROM [tManufacturers] " + SQL
    CBox.SetFocus
    CBox.SelStart = CBox.SelLength
    CBox.SelLength = 0
    CBox.Dropdown
End Function
Function UpdateQuery()
    Dim CurrentControl As Control
    Dim SQL_Query As String
    Dim GotOne As Integer
    GotOne = 0
    SQL_Query = ""
    For Each CurrentControl In Me.Controls
            With CurrentControl
                    If .ControlType = acComboBox Then
                        .SetFocus
                        If (.Text <> "") Then
                            If GotOne = 0 Then
                                SQL_Query = SQL_Query + "WHERE "
                                GotOne = 1
                            Else
                                SQL_Query = SQL_Query + "AND "
                            End If
                            SQL_Query = SQL_Query + "[" + .Tag + "] LIKE '" + .Text + "*' "
                        End If
                    End If
            End With
    Next CurrentControl
    UpdateQuery = SQL_Query
End Function


Basically I have a form which I will use for searching a table in my DB. On this form are a load of combo boxes. When you start typing in one of these it drops down using the text you are typing as a filter. So if you type an A only the things beginning with A will appear. Then when you use the record selectors at the bottom of the form you can browse through the table using the filter you put in e.g. A in the name combo box and you browse through all the records with an A as the first letter in the name field.

The point of the filter combo function is too accept a combo box and set the filter up. The UpdateQuery function works out all the WHERE filters. This can then be added onto a SELECT statement so I can set the filter of the form and the combo boxes.

Right I need to know if the SQL statement that I would use in the record source of the form will return any rows because when the record source of the form doesn't return any rows the form goes blank(Oh if someone can tell me how to stop this from happening I can avoid the whole problem).

Here are two sample SQL statments that I might use

SELECT * FROM [tManufacturers] WHERE [Name] LIKE 'a*' (doesn't return any rows even though it does when I put it in a normal access query thing)

SELECT * FROM [tManufacturers] (works fine)

This problem is really annoying if anyone could help I'd be really grateful

Thanks

[EDIT]Oh I'm doing the SQL command slightly differently cause I was looking on MSDN for different ways to do the same thing and that was one of them [/EDIT]

[edited by - Monder on December 12, 2002 4:44:40 PM]

[edited by - Monder on December 12, 2002 5:01:56 PM]
WayfarerX
WayfarerX
Try using:

LIKE ''a%''

That''s how it''s done using standard SQL. I think the Access query window may be replacing your * with % behind the scenes, but ADO isn''t.
"There is no reason good should not triumph at least as often as evil. The triumph of anything is a matter of organization. If there are such things as angels, I hope that they're organized along the lines of the mafia." -Kurt Vonnegut
Monder
Monder
Nope that didn''t work

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.