MsAccess working query do not work in QlikSense.... Stucked !

[#1]

Dear all,

I got several working queries on ms access database but when I insert flat script on QS I got Always strange error

saying

SQL##f - SqlState: 07001, ErrorCode: 4294964286, ErrorMsg: [Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 1.

Insufficient parameters . Expected 1.    ????

ON PREVIEW (within data selection area)  I SEE ALL DATA NORMALLY

This is the very simple query

REM LOAD "Tipo Doc",

    "N doc",

    Anno,

    codtab,

    tipo,

    Data,

    Codart,

    Descrizione,

    "Codcat_Ft",

    Quantità,

    Prezzo,

    "Cod iva",

    PrezzoScontato,

    Totriga,

    "Tipo_Omaggio",

    Omaggi,

    CostoUnit;

SQL SELECT "Tipo Doc",

    "N doc",

    Anno,

    codtab,

    tipo,

    Data,

    Codart,

    Descrizione,

    "Codcat_Ft",

    Quantità,

    Prezzo,

    "Cod iva",

    PrezzoScontato,

    Totriga,

    "Tipo_Omaggio",

    Omaggi,

    CostoUnit

FROM "D:\EOS\CONTROLLO_GESTIONE\MDB\EOSSRL"."CG_RIGHE_FT_01";

[#2]

Have you tried to create a database connection to the ms access database in the script first? And then try to open the table? Sense need a lib to be able to access the database.

when the connection are up, it should create a lib connection string.

LIB CONNECT TO 'Customer Service (username)';

Or you could alter the script to legacy mode from the settings.ini file and it should work without the lib.

[ogster1974]

Probably down to a missing column in your data source or issue in your data itself.

Try this to flush it out.

1. Run your query in Access and export to excel

2. Save excel file and using the Qlik Sense data loader bring in the excel sheet and let Qlik Sense build the load script.

3. Run the data load and see if your data goes in without any issues.

4. If works ok create your new connection to the access database and replace the Excel source with Access source.

Hope this helps

Andy

[#4]

Yes , of corse

LIB CONNECT TO 'Dinamico_Eos_BI_Local';

the very strange is that every kind of imported script about Ms Access Table&Queries has the same behavior and generate that error when you try to upload data (also without using LOAD ).

On preview area within connection I see all data with no problems. Ms AccessTables and also Queries.

Did you try to use it on last QS version  ??

[#5]

This is the error complete report on pink window.

Insufficient parameters .  Expected 1.

Connessione a Dinamico_Eos_BI_Local

Connesso

Si è verificato il seguente errore:

SQL##f - SqlState: 07001, ErrorCode: 4294964286, ErrorMsg: [Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 1.

L'errore si è verificato qui:

SELECT "Documenti st"."Tipo Doc", "Documenti st"."N doc", "Documenti st".Anno, "Tabella generica".codtab, "Tabella generica".tipo, "Documenti st".Data, "Documenti st sotto".Codart, "Documenti st sotto".Descrizione, Articoli.Codcat AS Codcat_Ft, "Documenti st sotto".Quantità, "Documenti st sotto".Prezzo, "Documenti st sotto"."Cod iva", "Documenti st sotto".Omaggi, "Documenti st sotto".CostoUnit FROM ("Documenti st" LEFT JOIN "Tabella generica" ON "Documenti st"."Tipo Doc" = "Tabella generica".codtab) LEFT JOIN (("Documenti st sotto" LEFT JOIN "Tabella iva" ON "Documenti st sotto"."Codiva" = "Tabella iva"."Cod iva") LEFT JOIN Articoli ON "Documenti st sotto".Codart = Articoli.Codart) ON "Documenti st"."Id doc" = "Documenti st sotto"."id doc" WHERE ((("Documenti st".Anno)=2015) AND (("Tabella generica".tipo)=93) AND (("Documenti st sotto".Codart)<>'-----' And ("Documenti st sotto".Codart) Is Not Null And ("Documenti st sotto".Codart)<>'999999992' And ("Documenti st sotto".Codart)<>'#SN3')) OR ((("Documenti st".Anno)=2015) AND (("Tabella generica".codtab)='C11') AND (("Tabella generica".tipo)=93) AND (("Documenti st sotto".Codart)<>'-----' And ("Documenti st sotto".Codart) Is Not Null And ("Documenti st sotto".Codart)<>'999999992' And ("Documenti st sotto".Codart)<>'#SN3')) ORDER BY "Documenti st".Data

I dati sono stati caricati. Correggere l'errore e riprovare a caricare.

[#6]

[#7]

My script runs through when i try to read from a MsAccess database. The only difference i see i comparison with yours, is that you are missing the .mdb extension. Don't know if that might have an impact or not?

FROM "E:\Qlik2Go\Qlik2Go Content\_Data\Databases\CustomerService.mdb".CallCtrFact;

I'm using the MS Access driver and Qlik Sense 2.1 Enterprise.

[#8]

Insert scritpt do all itself and normally works.

I'm using 2.02 but it sound strange that's connected to version.

If you like I can send db on privately mode

[#9]

You could also try to load all fields by using SQL Select * to see if the problem is related to missing fields as Andy mentioned earlier.

[#10]

Yes , I'm only using the select NOT the Load and the error arise the same.

LIB CONNECT TO 'Dinamico_Eos_BI_Local';

REM LOAD "Tipo Doc",

    "N doc",

    Anno,

    codtab,

    tipo,

    Data,

    Codart,

    Descrizione,

    "Codcat_Ft",

    Quantità,

    Prezzo,

    "Cod iva",

    PrezzoScontato,

    Totriga,

    "Tipo_Omaggio",

    Omaggi,

    CostoUnit;

SQL SELECT "Tipo Doc",

    "N doc",

    Anno,

    codtab,

    tipo,

    Data,

    Codart,

    Descrizione,

    "Codcat_Ft",

    Quantità,

    Prezzo,

    "Cod iva",

    PrezzoScontato,

    Totriga,

    "Tipo_Omaggio",

    Omaggi,

    CostoUnit

FROM "D:\EOS\CONTROLLO_GESTIONE\MDB\EOSSRL"."CG_RIGHE_FT_01";