Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Load script - loop through all tables in Access database

Hi All

I have an access database that can contain x amount of identically structured tables.

I want to loop through all tables in this database and load them all into Qlikview in a single table.

I know Qlikview will automatically concatenate tables with the same fields so the real issue is how to make qlikview loop through them all regardless of the number.

Please can anyone advise?

Thanks

Dominic

Tags (3)
6 Replies
Not applicable

Re. :Load script - loop through all tables in Access database

Hello dom,

you don't have any possibility to create a macro or VBA program directly in Access to make the concatenation automatically in a single table before loading the data with Qlikview?

Perhaps is there any possibility to execute this macro from QlikView?

matt_crowther
Valued Contributor

Load script - loop through all tables in Access database

Dominic,

You need to first generate a list of all the tables; this can be done manually via a simple .xls list for example or automatically via a query of the Access DB's 'sysObjects' table.

Once you have the list you can then set a loop within you're load script (For i to n...) to load through the tables.

Take a look at the script of my SQL Extractor qvw as that should point you in the right direction - the password for the Hidden Script is '1234'.

http://community.qlik.com/media/p/154056.aspx

Hope that helps,

Matt - Visual Analytics Ltd

Not applicable

Load script - loop through all tables in Access database

Hello,

I am trying to adopt your script to help me get all the tables from my MSAccess db.

What I did is to change sys.objects to MSysObjects (else I get error that there is no sys.mdb file).

I use this string for connecting the db:

[Reg_2000-5;DBQ=D:\Path_to_db\Register03.mdb]

And I got the error: no read permitions to MSysObjectstable.

My dbase is MSAccess 2000.

danielrozental
Honored Contributor II

Load script - loop through all tables in Access database

I believe you should be able to read all tables from the ODBC connector.

Something like:

Tables:

sqltables;



Not applicable

Re: Load script - loop through all tables in Access database

Matthew Good afternoon,

You have this example to post again?

Thank you

Employee
Employee

Re: Load script - loop through all tables in Access database

ODBC CONNECT TO [MS Access Database;DBQ=PATH\yourbase.accdb];

Tbls:

SQLTABLES; // Retrieve table names

List:

LOAD

    TABLE_NAME

resident  Tbls

where TABLE_NAME LIKE 'Base*';

for i = 0 to NoOfRows('List') - 1

LET tablename=Peek('TABLE_NAME',$(i),'List');

//your load script here

next

Community Browser