CODEWITHSUNDEEP
sqlexcelvbams-access
hectormtnezg
hectormtnezg

Reputation: 132

Select query to Access from VBA not returning duplicate values

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.

Upvotes: 0

Views: 1105

Answers (2)

bilbo_strikes_back
bilbo_strikes_back

Reputation: 591

'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

Upvotes: 1

AdamsTips
AdamsTips

Reputation: 1776

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

Upvotes: 1

Related Questions