Ms Access Query

[Guest Wberm]

We are evaluating this system for purchase, but have been unable to query a MS Access database despite online technical support material.

 

* Using Windows 2000 Professional

* Set up the database name in ODBC Data Source Administrator (with "Microsoft Access Driver (*.mdb)

* Upgraded to MDAC 2.8 from MDAC 2.5

* Using VoiceGuide v. 5.2.1011

* Less than 20 modules in the script

* Tried different Connection String variations -- currently: ODBC;DRIVER={Microsoft Access Driver (*.mdb)}

* Tried removing the Connection String, even specifying the path to the database in the "Database or ODBC Database Source" field

* SQL query used: SELECT Amount_Due FROM Customers WHERE Account_Num='$RV[Get Account Num]'

* Database or ODBC Database Source: accounts.mdb

* Using Microsoft Access 2002

 

Script design: Caller enters account number, database is queried, account balance due is returned to caller.

 

Failure point: Always disconnects after account number is entered. Error: "OpenRecordset: 3146:ODBC--call failed."

 

Part of the relevant log file:

 

010609.75 0 [Get Account Num] Playing (C:\Program Files\VoiceGuide\system\ORIGINAL SOUND\EnterAccountNum.wav)

010609.95 0 PlaySoundStart ok [C:\Program Files\VoiceGuide\system\ORIGINAL SOUND\EnterAccountNum.wav]

010609.95 0 TimeoutClear

010609.97 0 wa(1896,34945300)

010612.77 0 wb(34945300)

010612.80 0 Play End line[0] (id=349453)

010612.81 0 ScriptEventCode 8001 iLineState=1300

010612.81 0 LsGetNbrsPlayWelcMsg EV_PLAY_FINISHED

010612.83 0 TimeoutSet 5 EV_TIMEOUT_REPLAYMSG

010613.77 0 dtmf 1 (65829,49,2)

010613.77 0 ScriptEventCode 49 iLineState=1301

010613.78 0 LsGetNbrsRxDigits 1

010613.78 0 [Get Account Num] Number Input 1

010613.80 0 path {1} not found

010613.80 0 TimeoutSet 6 EV_TIMEOUT_ENTERDATA

010613.81 0 TimeoutClear

010613.81 0 rv add [Get Account Num]{1}

010613.81 0 path {1} not found

010613.83 0 AddRVns [Get Account Num_PathTaken]{success}

010613.83 0 TimeoutClear

010613.84 0 [Access Query] DB Query

010613.84 0 db ODBC mode used

010613.88 0 RVreplace start: [sELECT Amount_Due FROM Customers WHERE Account_Num='$RV[Get Account Num]']

010613.88 0 RVns [PathSysVoice]{}[PathApp]{C:\Program Files\VoiceGuide\}[PathDataVm]{C:\Program Files\VoiceGuide\data\}[PathVgSys]{C:\Program Files\VoiceGuide\system\}[$RV_STARTTIME]{3/27/2004 1:05:56 AM}[$RV_DEVICEID]{0}[$RV_CIDNAME]{PHANHTHARATH SO}[PathApp]{C:\Program Files\VoiceGuide\}[$RV_CIDNUMBER]{5127315783}[Get Account Num]{1}[Get Account Num_PathTaken]{success}

010613.89 0 RVreplace end: [sELECT Amount_Due FROM Customers WHERE Account_Num=1]

010613.89 0 db About to execute a Retrieve type operation [sELECT Amount_Due FROM Customers WHERE Account_Num=1]

010613.91 0 OpenRecordset: 3146:ODBC--call failed.

010613.92 0 rv add [Hangup Time]{3/27/2004 1:06:13 AM}

 

Thanks for your help.

[SupportTeam]

Can you query that database directly when just specify the database filename and leave the "connect string" empty?

 

Can you please try that and post the trace.

 

Could you also please post the database and the script so that we may try to replicate what is happening.

[Wberm]

Thanks for your quick response.

 

When running the script, I can see the temporary version of accounts.mdb ("accounts.ldb") appear in the folder -- so VoiceGuide must be connecting to the database. The system disconnects within 5 seconds of entering the account number on the telephone keypad.

 

When we just specify the database filename and leave the "connect string" empty, the system disconnects immediately when the caller enters digits.

 

Both the database and script are small, to test the key feature we need, and I am uploading the requested files in a .zip archive. Let me know if you have any questions or need anything else. Thanks again.

VG.zip

[SupportTeam]

Looking at the SQL query in your script we can see that you've used quotes around the value of the Account_Num which you are trying to match. Account_Num field is defined in the database as a number, not as a string - so you should not be using quotes around the value.

 

please change:

 

SELECT Amount_Due FROM Customers WHERE Account_Num='$RV[Get Account Num]'

 

to be:

 

SELECT Amount_Due FROM Customers WHERE Account_Num=$RV[Get Account Num]

 

and it will work.

 

I did exactly that and was able to run your script with your database, with the value from Amount_Due column being retrieved and then spoken.

 

Test was done without using the ODBC driver - but you should see the same results regardless of whether the database is used directly (Jet mode) or via ODBC.

 

Also, when accessing the database directly (ie: in 'Jet' mode - not in 'ODBC' mode) you would have seen this error message in VG's log: "Data type mismatch in criteria expression" which does in fact describe what the problem was...

[Wberm]

I understand the problem now. Making the change does work. I will complete the demonstration for my partners. Thanks for the help.