topic Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database in QlikView

Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

Wed, 24 Aug 2016 20:31:38 GMT

I've got a nice dashboard built pulling lots of data from an access database. Everything is working fine. Today I added a new table with some staffing information in it. If I run the query in the access database, all is well. Data shows up with correct values, no errors, etc. When I add it to the load script, it gives me a "Error: ErrorSource: (null), ErrorMsg: (null)" message and won't load that table. No idea what the issue is. The statement is:

SQL SELECT ChgNo,
Company,
dateserial(year(DataDate), month(DataDate), 1) as Monthly_DataDate,
Employee,
FTEs,
Location,
OT,
PED,
Position,
PositionID,
RateTableID,
ResID,
Source
FROM `Master Staffing Database Format`;

Any ideas?

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

vishsaggi — Wed, 24 Aug 2016 20:51:59 GMT

Are you pointing to the right location where the access db is in the from statement.

ODBC CONNECT32 TO [MS Access Database;DBQ= <Yourfilepath>\xyz.accdb];

SQL SELECT ChgNo,
Company,
dateserial(year(DataDate), month(DataDate), 1) as Monthly_DataDate,
Employee,
FTEs,
Location,
OT,
PED,
Position,
PositionID,
RateTableID,
ResID,
Source
FROM

FROM <YourAccessDBPath>."Master Staffing Database Format"; // This is your table name from AccessDB.

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

Wed, 24 Aug 2016 21:18:24 GMT

Yes. I have several other load statements directly under that one that work fine, so I'm sure it's not the connect statement. (here it is for posterity)

OLEDB CONNECT TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=.\Dashboard.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False];

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

vishsaggi — Thu, 25 Aug 2016 00:08:31 GMT

Ok. in the SQL SELECT statement above did you give the path and the database name.

Is it possible to send me the whole script your are using in the edit script ?

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

tamilarasu — Thu, 25 Aug 2016 02:06:21 GMT

Hi Ben,

Are you using 32 bit OS.? If so, try again by changing the connect statement from Connect to Connect32.

OLEDB CONNECT32 TO ........

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

Thu, 25 Aug 2016 11:59:15 GMT

I can't figure out how to PM on this web site, so here's a reduced version of the script (that works if you remove the subject sql select statement (full script is >500 lines).

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SQL SELECT ChgNo,

Company,

dateserial(year(DataDate), month(DataDate), 1) as Monthly_DataDate,

Employee,

FTEs,

Location,

OT,

PED,

Position,

PositionID,

RateTableID,

ResID,

Source

FROM `Master Staffing Database Format`;

SQL SELECT DataDate, MonthEnd as MonthEndFlag

FROM DataDates

WHERE DataDate<>#1/1/2000#;

SQL SELECT `Activity Name`,

`Activity Status`,

`Actual Finish`,

`Actual Start`,

`Area (formerly Area/Department)`,

`BL Project Finish`,

`BL Project Start`,

Calendar,

Commodity,

Discipline,

`Early Finish`,

`Early Start`,

`Free Float`,

`Physical % Complete`,

`Primary Constraint`,

`Primary Constraint Date`,

`Proc Reference`,

`Punch List Item`,

QDP,

`Remaining Duration`,

`Resp PC - P6QC`,

`ROOM / ZONE`,

`Secondary Constraint`,

`Sub Phase`,

SUBP,

TEAM,

`Total Float`,

`Type-Act Code`,

WBS,

`WP - SOURCE`,

left(`WP - Source`,3) as P6_CA,

Department as DepartmentKey,

DataDate & Activity_ID as ActivityKey,

iif(Left([WBS],1)='E','EDT_Current','MP_Current') as Project,

iif(isnull(`Early Finish`),`Early Start`,`Early Finish`) as Milestone_Finish,

iif(WBS="MP_Current.07.01.4T.08.03.10.3821","Emergent Work",iif(left(WBS,28)="MP_Current.07.01.4T.08.03.88", "CLIN3 Punchlist", iif(left(WBS,28)="MP_Current.07.01.3H.04.04.88", "CLIN2 Punchlist", "Other"))) as Punchlist,

iif(isnull(`Early Finish`), dateserial(year(`Actual Finish`), month(`Actual Finish`), 1), dateserial(year(`Early Finish`), month(`Early Finish`), 1)) as Finish_Date_Month

FROM `P6_Activities`

WHERE DataDate<>#1/1/2000#;

SQL SELECT Initials as P6_CA_Manager_Key,

ManagerName as P6_CA_Manager

FROM `Source Managers`

WHERE Initials in (SELECT BldgMgr from `Source WBSData`);

SQL SELECT BldgMgr as P6_CA_Manager_Key,

left(ChgNo,3) as P6_CA

FROM `Source WBSData`;

SQL SELECT

iif(`Early Finish` is not null,`Early Finish`,iif(`Early Start` is not null,`Early Start`,iif(`Actual Finish` is not null,`Actual Finish`, `Actual Start`))) as EDT_Complete,

DataDate

FROM `P6_Activities`

WHERE DataDate<>#1/1/2000# and Activity_ID='PM0A008500';

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

Thu, 25 Aug 2016 12:00:13 GMT

Changing it to OLEDB CONNECT32 causes the following error:

ErrorSource: Microsoft OLE DB Service Components, ErrorMsg: Class not registered

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

beck_bakytbek — Thu, 25 Aug 2016 12:10:42 GMT

Hi Ben,

what kind of Format of Access db do you use? MDB or ACCDB?

beck

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

Thu, 25 Aug 2016 12:12:31 GMT

aaccdb

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

beck_bakytbek — Thu, 25 Aug 2016 12:20:49 GMT

Ben,

check this: Problems connecting to ODBC

beck

Re: Error: ErrorSource: (null), ErrorMsg: (null) on load from access database

Thu, 25 Aug 2016 12:30:03 GMT

Found the error. Apparently Position is a reserved word in either Qlikview or the OLEDB connection. Changing the field name (not aliasing it) allowed the data to load without issue.