topic Load script - loop through all tables in Access database in QlikView

Load script - loop through all tables in Access database

Wed, 30 Mar 2011 07:49:00 GMT

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

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

Wed, 30 Mar 2011 07:54:09 GMT

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?

Load script - loop through all tables in Access database

matt_crowther — Wed, 30 Mar 2011 09:32:57 GMT

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

Load script - loop through all tables in Access database

Tue, 19 Apr 2011 13:02:48 GMT

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.

Load script - loop through all tables in Access database

danielrozental — Tue, 19 Apr 2011 18:00:49 GMT

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

Something like:

Tables:

sqltables;

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

Tue, 25 Feb 2014 20:13:43 GMT

Matthew Good afternoon,

You have this example to post again?

Thank you

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

Clever_Anjos — Tue, 25 Feb 2014 20:19:05 GMT

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