assigning a query to an array

[Southern at Heart]

Can I assign the below query to an array?

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
strSql = "SELECT DISTINCT tblName.Phone FROM tblName;"
Set db = CurrentDb
Set qd = db.CreateQueryDef("", strSQL) ' create an unnamed, unsaved query
qd.Execute

....maybe like:
dim PhoneArray() as string
PhoneArray={the above query?}

 

[Dirk Goldgar]

Can I assign the below query to an array?

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
strSql = "SELECT DISTINCT tblName.Phone FROM tblName;"
Set db = CurrentDb
Set qd = db.CreateQueryDef("", strSQL) ' create an unnamed, unsaved query
qd.Execute

...maybe like:
dim PhoneArray() as string
PhoneArray={the above query?}

You can assign the *results* of the query to an array, though the array must
be declared as a variant, not directly as an array. Here's an example:

Dim rs As DAO.Recordset
Dim PhoneArray As Variant
Dim l As Long

Set rs = CurrentDb.OpenRecordset( _
"SELECT DISTINCT tblName.Phone FROM tblName")

With rs
If Not .EOF Then
.MoveLast
.MoveFirst
End If

PhoneArray = rs.GetRows(.RecordCount)
.Close
End With

For l = LBound(PhoneArray, 2) To UBound(PhoneArray, 2)
Debug.Print l, PhoneArray(0, l)
Next l

See the help topic on the GetRows method of the Recordset object for more
info.

 

[John Spencer]

You can use getRows to assign a recordset to an array

Public Sub drows()
Dim dbany As DAO.Database
Dim rstany As DAO.Recordset
Dim vArray As Variant
Dim strSQL as String

strSql = "SELECT DISTINCT tblName.Phone FROM tblName;"

Set dbany = CurrentDb()
Set rstany = dbany.OpenRecordset(strSQL)
vArray = rstany.GetRows(10)


End Sub


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

Can I assign the below query to an array?

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
strSql = "SELECT DISTINCT tblName.Phone FROM tblName;"
Set db = CurrentDb
Set qd = db.CreateQueryDef("", strSQL) ' create an unnamed, unsaved query
qd.Execute

...maybe like:
dim PhoneArray() as string
PhoneArray={the above query?}

Not like that. Try something more like:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim PhoneArray() as Variant

strSql = "SELECT DISTINCT tblName.Phone FROM tblName;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveLast
PhoneArray = rs.GetRows(rs.RecordCount)

rs.Close : set rs = Nothimg
set db = Nothing

Then refer to each row using PhoneArray(0, k)
where k is the row number.

 

[Marshall Barton]

Not like that. Try something more like:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim PhoneArray() as Variant

strSql = "SELECT DISTINCT tblName.Phone FROM tblName;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveLast
PhoneArray = rs.GetRows(rs.RecordCount)

rs.Close : set rs = Nothimg
set db = Nothing

Then refer to each row using PhoneArray(0, k)
where k is the row number.

Whoops - inadvertant send.

Now that you can get it into an array, I have to ask why?
Since you need to open a recordset, you should be able to
use the recordset almost as it it were an array.

Set rs = db.OpenRecordset(strSQL)
Do Until rs.Eof
' work on a row/record
rs.MoveNext
Loop

 

[Southern at Heart]

I think you're right. I thought it would make it more readable code to work
with an array, but maybe not...