SQL query variables in MS Access

Question

When writing a query for SQL Server, you can declare and use variables like this:

declare @test int
select @test = max(ID) from MyTable1
update MyTable2 set (...) where ID > @test
update MyTable3 set (...) where ID < @test

Is there a way to declare and use variables similarly when writing a query for MS Access?

I need to populate the variable with the result of another query and then use that value to perform insert/update operations. The query will be run from a .NET app.

Answer 1

In a way

parameters @test int;
select * from MyTable where ID = @test

However, you cannot use set @test = 1234, the parameter can be manually entered when the query is run or set in VBA.

Joel Coehoorn
In Query MS Access database in VB 2008

You use the classes in the System.Data.OleDb namespace to query access databases:

Using cn As New OleDbConnection("connection string here"), _
      cmd As New OleDbCommand("SELECT query with ? parameter here", cn)

    cmd.Parameters.Add("?", OleDbType.Int).Value = 1234

    MyCombobox.DataSource = cmd.ExecuteReader()
End Using

Further Notes re Edit to OP

Query 1

update MyTable2 set (...) where ID > (select max(test) from table1)

Query 2

update MyTable3 set (...) where ID < (select max(test) from table1)