I have the same problem as pawel had (in that thread), i.e. cannot Insert or Update data in Access database. However, the reason is not that I am using some keyword in my field name - it's been many years using SQL, some things are "unwritten" rules. But this is the first time I am trying something in LV.
However, after losing the entire day for a job that I expected to do in 1 hour in the morning itself, trying many different options, and debugging upto the lowest level in Database toolkit VIs (stepping in and over) I saw some weird SQL statement being formed internally- so I tried what Troy has tried.
So posting new question.
I stopped using "DB Tools Insert Data.vi" and instead, generating my own query programmatically (in loop) and passing it to "DB Tools Execute Query" and then "DB Tools Free Object".
I am trying to read tables and it works ok - meaning database connection, table names are fine.
I am trying to delete record and it works ok - meaning I have sufficient rights to work on the database.
I am checking the programmatically generated query, and guess what - it IS also OK (I copy pasted the same query statement into MS Access SQL View, and it does - Insert, successfully) - meaning the query being generated is Correct.
BUT, I am unable to Insert/Update the database.
I don't see what exactly is generating the error. I stepped into upto the Nth level of DB Toolkit VIs, and just do not understand why at some point the error is generated (thanks to the "no documentation" type VIs - they just don't say what are they doing - no offence).
Sorry, am a bit frustrated since I just lost the entire work day trying to figure out what is going on and am in a very critical time. Yes, I like learning, and I learned there is something wrong with my program. Might be something silly or may be something with Access-LV.
I am still on LV 8.6 while using MSAccess2007 (the database file is stored in 2002-2003 format).
In the attachments, I have attached the database (please create UDL before running the DBTester.vi) and support subVIs.
As the name suggests, the DBTester is just for testing the database related functions, so is not a very user friendly interface. However, just a quick tip
Run the DBTester.vi
- select path of the UDL file,
- select a table (it's a typedef, and has only those tables as in the database - UMS_4-temp.mdb)
- Click on "Read All" and if it shows the data in the arrays below, it means you have connected to the database succesfully.
- Click on "Read Table" and it shows data of the currently selected Table, in the "Current Table" indicator.
- For now, we will work only with 5 tables - MOManagers_M, Brokers_M, Clients_M, Resources_M, Orders_M.
- In the Input array control, add textual data in each field, and then click "Insert" and it SHOULD insert a record into the selected "Table". Which it doesn't.
- Update and Delete use the first Input value and compare it with the first column name in the predefined set of arrays in "UMSArrayToCluster4.vi"
- Delete works fine (the particular record is deleted infact), which means, the conditioning is working too.
- The indicators of All tables show all the columns. In the Insert/Update queries, I am specifying the column names to be modified, still it gives some error. I thought the password field requires to be passed as [password] in the column name list, and I removed the masking still it gives the error. I have changed all the fields' datatype to "Text" (instead of Number, Date/Time, Password etc) and still I cannot insert/update a record.
If you are confused about how to use, just two steps below :
1) just write "Inputdata" in 5 string control elements in the Input array, and select table "MOManagers_M" and it should insert the data. first element of the Input array is considered for "loginid" field in MOManager, Resources, Clients, Brokers table (refer to case structure in ArrayToCluster4.vi), and loginid field WAS "indexed" with "No duplicates." which I changed to simple (not indexed, and duplicates are allowed - just for testing) which didn't work either.
2) Also, to test Update, since you have seen the data using "Read All" you may use any of that table's record taking the value in the first element and putting it into the first element of the Input array (which means it will be used for the "condition" and that record should be modified).
The SubVIs are for my actual project. But I guess it should not be difficult to debug if you directly check the sql statement that is generated just before the "DB Tool Execute Query.VI" in UMSUpdateTable(SubVI)4.vi.
I am curious to know where I am making mistake.
Thanks all.
