How do I query a single record from a database?

[jasowiii]

I need to retrieve a record from a specific table within a .mdb file using it's name or primary key to search.
I have the database connectivity toolkit(LV7.1) and have not been able to figure this out.
Any help would be greatly appreciated.

Jim

[bdougr]

Open the database
SELECT table.[fieldvalue],table.[fieldvalue2] FROM table WHERE table.[datetimestamp] > #3/15/2005# AND table.[primarykey] = '1023'

The result will always return an array but what you want should be in the first element unless your primary key has dupes.

I can post examples but they are specific to my work

[jasowiii]

Bdougr,
Thanks for the reply, but it's a little over my head.
Maybe I should give some more info. We store test parameters in a MSAccess database.

Using LabView 7.1, I would like to retrieve a "record" or row based on a primary key (our part#)from a .mdb file and then use that data as my test parameters, I do not want to use the row# or record#. The database contains several tables so I also need to get to the specific table.

I have figured out how to get a "feild" or the hole thing but I don't know how to return just one specific row.

I have tried Select data, Fetch, Fetch all and Execute query VIs without success. Would "Execute query" be the way to do it? If so, what would be the correct SQL syntax to retrieve a record base on its primary key?

Jim

[Dennis_Knutson]

If you are unfamiliar with SQL syntax, you can open Access and design a query using it's built in tools. Then you can open the query and in design view, select View>SQL View. Bdougr gave you the general syntax. In your case, it would be more like SELECT * FROM tablename WHERE primarykeyname = whatever. By using the *, it will return the whole row.

[wd8ivl]

Jim,

I use a "udl" file to point LV to the database, that way I don't have to set up any ODBC data sources in Control Panel.

Here's an llb of the vi that I use to read the data.

Mike

[bdougr]

since you can get some data from the database we can assume you have an entry in the ODBC control panel

there are two methods to get your data
Open database - Select Record - close database
on select record under "optional Clause" use the SQL command "WHERE productID = '5873'"
the data returned will be the whole row and will be stored as Array of Variants
now take the variant data and bust them out of the array and use the Database Variant to Data function


or use SQL
open the database
execute query "SELECT * FROM table WHERE table.[productid] = '5489'"
get the data you want
close recordset
close database

I use SQL now (mostly) and find I use only a few commands
SELECT
UPDATE
INSERT

[wd8ivl]

Jim,

Doesn't look like my attachment got posted.

I'll try again...

[jasowiii]

Hey, It worked!

Thank you all very much for your help.

Jim