CODEWITHSUNDEEP
vbaexcelsql-server-2008-r2
GK1960
GK1960

Reputation: 121

Operation not allowed when the object is closed in VBA

I am new to this site and also to VBA, some how I managed to write VBA, but got above error. Please advise me any changes in my code. Thanks

Option Explicit
Const ConStrSql As String = "----"



Sub CopyDataFromDatabase()

Dim DataConn As ADODB.Connection
Dim StaffData As ADODB.Recordset
Dim DataFiels As ADODB.Field

Set DataConn = New ADODB.Connection
Set StaffData = New ADODB.Recordset

DataConn.ConnectionString = ConStrSql
DataConn.Open

On Error GoTo CloseConnection

With StaffData
.ActiveConnection = DataConn
.Source = "select ---"
.LockType = adLockReadOnly
.CursorType = adOpenKeyset

End With

On Error GoTo CloseRecordset

Worksheets.Add

For Each DataFiels In StaffData.Fields
ActiveCell.Value = DataFiels.Name
ActiveCell.Offset(0, 1).Select
Next DataFiels

Range("A1").Select
Range("A2").CopyFromRecordset StaffData
Range("A1").CurrentRegion.EntireColumn.AutoFit

CloseRecordset:
StaffData.Close


CloseConnection:
DataConn.Close




End Sub

when I remove CloseRecordset: StaffData.Close

I got columns listed in excel, but not data

Please any advise. Thanks

Upvotes: 0

Views: 10575

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

Your code defines Recordset but doesn't open it. Adding:

StaffData.Open

before:

Range("A2").CopyFromRecordset StaffData

should solve the problem.

Upvotes: 1

Related Questions