Store results of an SQL Query into a MD array in Access?

I am trying to get Access to store the results of SQL queries into a multi-dimensional array for later use. I am using the following setup...

aryArrayName(1,1) = DoCmd.RunSQL("SQLStatement;")

the debugger highlights ".RunSQL" and says "Expected Function or Variable"

I know the SQL statement is right because I tested it in the RowSource property for a list box and it returned a value no problem. I have tried storing the string for the SQL query into a variable and using it as the first param of RunSQL, but no luck there either. I am following the syntax listed in help for RunSQL. Am I going about this wrong or can I just not do this?

Any Ideas?

Thanks For Your Time

If you want to put the results of a query into an array, the easies way I know is to use ADO, build an ADO recordset, and use the recordset.getrows() method which automatically creates the array for you. I use this all the time in asp web pages so I can close the db connection right away.

DoCmd.RunSQL doesn't return a value - as it's a subroutine and not a function (or the other way around if you like)

You are attempting to assign DoCmd.RunSQL(...) to a an array -but only a value - ie as stored in a variable or returned by a function - can be assigned and hence the compile error.

As Doug says, you can use variantArray=OpenRecordset(sqlStr).getRows() for running sql that returns data, with DoCmd.RunSQL or db.Execute (I think) only for action queries / queries that do not return data.

First post so i'll try and give an answer.
This post was actually written on another message board by me 1 day before. I asked a similar question but ended up solving it myself. This may slightly differ from what you want but you should be able to use parts of it.

I needed to make a hybrid sql/value list combination to add to a combo box of which the select result from the combo box would filter a subform table.

anyways here we go sorry if some of it doesn't seem to flow as below here was posted on another forum.

------------------------------------

For anyone that may need it here is the answer. While i won't back it up as being the most efficient answer as i'm sure there may be a better way in existance. I'm pretty new to vb and it bamboozles me with all the stuff you don't have to create thats there to use especially with access integration.

However i feel kinda lame answering my own question but here it is.

i'll assume you have a table name ContractDetails with field ContractID and that your
combo box is called cboContractNumber. I'll also assume that the form with the combo box on it is called "Form_frmYourForm" and your using the dao record set rather then the ado (active data object) record sets since i haven't learnt to use it yet but i'm told dao is old school and ado is better. Anyways on with the show.

Please note however that i haven't added any validation error checking but the only part that needs it is you MUST check to see if the table query returns any rows before calling the MoveLast command. If there are no rows returned then you can't really move to the last record of a null set i guess :/ and you'll get a crash and burn situation.

I actually placed this code in the open event of the form but it could be used anywhere.

CODE

' variants can take string, integer whatever data we'll need one of these for
' the database field retrieval. I could use a string since i know the contractID is one
' but better to get into the habit of using variant for later more robust/reusable code
Dim strValueList As Variant
Dim strListQuery As String
Dim strFinalStringList As String

Dim wks As Workspace
Dim db As Database
Dim recContractDetails As DAO.Recordset

Dim iTotalrows As Integer
Dim iCounter As Integer

' set the query we are going to run
strListQuery = "SELECT [tblContractDetails].[ContractID] FROM tblContractDetails;"

Set wks = DBEngine.Workspaces(0)
Set db = wks.Databases(0)

Set recContractDetails = db.OpenRecordset(strListQuery, dbOpenSnapshot)
recContractDetails.MoveLast

iTotalrows = recContractDetails.RecordCount

' grab all the rows. Don't worry the variant will be automatically resized to a
' multi dimensional array
strValueList = db.OpenRecordset(strListQuery, dbOpenSnapshot).GetRows(iTotalrows)

iCounter = 0

' our first value of the field list is set to "All"
finalstring = "All;"

Do While iCounter < iTotalrows ' Inner loop.

' append to it all the extra values
finalstring = finalstring + strValueList(0, iCounter) + ";"
iCounter = iCounter + 1 ' Increment Counter.

Loop

Form_frmYourForm.cboContractNumber.RowSource = finalstring



Also note that you must add some extra code in the cboContractNumber onChange event. You'll have to go something like

CODE

if(chosen item from box == "All")
{
//then filter the table you have displayed with this sql command
docmd.runsql(select * from whatever_table)
}
// its one of the list items from the table so use the normal sql query
else
{
docmd.runsql(select * from whatever_table where contractID = cboContractNumber.value
}



The last bit was semi pseudo/vb/c++ code so if you've gotten the above to work you should be able to fix that up to work.

Anyways it took me some microsoft assistant help searching time and lots of bits and pieces on scrap paper to finally get it to work the proper way.

Anyone with a better/cleaner/more efficient solution please don't hesitate to post it. Else enjoy the code... maybe it can help someone else.

thanks for your time

------------------------------------

If you wish to see the full posting then you can at the following link

Click Here For The Link


I'm not advertising or anything so please don't get mad i merly thought the link may direct others that want to read the full thing to get the idea of what i did and was trying to do.

But again i don't think its the most efficient way so if people can tidy the code up make ti smaller and better then by all means as i'm not the best vb coder out there considering i've used it for around 2-4 weeks.

Anyways i hope it helps.