Run a query, and return a vba array for each record returned 1

[ETID]

Hi Everyone,

Could anyone please give me an example of how to query a table and return all records for a given field to an array / variable in VBA?


thanks

 

[SkipVought]

Hi,
I am not sure what you are asking. Queries are returned in a Recordset Object, which is an array of sorts.

You asked how to query a table to get all the rows of a particular column. The SQL might look like this...

SELECT DISTINCT CUSTOMER_NAME FROM CUSTOMERS

where CUSTOMER_NAME is one field (column) in the CUSTOMERS table and DISTINCE is a keyword for specifying that you want only one occurrence of each value.

Hope this helps Skip,
Skip@theofficeexperts.com

 

[ETID]

Thanks for your reply Skip,

I'm new at sql from vba, but I was thinking more on the lines of...

sub get_sql_stuff()

All the declarations here (dims and set statements)


strSQL=" My SQL string with paramters "



Then some code to loop through the records returned by the sql statement and return only the values of "my_field"
and pass each one to a variable or array.

end sub

Or, am I way off base?

 

[SkipVought]

Well here's a way with this code snippit...

    Set rst = dbs.OpenRecordset(sQuery, dbOpenSnapshot)
' headings        
    For iCols = 0 To rst.Fields.Count - 1
        Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
    Next
    Range(Cells(1, 1), _
        Cells(1, rst.Fields.Count)).Font.Bold = True
' data
    Range("A2").CopyFromRecordset rst

The data is copied directly starting with A2. You could then get whatever data you wanted from the sheet.

Hope this helps. Skip,
Skip@theofficeexperts.com

 

[ETID]

Thanks but my bad....this query is in MSAccess

Sorry about that....

Would you happen to have a snippit to use in an Access module?

 

[SkipVought]

    With rst
        .MoveLast
        .MoveFirst
        lRecordCount = .RecordCount
        If lRecordCount > 0 Then
            vResults = Empty
            vResults = .GetRows(lRecordCount)
            For i = LBound(vResults, 2) To UBound(vResults, 2)
                For j = LBound(vResults, 1) To UBound(vResults, 1)
            ' do your row and column assignments
                Next j
            Next i
        End If
    End With

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ETID]

Ok, Now I really feel stupid...(I need to taken by the hand sometimes)


How do I pass my sql string,( which has a variable concatenated into it that is the parameter )?


something like...

strSQL = "SELECT alpha.PE_NUMBER, alpha.TRADE_NAME, alpha.MANFCTURER, alpha.TYPE, alpha.DATE, alpha.DATE_ASSGN, alpha.STATUS, alpha.TRACKING
FROM alpha
WHERE (((alpha.PE_NUMBER)= '" & PE_Number$ &"'));"

------------------------
then in the loop...

extract to a variable (each time through the loop) the records for alpha.TRADE_NAME...let's say, to a msgbox?


....Thanks for your patience

 

[SkipVought]

strSQL = "SELECT alpha.PE_NUMBER, alpha.TRADE_NAME, alpha.MANFCTURER, alpha.TYPE, alpha.DATE, alpha.DATE_ASSGN, alpha.STATUS, alpha.TRACKING
FROM alpha
WHERE (((alpha.PE_NUMBER)= '" & PE_Number$ &"'));"

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

then the loop is

    With rst
        .MoveLast
        .MoveFirst
        lRecordCount = .RecordCount
        If lRecordCount > 0 Then
            vResults = Empty
            vResults = .GetRows(lRecordCount)
            For i = LBound(vResults, 2) To UBound(vResults, 2)
            ' do your row and column assignments
                   MsgBox vResults(1, i)
            Next i
        End If
    End With

where 1 is the position of TRADE_NAME in the array.

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ETID]

Hmmm more woa....:-(


get object required error on this line...

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

 

[SkipVought]

did you open you database???

set dbs = workspaces(0).opendatabase.....

the syntax is specific to your database/odbc dirver etc Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ETID]

I'm working in the open database

can I use...

set dbs = currentdb ?

 

[SkipVought]

You are writing CODE -- it does not know if yer in a database or a WC Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ETID]

LOL...I told you I was green,


Let me flounder around here and I'll let you know how I made out.

 

[ETID]

Well Skip,...I got the white flag up.

I guess what I really need to see is the entire proceedure
spelled out.

I am working in an access form with access tables within an access database, so i am guessing that the ms default "workspace" is what I should be pointing to...

but I cannot get to run.

I'm missing a really cool statement (or 3) somewhere...so it's a catch 22 ....I can't express in your terms what I need.

Is there another way to do this?

any way thanks for your time....

 

[ETID]

Skip...I told you I was an idiot! (as if you hadn't guessed)

I finally got it to run,...seems I was passing a string to a number parameter in the sql statement.


A million thanks....for your code and patience!