MS Access中的SQL查询变量 [英] SQL query variables in MS Access

问题描述

为SQL Server编写查询时，可以声明和使用如下变量:

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

编写针对MS Access的查询时，是否有类似的方法来声明和使用变量?

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

我需要用另一个查询的结果填充变量，然后使用该值执行插入/更新操作.该查询将从.NET应用运行.

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.

推荐答案

以某种方式

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

但是，不能使用set @test = 1234，在运行查询或在VBA中设置查询时可以手动输入参数.

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

Joel Coehoorn
在在VB 2008中查询MS Access数据库

Joel Coehoorn
In Query MS Access database in VB 2008

您使用System.Data.OleDb命名空间中的类来查询访问 数据库:

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

其他说明重新编辑为OP

查询1

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

查询2

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

这篇关于MS Access中的SQL查询变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！