Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

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";

11 Replies
Not applicable

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

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
Honored Contributor II

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

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

Not applicable

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

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  ??

Not applicable

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

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.

Not applicable

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

QS1.png

Not applicable

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

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.

Not applicable

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

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

Not applicable

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

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.

Not applicable

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

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";

Community Browser