save results from a query into an Array? - Newb Question

[tembenite]

I've been programming in VBA in Excel for awhile, but am new to Access.

Is there a way to run a SQL Query and store the values into an array for further manipulation through VBA? (I don't really want the query output to the screen). Thanks!!

Query I'd like to run:
SELECT accrualofdaysLOG.id, accrualofdaysLOG.Type, accrualofdaysLOG.numberdays
FROM accrualofdaysLOG
WHERE (((accrualofdaysLOG.id)=1));

 

[sfreeman@co.mer]

Should be very simple. From the Help file, here is an example of filling an array...

Sub FillArray()
Dim curExpense(364) As Currency
Dim intI As Integer
For intI = 0 To 364
curExpense(intI) = 20
Debug.Print "$" & curExpense(intI)

Next
End Sub

 

[gecko_1]

Would this work for what you want to do.

Set rs = db.OpenRecordset("SELECT accrualofdaysLOG.id, accrualofdaysLOG.Type, accrualofdaysLOG.numberdays FROM accrualofdaysLOG WHERE (((accrualofdaysLOG.id)=1));")

While Not rs.EOF
myArray(i,1) = rs.Fields("id")
myArray(i,2) = format$(rs.Fields("Type"), )

rs.moveNext
i = i + 1
Wend

You could format/manipulate as you but it into the array.

 

[Pat Hartman]

And yet another country is heard from:

You don't need to load the recordset into an array at all. Use the OpenRecordset method as shown in gecko_1's example. That opens a recordset for you to use in code. It is not visible to the user. You can move forward and backward as necessary.

 

[tembenite]

Would this work for what you want to do.

Set rs = db.OpenRecordset("SELECT accrualofdaysLOG.id, accrualofdaysLOG.Type, accrualofdaysLOG.numberdays FROM accrualofdaysLOG WHERE (((accrualofdaysLOG.id)=1));")

While Not rs.EOF
myArray(i,1) = rs.Fields("id")
myArray(i,2) = format$(rs.Fields("Type"), )

rs.moveNext
i = i + 1
Wend

You could format/manipulate as you but it into the array.

Thanks! That should work great!

 

[scooble]

Is it necessary to use;

'Dim rs as Object'

before using the;

'Set rs=....'

from the code posted above?

 

[jdraw]

Yes, and if you're using DAO you could use something like

Dim db as DAO.database
Dim rs as DAO.Recordset
..
..
Set db = Currentdb
Set rs = db.OPenrecordset(......)
...

 

[scooble]

when I used the code;

Dim db As DAO.database

it caused an error that says;

"Compile Error
Can't find project of Library"

So, within the VBA window, I opened 'Tools'/'References' and clicked
"Microsoft DAO 3.6 Object Library"

when I ran the code again, it caused an error on the following piece of code;

'Set db = CurrentDb'

And the Error Message this time was;

"Runtime Error 429
ActiveX component can't create object"

have I written the code wrong or am I missing something obvious here?

 

[jdraw]

when I used the code;

Dim db As DAO.database

it caused an error that says;

"Compile Error
Can't find project of Library"

So, within the VBA window, I opened 'Tools'/'References' and clicked
"Microsoft DAO 3.6 Object Library"

when I ran the code again, it caused an error on the following piece of code;

'Set db = CurrentDb'

And the Error Message this time was;

"Runtime Error 429
ActiveX component can't create object"

have I written the code wrong or am I missing something obvious here?

Sorry about not mentioning the "Microsoft DAO 3.6 Object Library", if you don't have it, and don't have it near the top, it defaults to ADO.
I don't see anything wrong with the code I gave in the previous response
Here's a sample from material that I use a lot

Sub AssignPeopleToTask()
Dim db As DAO.Database
Dim rsTask As DAO.Recordset
Dim rsAssigned As DAO.Recordset
Dim i As Integer
   On Error GoTo AssignPeopleToTask_Error

Set db = CurrentDb
Set rsTask = db.OpenRecordset("TblTask")
Set rsAssigned = db.OpenRecordset("TblTaskAssignment")
... other stuff here
...
...
AssignPeopleToTask_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure AssignPeopleToTask of Module Module1"

End Sub

Where are you trying to use/run the code??

I just googled the error you got and it seems to be an issue with registering the DAO Library

Here are some links I found with google
http://ezinearticles.com/?Say-No-to...tivex-Component-Cant-Create-Object&id=4994705

http://www.mvps.org/access/bugs/bugs0007.htm

 

[scooble]

thankyou for the help, however, I have no idea what it all means :-s

 

[NFSRacer]

I've been trying to implement this solution in a homework assignment that I've got, but am running into issues trying to increment an array for use in a query's results. Can someone please explain to me what Visual Studio means by 'Value of type 'Decimal' cannot be converted to '1-dimensional array of Decimal'' in the following VBA code snippet?

        Dim pay As Decimal
        Dim stuff() As Decimal
        Dim intCnt As Integer = 0
        Dim output As New List(Of Decimal)()
        Dim row As RRBCDataSet.PlayersRow
        For Each row In RRBCDataSet.Players.Rows
            stuff(intCnt) = row.Pay
            intCnt += 1
        Next

        frmPrint.lblAmount.Text = pay
        RRBC_Processing.NetPay(stuff(intCnt))

I'm sure I'm missing something somewhere, but forgive me when I say I'm not exactly that great at coding in VB when handling a database (I've never been able to wrap my head around them). Know that the method for 'NetPay' has an argument that needs to be passed that is in Decimal form. Could that have to do with it?

 

[spikepl]

Your code is not VBA but VB - a different kind of animal. Go to a VB forum.