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