Using ADO to connect to Access database from Excel error 3704

Question

When I use the code below to connect to an access database from Excel, I get error 3704 "Operation is not allowed when the object is closed." at line

Call .Offset(1, 0).CopyFromRecordset(rstRecordSet)

I can 'fix' this problem by commenting out the line

adoConnection.Close

but I really don't like that, or understand why it solves the problem.

Can anyone explain what's wrong and how to fix it?

Thanks

Private Const constStrDBPath As String = "H:\Projects\DP.mdb" Private Const constStrConnection As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & constStrDBPath & ";" & _ "Jet OLEDB:Engine Type=5;" & _ "Persist Security Info=False;" Public Function SelectStatement(strCommandText As String) As Object Dim adoConnection As New ADODB.Connection Dim adoCommand As New ADODB.Command Dim rstRecordSet As New ADODB.Recordset adoCommand.CommandText = strCommandText adoConnection.Open constStrConnection adoCommand.ActiveConnection = adoConnection 'create the recordset by executing command string Set rstRecordSet = adoCommand.Execute(, , adadoCommandText) Set SelectStatement = rstRecordSet ' clean up adoConnection.Close Set rstRecordSet = Nothing Set adoConnection = Nothing Set adoCommand = Nothing End Function Sub TestSelect() Dim rstRecordSet As Object Dim lngField As Long Set rstRecordSet = SelectStatement("SELECT * FROM tblSystem") If Not rstRecordSet Is Nothing Then With Sheet1.Range("A1") For lngField = 1 To rstRecordSet.Fields.Count .Cells(1, lngField).Value = rstRecordSet.Fields(lngField - 1).Name Next lngField Call .Offset(1, 0).CopyFromRecordset(rstRecordSet) End With End If End Sub

assuming you have a client-side cursor, I think you just need to set the recordset's ActiveConnection to Nothing before you close the connection — JosieP
– JosieP, Commented Dec 19, 2013 at 10:28

Massimo Fuccillo · Accepted Answer · 2013-12-19 11:45:09Z

Your function SelectStatement is not fetching (reading) anything from the database.

In plain terms your actual SelectStatement() opens a channel with Access, prepares the query, sets a cursor and then throws away all.

The cleanup phase has to be postponed after having read the last recordset.

I'd misunderstood how the recordset object worked. Both replies have pointed me in the right direction and I think with some work I'll get there now. Thanks.

Collectives™ on Stack Overflow

Using ADO to connect to Access database from Excel error 3704

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related