转换访问SQL要VBA
问题描述：
我需要将这个访问SQL查询到VBA查询 - >转换访问SQL要VBA
SELECT informationTable.userID,
ConcatRelated('itemSold','[informationTable]',"userID='" & [userID] & "'") AS NameOfItemSold
FROM informationTable
GROUP BY informationTable.userID;
我试过的部份VBA
DoCmd.RunSQL ("SELECT informationTable.userID,
ConcatRelated('itemsold','[informationTable]','userID= ' & Chr(34) & [userID] & Chr(34) & ') AS NameOfItemSold
Into CRInformationTable
FROM informationTable
GROUP BY informationTable.userID;")
，但我得到的
错误RunSQL操作需要一个包含SQL语句的参数
答
我做了一些测试。假设用户ID是数字类型字段，看看这对你的作品：
DoCmd.RunSQL ("SELECT DISTINCT informationTable.userID, " & _
"ConcatRelated('itemsold','[informationTable]','userID=' & [userID]) AS NameOfItemSold " & _
"INTO CRInformationTable FROM informationTable;")
如果用户ID是文本类型：
"ConcatRelated('itemsold','[informationTable]','userID=" & Chr(34) & "' & [userID] & '" & Chr(34) & "') AS NameOfItemSold " & _
人权委员会，而不是（34）：
"ConcatRelated('itemsold','[informationTable]','userID=""' & [userID] & '""') AS NameOfItemSold " & _
+0
[userID]是一个简短的文本类型字段。 – BellHopByDayAmetuerCoderByNigh
+0
哎呀！你有没有发现我的额外）错字？再次编辑答案。 – June7
答
我我已经多次使用这个漂亮的工具，并取得了巨大的成功。
Creating the form
The form just needs two text boxes, and a command button. SQL statements can be quite long, so you put the text boxes on different pages of a tab control.
Create a new form (in design view.)
Add a tab control.
In the first page of the tab control, add a unbound text box.
Set its Name property to txtSql.
Increase its Height and Width so you can see many long lines at once.
In the second page of the tab control, add another unbound text box.
Name it txtVBA, and increase its height and width.
Above the tab control, add a command button.
Name it cmdSql2Vba.
Set its On Click property to [Event Procedure].
Click the Build button (...) beside this property.
When Access opens the code window, set up the code like this:
Private Sub cmdSql2Vba_Click()
Dim strSql As String
'Purpose: Convert a SQL statement into a string to paste into VBA code.
Const strcLineEnd = " "" & vbCrLf & _" & vbCrLf & """"
If IsNull(Me.txtSQL) Then
Beep
Else
strSql = Me.txtSQL
strSql = Replace(strSql, """", """""") 'Double up any quotes.
strSql = Replace(strSql, vbCrLf, strcLineEnd)
strSql = "strSql = """ & strSql & """"
Me.txtVBA = strSql
Me.txtVBA.SetFocus
RunCommand acCmdCopy
End If
End Sub
Using the form
To use the form:
Open your query in SQL View, and copy the SQL statement to clipboard (Ctrl+C.)
Paste into the first text box (Ctrl+V.)
Click the button.
Paste into a new line in your VBA procedure (Ctrl+V.)
哪里是你的代码的其余部分？您向我们展示的唯一东西是SQL语句。您将SQL语句与VBA代码组合在一起，因为VBA不是一种查询类型。 – braX
您必须将SQL语句转换为VBA'string'类型变量，并且有特定的语法。 –
另外，不能'运行'一个SELECT sql。 SELECT sql用于设置RecordSource对象或表单/报告RecordSource属性。只有sql可以运行 - UPDATE，INSERT，DELETE，SELECT INTO。您可以打开一个SELECT查询对象。但为什么打开查询对象？为什么不使用查询创建报告作为RecordSource？ – June7