getting run time error 91 [object variable or with block variable not set]

Question

i am trying the code below to pull from access db, but getting error 91, please suggest how to remove the error.

Private Sub CommandButton1_Click()

    Dim con As ADODB.Connection

    Dim rs As ADODB.Recordset

    Dim strConn As String

    Set con = New ADODB.Connection

    con.Mode = adModeReadWrite

    If con.State = adStateClosed Then

      strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\temp\db2.mdb;Persist    Security Info=False;"

      con.ConnectionString = strConn

      con.Open

    End If

    Dim startRow As Integer

    ***Set rs.ActiveConnection = con***

    rs.Open "select * from tbl_name"
    startRow = 3
    Do Until rs.EOF
       Cells(startRow, 4) = rs.Fields(0).Value
       rs.MoveNext
       startRow = startRow + 1
    Loop

    rs.Close
    Set rs = Nothing
    con.Close
    Set con = Nothing
End Sub

Thanks Joe, now i got it.. now its working..

user3103991
– user3103991

2013-12-15 09:06:51 +00:00
Commented Dec 15, 2013 at 9:06 — user3103991
– user3103991, Commented Dec 15, 2013 at 9:06

to StackOverflow · Accepted Answer · 2013-12-15 09:00:11Z

3

You call:

rs.ActiveConnection = con
rs.Open

without first creating an instance of the RecordSet:

Set rs = New ADODB.RecordSet

answered Dec 15, 2013 at 9:00

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Catalin B · Accepted Answer · 2017-02-03 12:23:41Z

0

I had this error while testing an application.

In my particular case, there was a problem with the Oracle Home used by the application. Therefore, the data source wasn't reachable.

answered Feb 3, 2017 at 12:23

Catalin B

1

Collectives™ on Stack Overflow

getting run time error 91 [object variable or with block variable not set]

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related