Return Select query result to string variable in Access 2007

I need to be able to run a SELECT query and return all values it finds to a string variable with the results seperated by commas using VBA. Can anyone help?
LVL 1
JP_TechGroupAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
get the result as is from db and concatenate in the app

rs = cmd.open(sql)
whil not rs.eof
  if s="" then s = s & ", "& rs(0)
  if s<>"" then s = s & ", "& rs(0)
wend
0
 
Gustav BrockConnect With a Mentor CIOCommented:
I would use GetRows.
From the on-line help:
Sub RækkerMatrix()
  Dim dbs As Database, rst As Recordset, strSQL As String
  Dim varRecords As Variant, intI As Integer, intJ As Integer

  Set dbs = CurrentDb
  strSQL = "SELECT * FROM tblYourTable"
  Set rst = dbs.OpenRecordset(strSQL)
  rst.MoveLast
  rst.MoveFirst
  varRecords = rst.GetRows(rst.RecordCount)
  For intI = 0 To UBound(varRecords, 2)
    Debug.Print
    For intJ = 0 To UBound(varRecords, 1)
      Debug.Print varRecords(intJ, intI)
    Next intJ
  Next intI
  rst.Close
  Set dbs = Nothing
End Sub

Open in new window


Adjust the line
      Debug.Print varRecords(intJ, intI)
to fit your need.

/gustav
0
 
EDDYKTCommented:
0
 
JP_TechGroupAuthor Commented:
Used data gathering from one and string handling for the other. Thanks guys.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.