Select query to Access from VBA not returning duplicate values

Question

Any help with this issue is greatly appreciated.

I am trying to retrieve from Access, by means of Select, multiple values with the same ID and have it pasted into an Excel sheet. I am running the code from Excel VBA.

The query I am using to retrieve said values is:

SELECT Role 
FROM Roles 
WHERE App_ID=(SELECT ID FROM Apps WHERE NAME='app name');

which is assigned to a variable and afterwards executed by using Set variable = cn.Execute(variable). The problem is that this query, executed from Excel VBA, returns only the first value found. Now, if I run this query from Access it returns every value with the ID for the specified app.

I have tried tried using INNER JOIN, IN, HAVING, etc. but it just wont retrieve all of the values into Excel. Like I said, the query works fine when used in Access so I know this must be limitation in Excel.

Thank you for any help you guys can provide.

AdamsTips · Accepted Answer · 2014-11-13 21:20:41Z

1

Assuming you are using ADODB in Excel, keep in mind that the Execute function returns a Recordset. You can loop through the Recordset to see the additional rows.

Set rng = ActiveSheet.Range("A2")
Set rst = cn.Execute(strSQL)

With rst
    Do While Not .EOF
        rng = CStr(!Role)
        Set rng = rng.Offset(1)
        .MoveNext
    Loop
End With

edited Nov 13, 2014 at 21:20

answered Nov 13, 2014 at 21:11

AdamsTips

1,80621 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Johnny Bones Over a year ago

I think the OP wants all records written to their spreadsheet. Can you expand on this to include that piece?

bilbo_strikes_back · Accepted Answer · 2014-11-13 21:26:50Z

1

'Applies to Access 2010
'Reference Microsoft ActiveX Data Objects 6.1 Library

Dim strSQL As String
Dim strDBPathName As String
Dim strConProvider As String
Dim strPersist As String
Dim conADODB As ADODB.Connection
Dim rsADODB As ADODB.Recordset

Set conADODB = New ADODB.Connection
strConProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"

'Database path name
strDBPathName = "Data Source=C:\Temp\Database.accdb;"

strPersist = "Persist Security Info=False;"

With conADODB
    .ConnectionString = strConProvider & strDBPathName & strPersist
    .Open
End With

strSQL = "SELECT Role FROM Roles WHERE App_ID=(SELECT ID FROM Apps WHERE NAME='app name')"
Set rsADODB = New ADODB.Recordset

With rsADODB
    .Open strSQL, conADODB, adOpenStatic, adLockPessimistic

    If Not (.EOF And .BOF) Then
'Range of spreadsheet to paste records
        Cells(1, 1).CopyFromRecordset rsADODB
    End If

    .Close
End With

Set rsADODB = Nothing
conADODB.Close
Set conADODB = Nothing

answered Nov 13, 2014 at 21:26

bilbo_strikes_back

5913 silver badges10 bronze badges

2 Comments

waka Over a year ago

Just for future reference: Try adding some comments to your posts so the topic starter may get an idea of what you are doing. :)

bilbo_strikes_back Over a year ago

Thanks waka. I appreciate the tip.

Collectives™ on Stack Overflow

Select query to Access from VBA not returning duplicate values

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related