simple retrieving from access

hi

i am simply testing my connectivity using access with vb. i want to retrieve the name when the age is 31. i am pasting my code. a compilation error comes at statement "Dim rs As ADODB.Recordset" when i click to a select button(i just have a textbox and a button in my form). the error is :
USER-defined Tyoe not defined???

why is this so?


Private Sub select_Click()
Dim rs As ADODB.Recordset

' Open the Recordset.
Set rs = conn.Execute("SELECT name FROM Test WHERE age =
31", , adCmdText)
If Not rs.EOF Or rs.BOF Then
rs.MoveFirst
Text1.Text = rs![Name]
End If

rs.Close
Set rs = Nothing
End Sub

Do you have Microsoft ActiveX Data Objects as a reference to your program?

yes i just added... and now error is coming in recordset..

i am unable to open the recordset... in asp with access i used to use as:
rs.Open "SELECT name FROM Test WHERE age = 31", dsn = "school"

but how do i do in vb with access?

any help?
thanks!

i now changed my code as==>but still i am not able to execute ..its saying database test is already open???

Private Sub select_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

' Open a connection.
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test"

conn.Open

' Open the Recordset.
Set rs = conn.Execute("SELECT name FROM Test WHERE age = 31", , adCmdText)
If Not rs.EOF Or rs.BOF Then
rs.MoveFirst
Text1.Text = rs![Name]
End If

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

End Sub

U had opened the table in your DB or your code program is running??

no i closed the database

i also made the following changes:
Open the Internet Services Manager.
Edit the Properties of the application.
On the Directory Security tab, edit Anonymous Access and Authentication Control.
Edit the Account used for Anonymous Access.
In IIS 4.0, clear the Enable Automatic Password Synchronization check box.

In IIS 5.0, clear the Allow IIS to control password check box.

then also the same error is coming

U can add some judge in the code:
if Conn.state=opened then
conn.close
endif

pls see code below. this is gettin on my nerves..i cannot understand the problerm

i already tried what u said: it gives error=>
Private Sub select_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

' Open a connection.
Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test;Persist Security Info=false;"
If conn.State = opened Then
conn.Close operation is not allowed when object is not closed
End If
conn.Open

' Open the Recordset.
Set rs = conn.Execute("SELECT name FROM Test WHERE age = 31", , adCmdText)
If Not rs.EOF Or rs.BOF Then
rs.MoveFirst
Text1.Text = rs![Name]
End If

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error Resume Next
If conn.State = adStateOpen Then 'Check the open state of the connection
conn.Close 'If it is open then close it
End If
'Set oConnection and rs to nothing to prevent memory leak problems
Set conn = Nothing
Set rs = Nothing
End Sub

Try it like this

Private Sub select_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

' Open a connection.
Set conn = ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test"

conn.Open

'Set recordset
set rs.ActiveConnection = conn

' Open the Recordset.
rs.open "SELECT name FROM Test WHERE age = 31"

If Not rs.EOF Then
Text1.Text = rs.Fields(0)
End If

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

End Sub

Is "test" the name of the database file, without an extension?

Do you have any error trapping enabled (on error resume next) that may be hiding some error messages?

Yes! Doug G! U are right!
Data Source Must be a mdb filename.