asked on

Access 07 ADO Runtime error 91

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

SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

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

Start Free Trial

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

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

Start Free Trial

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Also, seems you would need something like

conn = CurrentProject.Connection

mx

jbakerstull

ASKER

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

ASKER

Working code is listed in my comments.