Convert Access SQL To VBA

Question

I am in need of converting this Access SQL Query to a VBA Query ->

SELECT informationTable.userID, 
ConcatRelated('itemSold','[informationTable]',"userID='" & [userID] & "'") AS NameOfItemSold
FROM informationTable
GROUP BY informationTable.userID;

I tried ths 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;")

but I get an error of

A RunSQL action requires an argument consisiting of an SQL statement

Where is the rest of your code? The only thing you are showing us is the SQL statement. You combine the SQL statement with VBA code, as VBA is not a type of query. — braX
– braX, Commented Sep 27, 2017 at 19:00
You have to turn the SQL statement into VBA string type variable and there is specific syntax for that. — Scott Holtzman
– Scott Holtzman, Commented Sep 27, 2017 at 19:15
Also, can't 'run' a SELECT sql. SELECT sql is used to set a RecordSource object or form/report RecordSource property. Only action sql can be run - UPDATE, INSERT, DELETE, SELECT INTO. You can Open a SELECT query object. But why open query object? Why not create report using the query as RecordSource? — June7
– June7, Commented Sep 27, 2017 at 19:22
@ScottHoltzman - see my edit. I failed to realize I omitted my VBA syntax — BellHopByDayAmetuerCoderByNigh
– BellHopByDayAmetuerCoderByNigh, Commented Sep 27, 2017 at 20:03
@BellHopByDayAmetuerCoderByNigh - See comment by June7. Also, I am wary of ConcatRelated. I bet Access doesn't like that syntax. — Scott Holtzman
– Scott Holtzman, Commented Sep 27, 2017 at 21:42

June7 · Accepted Answer · 2017-09-28 20:25:20Z

1

I did some testing. Assuming userID is number type field, see if this works for you:

DoCmd.RunSQL ("SELECT DISTINCT informationTable.userID, " & _
    "ConcatRelated('itemsold','[informationTable]','userID=' & [userID]) AS NameOfItemSold " & _
    "INTO CRInformationTable FROM informationTable;")

If userID is text type:

"ConcatRelated('itemsold','[informationTable]','userID=" & Chr(34) & "' & [userID] & '" & Chr(34) & "') AS NameOfItemSold " & _

Instead of Chr(34):

"ConcatRelated('itemsold','[informationTable]','userID=""' & [userID] & '""') AS NameOfItemSold " & _

edited Sep 28, 2017 at 20:25

answered Sep 28, 2017 at 5:07

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BellHopByDayAmetuerCoderByNigh Over a year ago

[userID] is a short text type field.

June7 Over a year ago

Ooops! Did you find my extra ) typo? Edited answer again.

June7 · Accepted Answer · 2019-05-10 20:17:36Z

I've used this nifty tool many times with great success.

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

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.)

http://allenbrowne.com/ser-71.html

Collectives™ on Stack Overflow

Convert Access SQL To VBA

2 Answers 2

2 Comments

Comments

Hot Network Questions