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.
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)
@test from within the query. Also, the query will be run from a .NET app, not VBA.
@ as a prefix for parameter names in Access. I Usually use : as the prefix because it is Standard SQL or arg_ because it shows up better in online forums ;)