使用Tablesample的Visual Basic RunSQL语法错误(Visual Basic RunSQL Syntax error using Tablesample)

使用Tablesample的Visual Basic RunSQL语法错误(Visual Basic RunSQL Syntax error using Tablesample)

我在Microsoft Access中有一个表单从文本文件导入记录到表中。 原始代码是这样的：

DoCmd.RunSQL "UPDATE QC_File SET QC_File.ToQC = 'x' " & _ "WHERE (((QC_File.ID) In (Select TOP " & VarPer & " PERCENT ID " & _ "FROM QC_File As DUPE " & _ "WHERE [DUPE].[HIST_USUS_ID] = [QC_File].[HIST_USUS_ID] And [DUPE].[Client1] = 'CLIENT' " & _ "ORDER BY [DUPE].[ID] DESC;)))"`

当我希望它随机拉10％时，这个代码只占据了TOP 10％所以我尝试了以下但我不断收到错误（“查询表达式中的语法错误”）并且它没有指定错误发生的行在，它只返回整个代码。

DoCmd.RunSQL "UPDATE QC_File SET QC_File.ToQC = 'x' " & _ "WHERE (((QC_File.ID) In (Select ID " & _ "FROM QC_File As DUPE Tablesample(10 PERCENT) " & _ "WHERE [DUPE].[HIST_USUS_ID] = [QC_File].[HIST_USUS_ID] And [DUPE].[Client1] = 'CLIENT' " & _ "ORDER BY [DUPE].[ID] DESC;)))"

我已经尝试过使用NEWID()但所有这一切都是以随机顺序返回TOP 10％。 在Visual Basic中编写SQL时是否无法使用Tablesample命令？

I have a form in Microsoft Access that imports records into a table from text files. The original code is this:

DoCmd.RunSQL "UPDATE QC_File SET QC_File.ToQC = 'x' " & _ "WHERE (((QC_File.ID) In (Select TOP " & VarPer & " PERCENT ID " & _ "FROM QC_File As DUPE " & _ "WHERE [DUPE].[HIST_USUS_ID] = [QC_File].[HIST_USUS_ID] And [DUPE].[Client1] = 'CLIENT' " & _ "ORDER BY [DUPE].[ID] DESC;)))"`

This code is only grabbing the TOP 10 percent when I want it to pull a random 10 percent so I tried the following but I keep getting an error ("Syntax error in query expression" ) and it does not specify what line the error is occuring on, it just returns the whole code.

DoCmd.RunSQL "UPDATE QC_File SET QC_File.ToQC = 'x' " & _ "WHERE (((QC_File.ID) In (Select ID " & _ "FROM QC_File As DUPE Tablesample(10 PERCENT) " & _ "WHERE [DUPE].[HIST_USUS_ID] = [QC_File].[HIST_USUS_ID] And [DUPE].[Client1] = 'CLIENT' " & _ "ORDER BY [DUPE].[ID] DESC;)))"

I already tried using NEWID() but all that does is return the TOP 10 Percent in random order. Is it not possible to use the Tablesample command when writing SQL in Visual Basic?

最满意答案

Access将在子查询中反对此FROM子句：

FROM QC_File As DUPE Tablesample(10 PERCENT)

Access SQL不支持Tablesample() 。

我建议你构建一个新的Access查询，它可以从子查询中完成你想要的。 一旦你有了这个工作，就用它作为子查询。

Access will object to this FROM clause in the subquery:

FROM QC_File As DUPE Tablesample(10 PERCENT)

Tablesample() is not supported in Access SQL.

I suggest you build a new Access query which accomplishes what you want from the subquery. Once that you have that working, use it as the subquery.