SQL result into an Array

[kschuster]

VB newbie, hope you can help

My objective is to query a table for distinct values in PhyState, then use the resulting list to pass each state to another query where the results will be exported to Excel

Steps:
1. Get list of states
2. Loop through list of states passing the state code to a query to produce a record set that can be exported to Excel.

I think I want to run this query;

Query:
Select Distinct phyState From cptConv_AMLTExtract Order By PhyState Asc

Insert the results into and Array === >>> How do I do this?

Then loop through the Array extracting the value to be passed =====> How do I do this?

I hope this makes sense. Thanks in advance
Keith

 

[ByteMyzer]

It sounds like a rather long-way-around solution, and not entirely clear. What is it exactly that you are trying to accomplish? (Not how are you trying to do it, but what is the background behind it, and what is the intended result?)

 

[geekay]

For this you don't want to use an array. The RecordSet itself will serve the purpose of the array. Look into the code below.

Dim strSQL As String, FileName As String, i As Long
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset: Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "SELECT Distinct PhyState From cptConv_AMLTExtract Order By PhyState"
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly

Do While Not rs.EOF

'Change the Table Name and Field Name as required

strSQL = "SELECT TableName2.* FROM TableName2 WHERE " _
& "TableName2.FieldName = '" & rs!PhyState & "'"

i = i + 1

'In the line below change the value of FIleName as per your requirement.

FileName = "C:\ExportedFile" & Str$(i) & ".xls"

'If required refer TransferSpreadsheet Method in access help
'using the key word TransferSpreadsheet

DoCmd.TransferSpreadsheet(acExport), acSpreadsheetTypeExcel9, strSQL, FileName
rs.MoveNext

Loop

rs.Close
Set rs = Nothing