How to programmatically retrieve a single record from Access?

[JennyT]

I need to retrieve a single record from Access. I have two columns, "Sess" and "Subject" in my database. I want to retrieve the Session number ("Sess") for a particular subject and display it on the front panel. I have made up the attached VI based on an example I found. This VI displays the Session number for the first record, but I can't figure out how to designate a search for a specific record in the table.  Thanks!

[Dennis_Knutson · Accepted solution]

Do a google search for 'sql tutorial'. You need to learn how the SELECT statement works. The general syntax is:

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

[jcarmody]

I fetch all the recordset data and work on the array, rather than iterating through the recordset.  Like this:

[josborne]

Retrieving all the records and then iterating through them is extremely inefficient.  What if you have millions of records?

Just use this SQL statement:

SELECT Sess FROM yourtablename WHERE Subject = 'blahblahblah'

[falkpl]

Also consider adding the LIMIT clause to set the max number of records to 1 (add LIMIT 1 to end of sql statement)

if the querry is not known to be unique.  You can also sork prior to limit to get things like most recent or first or lest based on some other criteria.

[jcarmody]

---

@josborne wrote:

Retrieving all the records and then iterating through them is extremely inefficient.  What if you have millions of records?

Just use this SQL statement:

SELECT Sess FROM yourtablename WHERE Subject = 'blahblahblah'

---

Not sure if this was directed at my comment, but...  I didn't say retrieve all records, I said fetch the recordset data.  The recordset contains the records that match the query (which, in my snippet, looks much like your SQL statement).  You have another problem if your query can return millions of records but you only want one.