access and ADO - simple example

[stingrae]

Hi,

does anyone have a simple example, or know where to find one, for the following:


* Using an SQL statement to retrieve data from a table
* being able to explicitly say which field you wish to see (like the old recordset.fields("fieldname") method

all the examples i can find only relate to SQL server and not to access.

thanks all

[briancps]

hope this helps. This retreives the next student no from table

Public Function NextStudentNoGet() As Long
'get next Student no
Dim sSql As String
Dim cn As New OleDb.OleDbConnection(dbcConnection)
cn.Open()

'set sql string to reflect search criteria
sSql = "SELECT LastStudent FROM Startup WHERE ID = 1"

'open the ADO.NET Datareader
Dim cmd As New OleDb.OleDbCommand(sSql, cn)
Dim drd As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

drd.Read()
NextStudentNoGet = ToNum(drd!laststudent) + 1
drd.Close()