LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

create a two dimensional table in Acess

Hi,
 
I am working with a database and the toolkit "the database connectivity tooset". I know how to create a new table from labview, but is it possible to create a "two dimensional" table from labview, i.e, where one table in Microsoft Access is linked to another one. I know how to do it in access, but it should be possible in program to crate a new table and this one has to be linked to another one then, just like the others. Does someone know how to do it??
 
Thank you!
0 Kudos
Message 1 of 8
(4,351 Views)
What do you mean with one table linke to another? On table contains a key (FK) from another table, where it is the primary key?
Using LV8.0
--------------------------------------------------------------------
Don't be afraid to rate a good answer... 😉
--------------------------------------------------------------------
0 Kudos
Message 2 of 8
(4,345 Views)
Hi,
 
well I have a table for a product that contains, say different data for the product, i.e. the date of the test, who is responsible for the test, where it was performed. Also, linked to the date (I guess it is the primary key), there are also some data related. so in my table in the database, next to the date, there is a "+"-sign, so I can open up the page of all the data related to the date. (plese see the attached file to get an idea). So, if I know would like to create a new table in my program (each product has its own table, so a new product=a new table), I would also like to create this "data" sheet that is connected to the data. Bu how do I do that?
 
I can easily just create a new table (i.e. a new product), just using the "DB Tools Create Table.vi". But I guess it is when I actually want to add the data to the database, where I create the "data" sheet? Using the "DB Tools Execute Query" and a SQL statement with INSERT INTO, is that the way to go?
 
Any tip at all are appriciated, because I hvae no idea!
0 Kudos
Message 3 of 8
(4,338 Views)
Hi
 
I assume you are not so familiar with databases, are you?
 
First I want to comment your previous post:


well I have a table for a product that contains, say different data for the product, i.e. the date of the test, who is responsible for the test, where it was performed. Also, linked to the date (I guess it is the primary key), there are also some data related. so in my table in the database, next to the date, there is a "+"-sign, so I can open up the page of all the data related to the date.
Well, did you create this database on your own? So you must have created two tables (table one with fields Datum, Responsible & Cost and table two with fields Place1, Place2, Place3 & Place4). Then you must have dragged on field from one table to another field in the other table in the "Relations" view of access. Doing so, you created a relation. Due to this, access now knows how this date is related and displays the "+"-sign. You can not assume that the date is the primary key.
In fact you do not have a "2D" table, but just different datasets from two tables connected to each other. This connection is created by relations.


So, if I know would like to create a new table in my program (each product has its own table, so a new product=a new table), I would also like to create this "data" sheet that is connected to the data. Bu how do I do that?
 
I can easily just create a new table (i.e. a new product), just using the "DB Tools Create Table.vi". But I guess it is when I actually want to add the data to the database, where I create the "data" sheet? Using the "DB Tools Execute Query" and a SQL statement with INSERT INTO, is that the way to go?

As mentioned before, this "datasheet" is just a dataset from a second table which is made displayable by the relation. So if you're question is "how to create a relation using LV", you are quite on the right way. First create two tables one of which has to contain a field where the primary key from the other table is linked to. Then you have to set a PK in one table (using SQL statements) and then build the relation (also using SQL statements). I can't tell you, if you have to define the foreign key in the second table too.
 
Have a look at the MS-Access help on how to assign relations and how to define primary keys. There may be something to take care off. Basically standard SQL should do - but using MS you never know ;).
 
As this is somehow complicated, I always create the database structure in MS Access and that's it - afterwards all is done programatically (write and read data). Usually I don't open the database again with Access unless there is a special task.
 
Hope this helps you a little bit otherwise come and ask again.
 
Thomas

 
Using LV8.0
--------------------------------------------------------------------
Don't be afraid to rate a good answer... 😉
--------------------------------------------------------------------
Message 4 of 8
(4,323 Views)

You can write to linked tables in Access with no problem. The deal is that you have to write to them separately, (as if they were 2 separate unlinked tables). Obviously, when you do write to the table with the foreign key, you need to include the field which is the key, (date in your case).

 

0 Kudos
Message 5 of 8
(4,314 Views)
Run the VI and Select a Product and the test result. When you are ready to write to the
database click on the buton. The database Example.mdb uses a DSN
(ODBC Administrator called ACCESS). You need to call it like that for the VI to work.
Hope this helps.
0 Kudos
Message 6 of 8
(4,309 Views)

I also don't understand the need to continuously create linked tables in Access. seems to me that you would be better off spending some time designing a more flexible architecture but if that's what you really need, then you add the CONSTRAINT keyword. An example with two tables would be:

CREATE TABLE UUT_RESULT
(
 ID             COUNTER CONSTRAINT UUT_RESULT_CONSTRAINT PRIMARY KEY,
 UUT_SERIAL_NUMBER     TEXT,
)

CREATE TABLE STEP_RESULT
(
 ID             COUNTER CONSTRAINT STEP_RESULT_CONSTRAINT PRIMARY KEY,
 UUT_RESULT        INTEGER NOT NULL,
 CONSTRAINT UUT_RESULT_FK FOREIGN KEY (UUT_RESULT) REFERENCES UUT_RESULT (ID)
)

This would link the table STEP_RESULT to UUT_RESULT by the ID column in UUT_RESULT.

0 Kudos
Message 7 of 8
(4,299 Views)

Hello everyone,

I want to create a new table in access from an existing one using sql command with labview.

I already have the command that i have to used from "SQL view" in ACCESS. I just want to know how to run that sql code on my existing query using labview. Any help will be appreciated!

Thanks!

0 Kudos
Message 8 of 8
(2,506 Views)