Access DB with data from inputbox in Cimplicity


Thread Starter

Frank Deprest

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?
You add records using SQL with the INSERT Statement.
(store_id, stor_name, city, state, zip)
('TST2','Test Store','Nashville','NY",'00319')

Do not try to put a value in for autonumber as Access does that automatically