F
For a automation project with a tramsimulator for educating tram drivers I have to write to a Access-database from Cimplicity. My Cimplicity
screens are used for simulating a defect on that tram (toggles a flag in the PLC). This is shown in a Alarm viewer OCX. When the users
acknowledges this 'alarm' (defect), I show a inputbox where the instructor can enter a score for that exercise (defect). Now I have to write
a (difficult) SQL-query in the Basic control engine (BCE) for adding a record to a table 'results' (with BCE-statement SQLrequest). This record contains the following: (a autonumber field), the number of the selected pupil for that exercise(displays in the Cimplicity screen as a text object), the date when the exercise took place (easy with built-in VB functions in BCE scripting), the score for that exercise (from a inputbox), yes or no this exercise was a examination (virtual boolean point in Cimplicity), the number of the instructor (displays in the Cimplicity screen as a text object)and a description of the exercise (from table in Access DB).
I have to manage about 1000 pupils in the database. In VB, I have programmed in DAO for generating several tables when these tables don't
exist. These exe-s are executed from Cimplicity when the instructor selects a pupil and a instructor (from the Access DB).
I have a table with all the pupils (their names, birth date, etc.), a table with all the instructors (name and number), a table with all the exercises (code of the exercise and description) and a table with all the results for all exercises and all pupils (a table with the results for every pupil is more difficult to execute I think).
I am very new with databases, so the problem lies in making the query. I use Access for that and change it in BCE to work with the Cimplicity data (points, string from inputbox).
The first problem is, when I insert the point data with POINTGET, I get weird errors from ODBC like 'syntax error' (I use string concatenation: & and spaces where necessary!).
The second problem is the following: is it possible to make a query with SQL to insert that record with that specific data? The query I have
figured out is a combination of a SELECT and a APPEND-query. SELECT to select the code and description of the exercise from the table
'exercises'; APPEND to append those 2 fields to the table 'results', together with the Cimplicity data. The criteria are filled in in the query design view for simulation like 'number of pupil = 14','number of instructor = 4' and 'code of the exercise ='D11'. I change that later in the BCE (with & and pointget etc.). Now, when I execute my query, I retrieve too many records (the pupil does more than once the same exercise, sometimes twice the same day). With a UPDATE query, all the records I have selected changes the score to the same etc. When I edit the SQL query in Access to exlude the 'results' table, I'm also confronted with similar problems.
Can someone help me out?
screens are used for simulating a defect on that tram (toggles a flag in the PLC). This is shown in a Alarm viewer OCX. When the users
acknowledges this 'alarm' (defect), I show a inputbox where the instructor can enter a score for that exercise (defect). Now I have to write
a (difficult) SQL-query in the Basic control engine (BCE) for adding a record to a table 'results' (with BCE-statement SQLrequest). This record contains the following: (a autonumber field), the number of the selected pupil for that exercise(displays in the Cimplicity screen as a text object), the date when the exercise took place (easy with built-in VB functions in BCE scripting), the score for that exercise (from a inputbox), yes or no this exercise was a examination (virtual boolean point in Cimplicity), the number of the instructor (displays in the Cimplicity screen as a text object)and a description of the exercise (from table in Access DB).
I have to manage about 1000 pupils in the database. In VB, I have programmed in DAO for generating several tables when these tables don't
exist. These exe-s are executed from Cimplicity when the instructor selects a pupil and a instructor (from the Access DB).
I have a table with all the pupils (their names, birth date, etc.), a table with all the instructors (name and number), a table with all the exercises (code of the exercise and description) and a table with all the results for all exercises and all pupils (a table with the results for every pupil is more difficult to execute I think).
I am very new with databases, so the problem lies in making the query. I use Access for that and change it in BCE to work with the Cimplicity data (points, string from inputbox).
The first problem is, when I insert the point data with POINTGET, I get weird errors from ODBC like 'syntax error' (I use string concatenation: & and spaces where necessary!).
The second problem is the following: is it possible to make a query with SQL to insert that record with that specific data? The query I have
figured out is a combination of a SELECT and a APPEND-query. SELECT to select the code and description of the exercise from the table
'exercises'; APPEND to append those 2 fields to the table 'results', together with the Cimplicity data. The criteria are filled in in the query design view for simulation like 'number of pupil = 14','number of instructor = 4' and 'code of the exercise ='D11'. I change that later in the BCE (with & and pointget etc.). Now, when I execute my query, I retrieve too many records (the pupil does more than once the same exercise, sometimes twice the same day). With a UPDATE query, all the records I have selected changes the score to the same etc. When I edit the SQL query in Access to exlude the 'results' table, I'm also confronted with similar problems.
Can someone help me out?
