Cannot run a SELECT statment on an MS Access Query

Hi,

 Hope some one here can help me. I am creating an advanced search tool and I am
attempt to use an MS Access Query kind of like a view in MS SQL. Created a Query
in Access that prefoms an INNER JOIN, and I need To run a SELECT Statment on the
query. Howerver, every time I try I get the folowing error.

The Microsoft Jet database engine cannot find the input table or query
'tblPropsQuery'.  Make sure it exists and that its name is spelled correctly.

What is wierd is that I can copy my SQL string that I am building from the from
debug mode, and past it into an MS Access query analizer and the query pulls the
exact data I am looking for. This has to mean I am just trying to call the query
incorrectly from my ADO.NET code. I looked arround on line to see if my syntax
was off and could only find examples of how to call a query with parameters. I
don't want to use parammeters because I am building the sql string dynamicly
from user input. 

 Here is my code. It would be awsome if someone could tell me how to make this
work.

  

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSearch.Click


        Dim sql As New StringBuilder
        Dim count As Integer = 0
        Dim where As Boolean = False
        sql.Append("SELECT DISTINCT * FROM tblPropsQuery")

        ' Test to find out if we will use the WHERE clause or not
If Me.ddlLocation.SelectedIndex = 2 Or _
            Me.ddlLocation.SelectedIndex = 3 Or _
            Me.ddlLocation.SelectedIndex = 4 Or _
            Me.ddlLocation.SelectedIndex = 5 Then
            where = True
        End If
        If Me.rblPropertyType.SelectedIndex = 0 Or _
        Me.rblPropertyType.SelectedIndex = 1 Then
            where = True
        End If
        If Me.rblTransactionType.SelectedIndex = 0 Or _
            Me.rblTransactionType.SelectedIndex = 1 Then
            where = True
        End If
        If Not Me.txtSqFtFrom.Text = ""And _
           Not Me.txtSqFtTo.Text = ""Then
            where = True
        End If
        If where = True Then
            sql.Append(" WHERE")
        End If' Append WHERE clause as needed
If Me.ddlLocation.SelectedIndex = 2 Or _
           Me.ddlLocation.SelectedIndex = 3 Or _
           Me.ddlLocation.SelectedIndex = 4 Or _
           Me.ddlLocation.SelectedIndex = 5 Then
            sql.Append(" County='" &
Me.ddlLocation.SelectedValue.ToString & "'")
            count += 1
        End If
        If Me.rblPropertyType.SelectedIndex = 0 Or _
        Me.rblPropertyType.SelectedIndex = 1 Then
            If count > 0 Then
                sql.Append(" AND")
            End If
            sql.Append(" PropType='" &
Me.rblPropertyType.SelectedItem.Text & "'")
End If
        If Me.rblTransactionType.SelectedIndex = 0 Or _
            Me.rblTransactionType.SelectedIndex = 1 Then
            If count > 0 Then
                sql.Append(" AND")
            End If
            sql.Append(" TransactionType='" &
Me.rblTransactionType.SelectedItem.Text & "'")
        End If
        If Not Me.txtSqFtFrom.Text = "" And _
           Not Me.txtSqFtTo.Text = "" Then
            If count > 0 Then
                sql.Append(" AND")
            End If
            sql.Append(" SqFt BETWEEN " & Me.txtSqFtFrom.Text
& " AND " & Me.txtSqFtTo.Text)
        End If


        ' Hit the database
        Dim props As New ArrayList
        Dim strSql As String = sql.ToString
        Dim com As New OleDbCommand(strSql, cn)

        Try
            cn.Open()
            Dim reader As OleDbDataReader = com.ExecuteReader
            While reader.Read
                Dim prop As New ManageProps.Prop

                prop.PropId = IIf(IsDBNull(reader("PropId")), 0,
reader("PropId"))
                prop.PropName = IIf(IsDBNull(reader("PropName")),
"", reader("PropName"))
                prop.Add1 = IIf(IsDBNull(reader("Address1")),
"", reader("Address1"))
                prop.Add2 = IIf(IsDBNull(reader("Address2")),
"", reader("Address2"))
                prop.City = IIf(IsDBNull(reader("City")),
"", reader("City"))
                prop.State = IIf(IsDBNull(reader("State")),
"", reader("State"))
                prop.Zip = IIf(IsDBNull(reader("Zip")), "",
reader("Zip"))
                prop.County = IIf(IsDBNull(reader("County")),
"", reader("County"))
                prop.PropImgUrl = IIf(IsDBNull(reader("PropImgUrl")),
"", reader("PropImgUrl"))
                prop.AffiliateId =
IIf(IsDBNull(reader("AffiliateId")), "",
reader("AffiliateId"))
                prop.PropType = IIf(IsDBNull(reader("PropType")),
"", reader("PropType"))
                prop.TransactionType =
IIf(IsDBNull(reader("TransactionType")), "",
reader("TransactionType"))

            End While
        Catch ex As Exception
            Throw New Exception
        Finally
            cn.Close()
        End Try

        ' Assign arrylist to objects returned to a session veriable
        Session("PropSearch") = props

    End Sub  Thanks in advance.

Are you sure your connection string is pointing you to the correct Access
database?

You could try a simple query:  "Select * from tblPropsQuery"  to see
if the table is found.

Thank you for your response.

 Ya sure the connection string is good I have been using the same one from the
web.config several other places in the application. This time I am trying to use
an Access Query like it's a view so I dont have to write a join statment. 

 So question. Are you saying that this code should be working?

The way I am trying to call the Access Query would be the same as writing
something like this....

"SELECT tblProps.PropId, tblProps.PropName, tblProps.Address1,
tblProps.Address2, tblProps.City, tblProps.State, tblProps.Zip, tblProps.County,
tblProps.PropImgUrl, tblProps.AffiliateId, tblProps.PropType,
tblProps.TransactionType, tblListings.SqFt FROM tblProps INNER JOIN tblListings
ON tblProps.PropId = tblListings.PropId WHERE County='Orange'"

 You know I get an errror running this query as well. says I'm missing a
parameter.

Assuming you haven't misspelled a field name, there doesn't appear to be any
reason why that sample SQL should throw an error.  Could you show the actual SQL
that you tried in the first instance, that ran successfully within Access, but
errored from ASP.NET?