Can someone tell me how to use existing MS Access queries from Labview?

[PCKOST]

I am trying to get data from a MS Access database by executing a query that has already been designed in MS Access. I have tried the SQL tool kit, and also active X, but all I get are errors saying I cannot execute a select query. Does any one have any examples or advice?

Thanks in advance!

Paul Kostenick (Boeing)

[mikeporter]

Copy the query out of the access file and then execute it using the toolkit. Can you read the query from Access using ActiveX?

Mike...

[PCKOST]

Thanks Mike! I've tried that, but I get an error that states I can't execute a "select" query?

[mikeporter]

What is the text of the query you are trying to execute?

Mike...

[PCKOST]

Here is the query I am trying to execute.

SELECT ToolFamilies.ToolFamilyID, AssemblyMeasurements.Diameter, AssemblyMeasurements.Type, AssemblyMeasurements.GageLength, AssemblyMeasurements.NominalLength, AssemblyMeasurements.MinBreak, AssemblyMeasurements.NominalCountersink, ToolFamilies.Name AS Description
FROM (ToolTypes INNER JOIN ToolFamilies ON ToolTypes.ToolTypeID = ToolFamilies.ToolTypeID) INNER JOIN (AssemblyTypes INNER JOIN (Tools INNER JOIN ((Assemblies INNER JOIN AssemblyMeasurements ON (Assemblies.AssemblyID = AssemblyMeasurements.AssemblyID) AND (Assemblies.AssemblyID = AssemblyMeasurements.AssemblyID) AND (Assemblies.AssemblyID = AssemblyMeasurements.AssemblyID)) INNER JOIN (qrySelectLastAssemblyForTool INNER JOIN ToolAssemblyAssocs ON qrySelectLastAssemblyForTool.AssemblyID = ToolAssemblyAssocs.AssemblyID) ON (Assemblies.AssemblyID = ToolAssemblyAssocs.AssemblyID) AND (Assemblies.AssemblyID = ToolAssemblyAssocs.AssemblyID) AND (Assemblies.AssemblyID = ToolAssemblyAssocs.AssemblyID)) ON (Tools.ToolID = ToolAssemblyAssocs.ToolID) AND (Tools.ToolID = ToolAssemblyAssocs.ToolID) AND (Tools.ToolID = ToolAssemblyAssocs.ToolID)) ON (AssemblyTypes.AssemblyTypeID = Assemblies.AssemblyTypeID) AND (AssemblyTypes.AssemblyTypeID = Assemblies.AssemblyTypeID)) ON ToolFamilies.ToolFamilyID = Tools.ToolFamilyID
WHERE (((ToolTypes.Name)="Drill"));

[mikeporter]

In case you have any doubts left as to why it's not a good idea to let Access write SQL for you.... WOW!

Given this syntactical mishmash my guess at this point is that there's something in the statement that the drivers don't like--especially if you are using the ODBC interface.

Step 1: Can you talk to the database if you send it a real simple query like:

SELECT * from ToolTypes

That should dump the entire contents of the ToolTypes table. If this works there's something screwy in the syntax that is causing the problem. I could help you rewrite the query but would need to know the table structures that are in the database. If it doesn't work there is something wlse going on (access permissions?).

Mike...

[PCKOST]

Thanks Mike! I am a real novice at SQL. I took your advice and started using simple queries to get the hang of SQL. It looks like was the "syntactical mishmash". I was able to use a much simpler query string to get my information. It sure would be easier to just use the SQL generated by MSAccess, but that would be too easy.

Thanks for your advice.

[mikeporter]

Glad I could help. Here's a really good book on getting started with SQL...

The Practical SQL Handbook, 4^th Edition, J.S. Bowman, S.L. Emerson, M. Darnovsky,

Addison-Wesley Developer’s Press, 2001, ISBN: 0-201-70309-2

 (The 3^rd edition is still available online through Amazon, and is also very good…)

Mike...