asked on

.Open source:=Src, ActiveConnection:=Connection

Now I have another problem. I trying to apply this connection.

I get the connection but cant get info to pull in. It stops at

.Open source:=Src, ActiveConnection:=Connection

Error "The connection can’t be used to perform this operation. It is either closed or invalid in this context."

Any help would be greatly appreciated.

''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Private conn As ADODB.Connection
Private Recordset As ADODB.Recordset

Public Sub OpenADO()
Dim dbpath As String
Dim Src As String
Dim Connection As ADODB.Connection
Dim Col As Integer
Dim Recordset As ADODB.Recordset
Dim As400 As Integer
Dim A1 As Range

'Without Password
' dbpath = "C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb"

'With Password
dbpath = "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\Volume.mdb;Jet OLEDB:Database Password=OPPS;"

Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open dbpath
End With

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter

Src = "SELECT [V05 CONV] ,[V05 GOVT], [Yes incentive] FROM [End_of_year_incentive] WHERE AS400 = " & TradeLimit.ComboBox1.Value

.Open source:=Src, ActiveConnection:=Connection

' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Sheet2.Range("a20").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

' Write the recordset
Sheet2.Range("a21").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

you define conn AND connection, open conn, but use Connection !!

Option Explicit
Private conn As ADODB.Connection
Private Recordset As ADODB.Recordset

Public Sub OpenADO()
Dim dbpath As String
Dim Src As String
Dim Col As Integer
Dim Recordset As ADODB.Recordset
Dim As400 As Integer
Dim A1 As Range

'Without Password
' dbpath = "C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb"

'With Password
dbpath = "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\Volume.mdb;Jet OLEDB:Database Password=OPPS;"

Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open dbpath
End With

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter

Src = "SELECT [V05 CONV] ,[V05 GOVT], [Yes incentive] FROM [End_of_year_incentive] WHERE AS400 = " & TradeLimit.ComboBox1.Value

.Open source:=Src, ActiveConnection:=conn

' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Sheet2.Range("a20").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

' Write the recordset
Sheet2.Range("a21").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
conn.Close
Set conn = Nothing
End Sub

ca1358

ASKER

Thank you!!!!!!!!!!

Guy Hengel [angelIII / a3]

note that you should really clean up the code, because as it is right now, you would run into the next error on the line
>connection.Close
with the error: object variable not set ...

ca1358

ASKER

Thank you for noting that.