REM this line
On Error GoTo Err:
then tell us the line that the code errors on.
--
Regards,
Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Jez" wrote:
| Hi, Please help...
|
| This is my query below trying to extract data from Access into Excel. I am
| having trouble withthe second part of the query. It works fine to bring in
| the first SQL but then fails on the second and gives me this error message
| "Operation is not allowed when the object is open"
| I dont understand why its going wrong when I havent closed the connection
| between the database but only closed when created the recordset.. How can
I
| fix this problem, as I may want to add another SQL to it?
|
| Option Explicit
|
| Dim cnnDW As ADODB.Connection
| Dim rsDW As ADODB.Recordset
| Dim sQRY As String
| Dim strDWFilePath, strCSVFilePath, strDestFilePath, strDestFileName As
String
|
| Sub GetData()
|
| On Error GoTo Err:
| strDWFilePath = "H:\NCHO\Housing Services\Data Warehouse\HSG Data
| Warehouse.mdb"
|
| Set cnnDW = New ADODB.Connection
|
| Set rsDW = New ADODB.Recordset
| 'ASV N Week by Contract
| Sheet4.Range("B5:BB23").ClearContents
| cnnDW.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
| "Data Source=" & strDWFilePath & ";"
| sQRY = "TRANSFORM Count([qryNoAccess(byAppt)].WRNumber) AS
| CountOfWRNumber " & _
| "SELECT [qryNoAccess(byAppt)].CouncilName " & _
| "FROM [qryNoAccess(byAppt)] " & _
| "WHERE ((([qryNoAccess(byAppt)].BANumber) <> 'HSG0008 20') And
| (([qryNoAccess(byAppt)].AppointmentOutcomeID) = 'N') And
| (([qryNoAccess(byAppt)].ActionTypeID) = 'AS')) " & _
| "GROUP BY [qryNoAccess(byAppt)].CouncilName " & _
| "PIVOT [qryNoAccess(byAppt)].Week"
| rsDW.CursorLocation = adUseClient
| rsDW.Open sQRY, cnnDW, adOpenStatic, adLockReadOnly
| Application.ScreenUpdating = False
| Sheet4.Range("B5").CopyFromRecordset rsDW
| rsDW.Close
| Set rsDW = Nothing
|
| Set rsDW = New ADODB.Recordset
| 'ASV N Week by Neigbourhood
| Sheet4.Range("B25:BB79").ClearContents
| cnnDW.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
| "Data Source=" & strDWFilePath & ";"
| sQRY = "TRANSFORM Count([qryNoAccess(byAppt)].WRNumber) AS
| CountOfWRNumber " & _
| "SELECT [qryNoAccess(byAppt)].CouncilDistrict " & _
| "FROM [qryNoAccess(byAppt)] " & _
| "WHERE ((([qryNoAccess(byAppt)].BANumber) <> 'HSG0008 20') And
| (([qryNoAccess(byAppt)].AppointmentOutcomeID) = 'N') And
| (([qryNoAccess(byAppt)].ActionTypeID) = 'AS')) " & _
| "GROUP BY [qryNoAccess(byAppt)].CouncilDistrict " & _
| "PIVOT [qryNoAccess(byAppt)].Week"
| rsDW.CursorLocation = adUseClient
| rsDW.Open sQRY, cnnDW, adOpenStatic, adLockReadOnly
| Application.ScreenUpdating = False
| Sheet4.Range("B25").CopyFromRecordset rsDW
| rsDW.Close
| Set rsDW = Nothing
|
| cnnDW.Close
| Set cnnDW = Nothing
| frmData.Hide
|
| Exit Sub
|
| Err:
| MsgBox "The following error has occured-" & vbCrLf & vbCrLf &
VBA.Error,
| vbCritical, "HSG NA Trending"
| MsgBox VBA.Err
|
| End Sub
|
|
| Thanks,
| Jez
|
|
Similar Threads
