object is closed

[mike]

hi
i wrote a macro to connect to a database using ADO
to retrieve some records

Private Sub Display()
Dim sql As String
Dim con As ADODB.Connection
Dim rs1 As ADODB.recordset

sql = "select * from table"
MsgBox sql

Set con = New ADODB.Connection
Set rs1 = New ADODB.recordset
con.Open "DSN=" & DSN & ";Database=" & DB & ";UID=" _
& User & ";PWD=" & Password
rs1.Open sql, con

If Not rs1.EOF Then <<---debugger points here..
MsgBox "Yes"
Else
MsgBox "No"
End If
rs1.Close
con.Close
End Sub

Whenever i run this, i would get error "Operation is not allowed when
the object is closed." and the debugger will point to the statement "If
Not rs1.EOF Then...". I tried using "Do Until rs1.EOF" but the error is
the same..
Can anyone advise me on what is wrong..thanks very much

 

[Harald Staff]

Hi Mike

It looks pretty good from here, givetn that User, Database and Password are
declared and correct. I can't test it without the real things present. My
first guess would be that it doesn't open for some reason but fails to
report the error. Doublecheck your connection string at Carl Prothman's
site:
http://www.carlprothman.net/Default.aspx?tabid=81

Also, you haven't provided cursor and lock. That is always a good thing to
do:

rs1.Open sql, con, adOpenForwardOnly, adLockReadOnly

HTH. Best wishes Harald