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

[#1]

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?

[vishsaggi]

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.

[#3]

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

[vishsaggi]

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 ?

[tamilarasu]

Hi Ben,

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

OLEDB CONNECT32 TO ........

[#6]

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

[#7]

Changing it to OLEDB CONNECT32 causes the following error:

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

[beck_bakytbek]

Hi Ben,

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

beck

[#9]

aaccdb

[beck_bakytbek]

Ben,

check this: Problems connecting to ODBC

beck