return value from sql into a variable

[scratch]

here's an example of what i want to do:

SQL = "SELECT Table1.equipment FROM Table1 WHERE (((Table1.clinic)='" & strCriteria & "'))"

then execute it and then store the values returned (equipment in this case) in a variable or array.

Thanks,

scratch

 

[jalldridge]

This link will give an indication of what you need to code:

http://www.access-programmers.co.uk/forums/showthread.php?t=87401

 

[Mile-O]

What exactly are you trying to do? How many records do you expect to return? etc?

 

[scratch]

how many records

ideally, i would like something to handle as many records as needed. That's why I mentioned that I would like to put the values in an array (not sure if vba supports dynamically sized arrays or not). But even if i could get one item in a variable, it would come in handy.

scratch

 

[jalldridge]

Scratch

The link I provided show how you can code for a select statment and get the recordset into a variant array. From that you can manipulate as needed.

 

[scratch]

access the variant

i changed things a little from the link but i can find 2 records based on the criteria in the sql which is correct. I can't seem to access the variant as an array though. Msgbox variant(0) gives me subscript out of range though i have two records. How can I access the elements?

thanks,

scratch

 

[scratch]

getting info from getrows

I didn't realize that it returned a 2d array so that problems is solved. the problem now is that vDBDetails = rst.GetRows doesn't give me the full size of the array. If I have 3 records returned vDBDetails = rst.GetRows(3) will give the full range. vDBDetails = rst.GetRows(rst.recordcount) doesn't work either.

For some strange reason i get 1 returned from the first msgbox and 3 with the second (you would think i would get 3 both times) with this code:

MsgBox rst.RecordCount
vDBDetails = rst.GetRows
MsgBox rst.RecordCount

Any suggestions?

scratch

 

[doulostheou]

If you posted more of your code showing what you are trying to do, it would probably make it easier to assist you. I'm not familiar with GetRows, but I do know that RecordCount is not accurate until you move to the last record (rst.MoveLast). I would guess that GetRows accomplishes the same thing, which is why you get a different recordcount afterwards.

 

[jalldridge]

Hey Scratch

Yep you need to do the move last, move first thing to get a correct count of records

 

[scratch]

solved

That solved it.

Thanks

 

[scratch]

ado v. dao

I got it to work with dao but I think I should go with ado if possible since I've read that dao is being phased out. Looks like a I basically got it to work in ado now but with a few issues:

in jailridges example, ado was connected to like this:

    Dim curconn As New ADODB.connection
    Dim rst As ADODB.Recordset
    Dim curdb As Database

    Set curdb = CurrentDb

    With curconn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "data source = " & curdb.name
        .Open

    End With

I get an error saying that I don't have permission to do this. So I tried this:

    Dim conDatabase As ADODB.Connection
    Set conDatabase = Application.CurrentProject.Connection
    Dim rst As ADODB.Recordset

I did get the records that I should get so I know it works. I was wondering why there's 2 ways of connecting. Is one better in some environments? If I stick with the way I'm doing it, am I asking for trouble?

One other strange incident with using ado: MsgBox rst.RecordCount returned -1. If I use .movelast, I get the error message "Rowset does not support fetching backward".

Thanks for any help,

scratch