Access Query via Excel (ADODB) - no error, but no output!

Hi,

I have the following problem: I am simply doing the following code in Excel to carry out an Access query:

Sub writedata()

Sheets("Staaten_Raw").Range("A1").CurrentRegion.Clear
Sheets("Agencies_Raw").Range("A1").CurrentRegion.Clear

Dim Datei As String
Datei = "S:\RS\RSF\RSF_PUBLIC\Modelle\RichCheap\Rich_Cheap_archive_neu.mdb"

' Create a connection object.
Dim ADOCon As ADODB.Connection
Set ADOCon = New ADODB.Connection

With ADOCon

.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = Datei

.CursorLocation = adUseClient
.Mode = adModeReadWrite

.Open

End With

' Create a recordset object.
Dim ADORec As ADODB.Recordset
Set ADORec = New ADODB.Recordset

Dim ADORec1 As ADODB.Recordset
Set ADORec1 = New ADODB.Recordset

ADORec.ActiveConnection = ADOCon 'aktive Verbindung zuweisen


'Staaten auslesen
ADORec.Open "RVA5", ADOCon
Workbooks("Spread_Trade_Tool.xls").Sheets("Staaten_Raw").Range("A1").CopyFromRecordset ADORec
Set ADORec.ActiveConnection = Nothing

ADORec1.ActiveConnection = ADOCon 'aktive Verbindung zuweisen

'Agencies auslesen
ADORec1.Open "LaenderUndAgenciesRVA5"
Sheets("Agencies_Raw").Range("A1").CopyFromRecordset ADORec1
Set ADORec1.ActiveConnection = Nothing

' Tidy up
If ADORec.State = adStateOpen Then
ADORec.Close
End If

If ADORec1.State = adStateOpen Then
ADORec1.Close
End If

Set ADORec1 = Nothing
Set ADORec = Nothing
Set ADOCon = Nothing

End Sub

The macro runs without showing any error (thereīs a connection to the Access DB as a .ldb file is created), but the copyfromrecordset does not write any data in the Excel sheet. However, when I open the query manually in Access, i get a result table (takes a few minutes as the query is rather complex). Can someone pls help?

Rgds
Christian

Nothing seems fishy to me. Can you please post your query?

Thanks,
V

The query RVA5 looks like this (itīs basically a cascade of queries beginning from RVA1 to RVA5):

SELECT RVA4.bloombergID, RVA3.ISIN, RVA4.maturity, RVA3.SecurityDes, RVA3.coupon, RVA4.Volumen, RVA3.Country, RVA4.issue_date, RVA4.RLZ, RVA4.genMIDkurs, RVA4.calcMIDyield, RVA4.calcASKyield, RVA4.calcBIDyield, RVA3.HochBIDspreadvsBunds, RVA3.HochASKspreadvsBunds, RVA3.TiefBIDspreadvsBunds, RVA3.TiefASKspreadvsBunds, RVA4.BIDspread_vs_bund, RVA4.ASKspread_vs_bund, RVA4.MIDspread_vs_bund, RVA3.HochMIDspreadvsBunds, RVA3.TiefMIDspreadvsBunds, RVA3.MWMIDvsBunds, ([MIDspread_vs_bund]-[MWMIDvsBunds])/[StdAbwMIDvsBunds] AS MID_T_WertvsBunds, RVA4.BIDspread_vs_home, RVA4.ASKspread_vs_home, RVA4.MIDspread_vs_home, RVA3.HochBIDspreadvsHome, RVA3.HochASKspreadvsHome, RVA3.TiefBIDspread_vs_Home, RVA3.TiefASKspread_vs_Home, RVA3.HochMIDspreadvsHome, RVA3.TiefMIDspread_vs_Home, RVA3.MWMIDvsHome, ([MIDspread_vs_Home]-[MWMIDvsHome])/[StdAbwMIDvsHome] AS MID_T_WertvsHome, RVA4.BIDspread_vs_swaps, RVA4.ASKspread_vs_swaps, RVA4.MIDspread_vs_swaps, RVA3.HochBIDspreadvsSwaps, RVA3.HochASKspreadvsSwaps, RVA3.TiefBIDspread_vs_Swaps, RVA3.TiefASKspread_vs_Swaps, RVA3.HochMIDspreadvsSwaps, RVA3.TiefMIDspread_vs_Swaps, RVA3.MWMIDvsSwaps, ([MIDspread_vs_swaps]-[MWMIDvsSwaps])/[StdAbwMIDvsSwaps] AS MID_T_WertvsSwaps, RVA3.StdAbwMIDvsBunds, RVA3.StdAbwMIDvsHome, RVA3.StdAbwMIDvsSwaps
FROM RVA3 INNER JOIN RVA4 ON RVA3.bloombergID = RVA4.bloombergID
WHERE (((RVA4.bloombergID) Not Like "*ED683467 Corp*" And (RVA4.bloombergID) Not Like "*EC089748 Corp*" And (RVA4.bloombergID) Not Like "*ED313299 Corp*" And (RVA4.bloombergID) Not Like "*ED633027 Corp*" And (RVA4.bloombergID) Not Like "*ED794944 Corp*" And (RVA4.bloombergID) Not Like "*GG737987 Corp*") AND ((RVA3.SecurityDes) Not Like "ITALY*" And (RVA3.SecurityDes) Not Like "*LGB*" And (RVA3.SecurityDes) Not Like "*SLOREP*") AND ((RVA4.Volumen)>=1000000000) AND ((RVA4.issue_date)>=#1/1/1994#) AND ((RVA4.RLZ)>=2 And (RVA4.RLZ)<=35))
ORDER BY RVA3.Country, RVA4.RLZ;

As is said, what I donīt understand: When I open the query in Access, I get a result table...

Does it actually query anything at all? I wouldn't have thought you can build queries in access and then call them through ADO when they use access syntax.

Start simple and create a query that just gets the top 10 of something and see if that works, if it does then it's your query, if it doesn't then you can't call access queries through ado like that