Importing Data from MS Access

Hello,

I'm wondering if anybody can point me in the right direction here - I've probably gone wrong somewhere really silly!

I have written the following code (well most of it = some of it is clearly borrowed from the coursework!), which looks through an array and creates an Access recordset for each variable within the array. It's then ordered by the Version ID so I only need the first record in the table, which I want to return to Excel.

The problem is, it returns no results. If the query is run manually then it works fine so I must have gone wrong linking to the database somewhere...

Ignore the Range("F7").Value for now - that's just there to test if the code works & I'll be adding a rowcount to the loop when this bit works.

Thank you lots! :)

Kind Regards,

Sarah

Set Con1 = New ADODB.Connection
Con1.ConnectionString = ConnString 'Zero in on the database

'Now open up the connection

Con1.Open

For d = LBound(PRNumber) To UBound(PRNumber)
If PRNumber(d) <> "" Then
Set ForecastRS = New ADODB.Recordset
ForecastSQL = "SELECT ForecastTable.[Version ID], ForecastDetailTable.Total FROM ForecastTable "
ForecastSQL = ForecastSQL & "INNER JOIN ForecastDetailTable ON ForecastTable.[Forecast ID] = ForecastDetailTable.[Forecast ID] "
ForecastSQL = ForecastSQL & "WHERE ForecastTable.PRNumber = 'PRNumber(d)' And ForecastTable.VersionStatus = '" & CurrentForecastVersion & "' And ForecastDetailTable.Year = 'Whole Project'"
ForecastSQL = ForecastSQL & " ORDER BY ForecastTable.[Version ID] DESC"
Call ForecastRS.Open(ForecastSQL, Con1)

If ForecastRS.RecordCount > 0 Then
ForecastRS.MoveFirst
Range("F7").Value = ForecastRS!Total
End If

Set ForecastRS = Nothing
End If
Next d

ForecastRS.Close
Con1.Close
Set Conn1 = Nothing
Set ForecastRS = Nothing