Inserting Data Into Access Table

[Guest Mark Connelly]

I successfully created an Access query recording user input for a given call into a table (using ODBC) for a short version of a survey we are trying to run. However, when I tried to do a query for the full survey (21 items), none of the data would get recorded into the Access table. When I then took went back to a short query (to enter the first few data entries), it worked. I have pasted the information from the full survey Access Query module below. Can you help me determine what is causing the problem? Is there a length limit to a query? We really need to be able to record the start date and time of calls into Access along with the other information. Thanks,

Mark

 

Here is the module information:

 

Module Name: AccessQuery

Database: Cancer

Connect String: ODBC;DRIVER={Microsoft Access Driver (*.mdb)}

SQL Query: INSERT INTO Diary (ID, Pain, Tired, Cough, Breath, Cope1, Cope1b, Cope2, Cope2b, Cope3, Cope3b, Cope4, Cope5, Cope6, Cope7, Cope7b, Cope8, Cope9, Cope10, Cope11, Cope12, Time) VALUES ('$RV[GetID]', $RV[GetPain]', '$RV[GetTired]', '$RV[GetCough]', '$RV[GetBreath]', '$RV[GetCope1]', '$RV[RecordCope1]', '$RV[GetCope2]', '$RV[RecordCope2]', '$RV[GetCope3]', '$RV[RecordCope3]', '$RV[GetCope4]', '$RV[GetCope5]', '$RV[GetCope6]', '$RV[GetCope7]', '$RV[RecordCope7]', '$RV[GetCope8]', '$RV[GetCope9]', '$RV[GetCope10]', '$RV[GetCope11]', '$RV[GetCope12]', '$RV_STARTTIME')

[SupportTeam]

I'd start progressively increasing the SQL statement and debugging it to find out at what stage it breaks, and then having a close look as to why it stops working..

 

I suspect that the date format expected by MS Access is a bit different they what is provided in the SQL statement using

 

'$RV_STARTTIME'

 

I'd try:

 

'#$RV_STARTTIME#'

 

but have a read through the MS Access manuals first.

 

BTW. Just about every database out there requires its 'date/time' inputs as part of the SQL statement to be different...