Load script - loop through all tables in Access database

[#1]

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

[#2]

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]

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

[#4]

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]

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

Something like:

Tables:

sqltables;

[#6]

Matthew Good afternoon,

You have this example to post again?

Thank you

[Clever_Anjos]

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