Using DB Tools Insert Data VI for a joined table

[schmir1]

I'm trying to use the Insert Data Vi to write data to joined tables. Is this possible?

[JRA]

Within the MS Access environment, it is possible to perform joins of various types (inner, left outer, right outer). From LabVIEW, entering a SELECT statement that performs an inner join results in the error message: Execute SQL: Unexpected end of text at SQL query: 'JOIN'. The SELECT statement is:
SELECT*
FROM TableA INNER JOIN TableB on TableA.Col2A = TableB.Col2B;

Valid join types from LabVIEW are LEFT JOIN and LEFT OUTER JOIN, but not RIGHT JOIN.

I produced this error SQL 1.1.4 and Access 7.0 driver and SQL2.0 with Access 97 driver.

Instead of using SQL of the form:
SELECT*
FROM TableA INNER JOIN TableB on TableA.Col2A = TableB.Col2B;

use the following:
PROCEDURE test; SELECT*
FROM TableA INNER JOIN TableB on TableA.Col2A = TableB.Col2B;

The
Access help file describes the "Procedure" statement, but they give almost no information on it other than you specify a procedure name and a single statement.

An alternative is to not use an inner join but to do it yourself. That is, rather than selecting * from the joined results, specify the actual columns that you want from each table and the conditions (e.g.: select tablea.col1a, tablea.col2a, tableb.col1b from tableA, tableB where tablea.col2a = tableb.col2b;)

[mikeporter]

Yes, depending upon the database you are using there are a variety of techniques for doing so. Foe example, with Oracle (and most other real databases) you can create a view that presents the contents of multiple tables as thought they were columns in a single large table. In some cases you can even use this view to insert and update information in all the dependent tables in one SQL statement.

If you are inserting data into seperate tables using multiple SQL statement be sure to always use transactions to protect the operation from errors that occur partway through the process.

What database are you going to be talking to?

Mike...

[schmir1]

I'm connecting to a SQL Server. I'm new to SQL servers so don't know about stored procedures and such. I'm an Access Designer and normal I would use VBA to write records. I would use a SELECT Query like the following:

SELECT Raw_Test_Subtable.Raw_Current, Raw_Test_Subtable.Raw_Voltage
FROM Raw_Test_Data INNER JOIN Raw_Test_Subtable ON Raw_Test_Data.Raw_Test_Data_ID = Raw_Test_Subtable.Raw_Test_Data_ID;

Then use AddNew method to add a new record in Raw_Test_Subtable for Raw_Current and Raw_Voltage.

I'm not having luck doing this with the toolset.

[mikeporter]

There is a known problem with the AddNew method. You can only insert one value with it. The documentation says you can give it arrays of fields and data valuews, but you can't. This is supposed to be fixed in V7. The workaround is to use AddNew to create the new record in the recordset and then use the Update method to insert the record's other values.

Mike...

[LVFan]

I have the same challenge like you. The most simple solution would be to write into the first table and receive the primary key for the inserted dataset. Now you can insert your data in the second table using this primary key as a foreign key. The problem is, that the first insert statement does not return a primary key value (also, I didn't find a stored procedure or another SQL-command which could realize this). So you have to requery your first table for the latest data set, to get the just inserted data set. (You can't be sure thats your data set because someone else could insert data immediatly after your statement).

For example:
"Insert into tblA (a,b) values (1,2)" //Insert data in table A
"select MAX(tblA.PrimID_tblA) from tblA" //Requer
y Primary Key in table A
"Insert into tblB (ForeiID_A,c,d) values (PrimID_tblA,3,4)" //Insert data in table B

I use a transaction which contain the statements above, to guarantee that all data set in both tables are removed if there is an error.

If you have found a better solution, I would be greatful for a hint.