10 Replies Latest reply: Aug 25, 2016 7:59 AM by Ben Fox RSS

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

    Ben Fox

      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
          Vishwarath Nagaraju

          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
              Ben Fox

              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
                  Vishwarath Nagaraju

                  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
                      Ben Fox

                      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';

                       

                       

                       

                       

                      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];

                       

                       

                      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
                  Tamil Nagaraj

                  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
                    beck bakytbek

                    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
                      Ben Fox

                      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.