Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kakmaurya
Contributor
Contributor

OLE DB autoload

I want to automate the loading of all the tables in OLE DB. Suppose there are three tables like Property_price, property_location, property_ owners, Is there a way to get all names of tables in database(databasetables) or is there any method to perform this operation?

for each tablename in 'databasetables':

LOAD *;

SQL SELECT *

FROM tablename;

Labels (1)
5 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Try using this command:

SQLTABLES;

and see what information you are getting in response. 

I'm explaining this technique in detail in my book QlikView Your Business It was written mostly for QlikView, however it is teaching many advanced development techniques that are relevant for both QlikView and Qlik Sense.

Cheers,

Oleg Troyansky

 

kakmaurya
Contributor
Contributor
Author

@Oleg_Troyansky  Thanks for helping me out. I used this code and this is the message i am getting.

for each tablename in SQLTABLES
LOAD *;
SQL SELECT *
FROM tablename;

 

Message : Connector reply error: ErrorSource: Microsoft JET Database Engine, ErrorMsg: The Microsoft Jet database engine cannot find the input table or query 'tablename'. Make sure it exists and that its name is spelled correctly.

 

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

I don't think you can use SQLTABLES this way... It's a command that loads a table with the information about your DB tables. Then you can process the results one by one and load the tables one by one.

If there was a way to use SQLTABLES the way you are trying to use it, then you'd need to enclose the variable "tablename" in a dollar-sign expansion: $(tablename). But again, I doubt if it could work this way.

kakmaurya
Contributor
Contributor
Author

I got the solution using this script. 

TableNames:
SQLTables;

For i = 0 to NoOfRows('TableNames')-1
Let TABLE = Peek('TABLE_NAME',$(i),'TableNames');
[$(TABLE)]:
LOAD*;
SQL SELECT *
FROM $(TABLE);
Next

But, the error i am getting is , the script is reading the MSAccessObjects like 'MsysACEs' etc as tablenames but unable to load them and the app is crashing. Can Anyone help how they can be removed from loading?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Check the field TABLE_TYPE . These system tables are coded differently from
the regular tables

Cheers,
Oleg