am getting a run time error '3704' Operation is not allowed when the object is closed.

[jimmy2times]

Hi Everyone,

I am trying to copy an access table into an excel workbook but I am getting the above error message. Can anyone tell me how to amend my code to get this to execute successfully?

Sub AccessToExcel()
'Declare variables
Dim dbconnection As ADODB.Connection
Dim dbrecordset As ADODB.Recordset
Dim dbfilename As String
Dim strSQL As String
Dim destinationsheet As Worksheet


'Set the assignment to the object variables
Set dbconnection = New ADODB.Connection
Set dbrecordset = New ADODB.Recordset
Set destinationsheet = Worksheets("Sheet1")


'Define the access database path and name
dbfilename = "C:\Users\filepath\Training\Database1_2007_2010.accdb"
'Define the provider for post 2007 db files
dbconnection.Provider = "Microsoft.ACE.OLEDB.15.0;Data Source=" _
& dbfilename & ";persist security info=False;"


'Use SQL SELECT & FROM clause for importing DB table
strSQL = "SELECT tblEmployees.* FROM tblEmployees;"


'Clear the destination worksheet
destinationsheet.Cells.Clear


With dbconnection
'Open the connection
.Open
'The purpose of this line is to disconnect the recordset
.CursorLocation = adUseClient
'.ConnectionTimeout = 60
End With


With dbrecordset
'Create the recordset
'Open strSQL, dbconnection
'Disconnect the recordset
Set .ActiveConnection = Nothing
End With


'Copy the tblEmployees recordset to sheet1 starting in cell A1
'Row 1 contains headers that will be populated at the next step
destinationsheet.Range("A2").CopyFromRecordset dbrecordset


'Reinstate field headers (assumes a 4 column table)
'Note that the id field will also transfer into column A
'so you can optionally delete column A
destinationsheet.Range("A1:E1").Value = _
Array("ID", "Header1", "Header2", "Header3", "Header4", "Header5")


'Close the recordset
dbrecordset.Close
'Close the connection
dbconnection.Close


'Release object variable memory
Set dbrecordset = Nothing
Set dbconnection = Nothing
Set destinationsheet = Nothing


End Sub

Any help would be much appreciated

 

[Norie]

Where do you get the error?

Also, why are you setting ActiveConnection to Nothing?

 

[jimmy2times]

It's okay- I have sorted this by running the code in a different way altogether

Thank you