topic Re: Script Error when connecting to .mdb file with OLE DB in QlikView

Script Error when connecting to .mdb file with OLE DB

Wed, 11 Dec 2013 16:10:23 GMT

I am a newbie and am completing a tutorial in a book I bought. I am trying to connect to a .mdb (Access file) and when I hit "reload" I receive a Script Error pop-up:

Field not found - ,%Origin AirportID`,

`Origin Airport`>

SQL SELECT `%Origin Airport ID`,

`Origin Airport`

FROM 'Origin Airports`

I tested the connection when I created the connection script and I'm positive that I am pointed at the correct file. The script looks like this:

[Distance Groups]:

LOAD [%Distance Group ID],

[Distance Interval]

FROM

[..\Data Files\QVDs\Distance Groups.qvd]

(qvd);

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Users\Jerid\Documents\CIA_Stuff\Software\QlikView\QlikView_11_for_Developers\Chapters 1 - 11\QlikView Development\Airline Operations\Data Files\MDBs\Dimension Tables.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False];

LOAD `%Origin Airport ID`,

`Origin Airport`;

SQL SELECT `%Origin Airport ID`,

`Origin Airport`

FROM `Origin Airports`;

Re: Script Error when connecting to .mdb file with OLE DB

jaimeaguilar — Wed, 11 Dec 2013 16:32:50 GMT

Hi,

try with brackets [ ] :

LOAD [%Origin Airport ID],

[Origin Airport];

SQL SELECT `%Origin Airport ID`,

`Origin Airport`

FROM `Origin Airports`;

regards

Re: Script Error when connecting to .mdb file with OLE DB

Wed, 11 Dec 2013 16:41:24 GMT

Thanks Jaime! The SELECT wizard created that syntax... you would think that it would be correct??? Right? Is this a bug of some kind that should be reported?

Re: Re: Script Error when connecting to .mdb file with OLE DB

jaimeaguilar — Wed, 11 Dec 2013 16:50:26 GMT

I'm not sure if it's a bug, but definitely it can be misleading. This ' and this ` work as expected in SQL/ script. However, in QlikView script, fields with names that contain spaces need to be enclosed with [ ] instead of the other symbols. Another option if you need to get all the fields from Database Table you can use LOAD * in the QlikView Part.

Re: Script Error when connecting to .mdb file with OLE DB

Wed, 11 Dec 2013 17:19:12 GMT

Ok. I forgot about the "[ ]"'s being required for field names that contain spaces. But shouldn't the Qlikview Select Wizard check the field name and insert the correct symbol? Sounds like a nice functionality improvement suggestion. Thanks for you help Jaime!