problems querying from microsoft access

[fgperry]

Here is a description of what I'm doing.  We store calibration losses into a microsoft access database.  There are three colums...

ID, Frequency, Cal_Factor

ID is a list of consecutive integers.  1-401

Frequency is a list of frequencies.

Cal_Factor is the cal factor associated with the losses at those frequencies. 

The program that we run looks in this database at the Frequency column until it finds the right frequency and then reports the cal factor. 

However, recently we've seen a few problems.  When we update the cal table using a seperate vi, the IDs are updated, 402-802, the frequencies are constant, and of course the cal factor changes.  This is to recalibrate the system.  The problem is that after we calibrate, our program that reports the cal factor is not returning the correct values in the table.  As it steps through the frequencies, it reports the wrong frequency.  The  program iterates from row 1 to the last row.  So it should go from the first frequency in the list until it finds the right one.  Instead, it gets to a point, the same point each time, and then skips to a later value.  It will skip the same amount each time.  It never finds the correct frequency b/c of this.  Any ideas or is this clear enough.

[Robst]

Hi fgperry:

There must be something wrong with the Query on the Database, in the misterious program that reports the cal factor. Has the program behaved well before? How are U accessing the Database? Are U using the SQL Toolkit or using other software like LabSQL?

I think it would be helpful if you could upload your VI, or at least a screenshot of it. And if it's in version 7.0 much better

Regards.

Robst.

[Brian_Beal]

I'm not completely clear on what you are doing, but it appears that you are counting lines instead of doing a true query.  Are you assuming that the data is stored in order in the database?  You may want to include an ORDER BY statement in your query.

[fgperry]

I believe the problem may actually be due to the indexing in access, but i'm inexperienced with that.  B/c on different computers/test racks, I get different results.  Some of the tables within access work correctly when I call them with the vi below.  Others start on the wrong row when it begins looking for frequencies and cal factors.  Other do the problem as described below, but at different intervals.  I'm going to try and rewrite the vi that stores the cal factors to stop it from overwriting the ID column as seen in the image below.  I inherited this system.  I know that it works great until i try to update the cal tables.  I learned how to do this from the guy that used to own these vis.  I know I'm doing his method correctly.  However, I'm not positive that it works since it isn't.  I probably need to address this to an Access form instead, but I figured I'd try here first.  As for the SQL vis, it is what is provided by LabVIEW if I'm not mistaken.

Also, if you look at the image of the vi, the results from the query is what comes out incorrect.  So when you get to the section of the vi where it looks for the correct values, it is running the the array of data that is a result of the query. 

Message Edited by fgperry on 02-01-2006 08:37 AM

[fgperry]

Info on the vi that actually replaces data in the database.  Maybe this is where things are getting screwed up. 

SQL1.vi shows frame one of the case and SQL2.vi shows frame 2.

If you look at the image of the vi, the results from the query is what comes out incorrect.  So when you get to the section of the vi where it looks for the correct values, it is running the the array of data that is a result of the query. 

Look in the SQL vi to see how we do the table updating.  Ther is a vi called Clear Cal Db that does that runs the following SQL statement:

delete from Path1_Band1 where Frequency >=0.

[fgperry]

Update on this issue:

Description of problem: We have an access database.  We use LabVIEW SQL to access it.  Inside the access database, we have three columns.  The first is an indexing column (called ID) and the other two contain data (called Frequency and Cal_Factor).  The file is used to track cable loss in our automated test equipment (ATE).  The first time the database is created, the ID column ranges from 1-400 (400 points of data).  We then use another LabVIEW program to populate the two data columns.  After this is done, we can use our ATE.  We use LabVIEW to query the file row by row.  It uses the Fetch Next Record.vi until it finds the needed Frequency value.  Soon we have to recalibrate the rack.  At this point, we repopulate the access database.  We just open the database, and overwrite the existing data.  Now our ID column ranges from 401-800.  Our frequency column is overwrited with the same values and the Cal_Factor data is updated.  Now when we use Fetch Next Record.vi, it will not go row by row.  It may start out with the first 200 rows, then all of a sudden, it doesn't go to the next row, but instead goes to row 300.  When it fetches from 300-400, it will then go back to 201 and get the missing data up to 299.  Those numbers are just hypothetical.  It isn't always the same.  It behaves differently on each test system.

We can solve the problem by keeping our old database in tact (make a copy and rename it).  Assume we start with a database called "ATE_CalData".  We make a copy called "Copy of ATE_CalData".  Then our automated routine repopulates "ATE_CalData".  We then take the results from each table in this file and paste them into "Copy of ATE_CalData".  In this situation, the only thing we copy is the Cal_Factor column of data.  We now have the new data in the copy.  We can delete "ATE_CalData" and rename "Copy of ATE_CalData" to "ATE_CalData" and now Fetch Next Record.vi works fine.  This is actually an annoying process that we would like to quit doing.

Any ideas?  If I look in my add remove programs... i'm running SQL Toolkit v5.0 and I also have NI LabVIEW Database Connectivity Toolset 1.0.1.  I am running LabVIEW 7.1  If I need to give anymore info, please let me know.

[nyc_(is_out_of_here)]

I am not sure how you are "overwriting" your database when you are using INSERT statements.

Are you going in and manually deleting the previous 400 rows of calibration data?

I would use Brian Beal's suggestion.

[fgperry]

Oops.. we have a Clear Cal DB vi that runs a delete from "table name" where Frequency >=0.  This deletes all lines b/c all frequencies in the tables are always >=0.  We then use the INSERT into statement.  I understand the Order by command and why it may be good to use.  What I don't understand now is why does the FETCH vi not actually go to the next line in the data? 

Also, just a note.  I don't work with databases much as that is not my primary focus.  This program was already here when I began work. 

As far as what we are doing.  If we want to get a calibration value at 1700MHz or so.  The calibration data may not actually have a 1700 in the table.  So it looks at each line of the database.  When it fines a frequency greater than 1700, it pulls out the cal factor for this line and the previous line.  This way we can interpolate a cal value.  So my understanding is that the FETCH vi will go line by line to look in our file.  Why then does it skip a bunch of lines in the database.  Is the database not necessarily in the order as it appears when you open it?  I mean, the ID for each row goes from 401 - 800 in incremental steps.  So row one has 401, row two has 402, etc.  I guess I assumed it does.  If it is always in the correct order, it should work.  I'll try and ORDER BY, but if that doesn't salve the problem... I'm not sure what will.

[nyc_(is_out_of_here)]

It is important that the frequencies are unique.

Are you sure that the database table is being completely cleared?

And yes, what you see when you view a table through Access is not necessarily how they are stored in the actual database.

[fgperry]

So it seems like the order by was a very simple fix.  I just tried it out.  I just hope it is doing as I think it is.  All I did is the following.  If you look at vi_part1.JPG, you can see where it says

SELECT Frequency, Path_Loss FROM Path2_900MHz. 

I changed it to

SELECT Frequency, Path_Loss FROM Path2_900MHz ORDER BY Frequency ASC