CODEWITHSUNDEEP
sql.netms-access
andr111
andr111

Reputation: 3052

Pass parameter to a query from another query in Access

I have a parameterized query GET_CUSTOMER:

SELECT * FROM Customer WHERE id = [customer_id]

I want to call this query from another query and pass it a parameter:

SELECT * FROM GET_CUSTOMER(123)

Note the above code is not valid, it is here to give you an idea of what I'm trying to do. Is it possible to do this in MS Access?

UPDATE 1:

The queries I posted are for example. The actual queries are much more complex. I know I can use table joins, but in my specific case it would be much easier if I could run parameterized queries inside other queries (that are parameterized as well). I can't use access forms because I'm using access with my .NET application.

Upvotes: 4

Views: 10327

Answers (2)

andr111
andr111

Reputation: 3052

This is how I end up solving this with help of https://stackoverflow.com/a/24677391/303463 . It turned out that Access shares parameters among all queries so there is no need to specifically pass parameters from one query to another.

Query1:

SELECT * FROM Customer WHERE ID > [param1] AND ID < [param2]

Query2:

SELECT * FROM Query1

VB.NET code:

    Dim ConnString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=Database.mdb"
    Dim SqlString As String = "Query2"
    Using Conn As New OleDbConnection(ConnString)
        Using Cmd As New OleDbCommand(SqlString, Conn)
            Cmd.CommandType = CommandType.StoredProcedure
            Cmd.Parameters.AddWithValue("param1", "1")
            Cmd.Parameters.AddWithValue("param2", "3")
            Conn.Open()
            Using reader As OleDbDataReader = Cmd.ExecuteReader()
                While reader.Read()
                    Console.WriteLine(reader("ID"))
                End While
            End Using
        End Using
    End Using

Upvotes: 5

Albert D. Kallal
Albert D. Kallal

Reputation: 49329

You can build the SQL on the fly.

MyID = prompt or get from user some ID

strSQl = "Select * from tblCustomer where ID in " & _
       "(select * from tblTestCustomers where id = " & MyID

So you can nest, or use the source of one query to feed a list of ID to the second query.

Upvotes: -1

Related Questions