Access 07 ADO Runtime error 91

jbakerstull
jbakerstull used Ask the Experts™
on
When I run the subroutine with syntax code I receive run time error 91 Object variable or with block variable not set at line rst.ActiveConnection = conn.

I'm not sure exactly why I'm receiving this error. Goal is to display table details via immediate window.

Thanks
Sub Open_Table()
   Dim conn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field

    rst.ActiveConnection = conn

   Set conn = New ADODB.Connection
   
   Set rst = New ADODB.Recordset
    
    rst.Open "Select * from tblDisabilityElig", conn, adOpenKeyset, adLockOptimistic
    Do Until rst.EOF
       For Each fld In rst.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
Try reversing these two lines:

    Set conn = New ADODB.Connection
    rst.ActiveConnection = conn

mx

Top Expert 2010
Commented:
>>I'm not sure exactly why I'm receiving this error.

It's because you are trying to modify the recordset's properties before you actually instantiated it.

Move the line:

    rst.ActiveConnection = conn

Open in new window


below the lines where you create new instances of rst and conn.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Also, seems you would need something like


conn = CurrentProject.Connection

mx
jbakerstull

Author

Commented:
Final code is listed below.. thanks for you're help. Learning little more each day. 
Sub Open_Table()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

Set rst = New ADODB.Recordset

'Use the ADO connection that Access uses
Set conn = CurrentProject.AccessConnection

rst.Open "Select * from tblDisabilityElig", conn, adOpenKeyset, adLockOptimistic
    Do Until rst.EOF
       For Each fld In rst.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
   conn.Close
   Set conn = Nothing

End Sub

Open in new window

jbakerstull

Author

Commented:
Working code is listed in my comments.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial