SQL records in a MsgBox

Question

I have a VBS program that works great. My code below:

Dim qry, db, cs, cn, cmd

'Query to add all the following which date more than 20 days in the table ToUnfollow
qry  = "INSERT INTO ToUnfollow " & _
       "SELECT Name FROM Follow t1 " & _
       "WHERE t1.Joined < datetime(CURRENT_DATE, '-20 days') " & _
       "AND NOT EXISTS (SELECT 1 FROM ToUnfollow t2 WHERE t2.Name=t1.Name);"

db = "C:\Users\Quentin\Downloads\Quentin-Classementhashtags.db"
cs = "DRIVER=SQLite3 ODBC Driver;Database=" & db

'Connection to database
Set cn = CreateObject("ADODB.Connection")
cn.Open cs

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn

'Execute the SQL query
cmd.CommandText = qry
cmd.Execute

'Close the connection
cn.Close

I want to display all records from the SQL query in a MsgBox. I tried several solutions from several forums but none works for me.

`INSERT` statements don't return any records. If you want to display what was inserted you need to run the `SELECT` part of the statement as a separate statement. For further help show what you have tried, and explain how the results were different from what you expected. — Ansgar Wiechers, Oct 26 '16 at 14:41
My post is edited with a code which I tested. The solutions that I found asked to treat RecordSet and I am not very strong for that. — leyh, Oct 26 '16 at 15:07

score 1 · Accepted Answer · edited Oct 27 '16 at 11:42

My solution :

Dim qry, db, cs, cn, cmd, adoRec, name

'Query to add all the following which date more than 20 days in the table
'ToUnfollow
qry  = "SELECT Name, Joined FROM Follow t1 " & _
       "WHERE t1.Joined < datetime(CURRENT_DATE, '-20 days')"

db = "C:\Users\Quentin\Downloads\Quentin-Classementhashtags.db"
cs = "DRIVER=SQLite3 ODBC Driver;Database=" & db

'Connection to database
Set cn = CreateObject("ADODB.Connection")
cn.Open cs

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn

cmd.CommandText = qry
Set adoRec = cmd.Execute()

'Iterate through the results
While Not adoRec.EOF
  name = name & vbCr & adoRec(0) & " - " & adoRec(1)
  adoRec.MoveNext
Wend

MsgBox name

'Close the connection
cn.Close

SQL records in a MsgBox

1 Answers1