Query output is incorrect in ucanaccess

Question

I have an accdb file. I am using the ucanaccess api to connect to it from my Java project and process the data. (I do not have MS Access, I use ucanaccess as a referenced library in my Java Project).

However, I am facing a strange issue, when I import the accdb file in SQL server, and run the query SELECT B.grd , b.[credit hours] , b.term from [1i-stem dcccd student courses] B where [student id] = '2169095'; I get the following output:

But when I run the query in ucanaccess, I get the following output:

(There are two extra records in the ucanaccess command line output with grd as null).

I get the following messages in the ucanaccess console when I input the .accdb file path:

    Please, enter the full path to the access file (.mdb or accdb): C:\\Users\\Tejas\\Desktop\\sample.accdb
log4j:WARN No appenders could be found for logger (com.healthmarketscience.jackcess.impl.DatabaseImpl).
log4j:WARN Please initialize the log4j system properly.
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\GPA - Standing.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\GPA - Standing.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:Error in the metadata of the table 12i-Fac Info: table's row count in the metadata is 1642 but 1652 records have been found and loaded by UCanAccess. All will work fine, but it's better to repair your database.
WARNING:Error in the metadata of the table 3i-STEM STUDENTS LOAD BY TERM: table's row count in the metadata is 8716 but 4358 records have been found and loaded by UCanAccess. All will work fine, but it's better to repair your database.
Error occured at the first loading attempt of 12i-Faculty Latetst Information
Converted view was :CREATE VIEW "Z_12I-FACULTY LATETST INFORMATION" AS SELECT "STUDENT COURSE"."FAC ID", Last("STUDENT COURSE"."FACULTY LAST NAME") AS "LASTOFFACULTY LAST NAME", Last("STUDENT COURSE"."FACULTY FIRST NAME") AS "LASTOFFACULTY FIRST NAME"
FROM "STUDENT COURSE"
GROUP BY "STUDENT COURSE"."FAC ID"
Error message was :user lacks privilege or object not found: STUDENT COURSE
Loaded Tables:
10i-STEM STUDENT LATEST MAJOR INFORMATION, 12i-Fac Info, 13-F1 students by Term, 14i- Faculty Per Term, 15i- STEM COURSE MASTER, 15i- STEM COURSE MASTER by TERM, 1i-STEM DCCCD STUDENT COURSES, 2i - STEM latest Student Information, 3i-STEM STUDENTS LOAD BY TERM, 4i-STEM STUDENT Latest GPA, 5i-STEM MVC STUDENT COURSE 2014-15AY, 6-Financial AID By Sum of Term Amt, 7-STEM - ACAD YR MASTER, 7-STEM MVC Acad-YR TERM, 8i-STEM STUDENT COURSE STATUS BY TERM, 9-STEM TERM MASTER, Financial AID By Sum of Term Amt, STEM TERM VALUES
Loaded Queries:
2014-15AY STEM STUDENT UNIQ ID AS OF 6-4-15, 2i-STEM UNIQUE STUDENT LATEST TERM
Loaded Indexes:
Primary Key  on 1i-STEM DCCCD STUDENT COURSES Columns: (ID) 
, Primary Key  on 5i-STEM MVC STUDENT COURSE 2014-15AY Columns: (ID) 
, Primary Key  on STEM TERM VALUES Columns: (Term Code) 
, Index on 12i-Fac Info Columns: (FAC ID) 
, Index on 7-STEM - ACAD YR MASTER Columns: (CODE) 
, Index on STEM TERM VALUES Columns: (Term Code) 

Cannot load view MVC Acad_YR - TERM : user lacks privilege or object not found: STUDENT COURSE
Cannot load view 12i-Faculty Latetst Information : user lacks privilege or object not found: STUDENT COURSE
Cannot load view 4i-STEM STUDENTS Latest GPA - 3 : user lacks privilege or object not found: GPA - TERM AND CUMM
Cannot load view Update the Term Master : user lacks privilege or object not found: STUDENT COURSE
Cannot resolve table 5- STEM MVC Course
Cannot resolve table Course
Cannot resolve table First Time in College
Cannot resolve table GPA - Term and Cumm
Cannot resolve table Student
Cannot resolve table Student Course
Cannot resolve table Student Financial Aid
Cannot resolve table Student Load
Cannot resolve table Student Type
UCanAccess>
Copyright (c) 2012 Marco Amadei
UCanAccess version 3.0.1
You are connected!! 
Type quit to exit 

Commands end with ; 

use:   
   export <pathToCsv>;
for exporting into a .csv file the result set from the last executed query

UCanAccess>SELECT B.grd , b.[credit hours] , b.term from  [1i-stem dcccd student courses] B where [student id] = '2169095';
------------------------------
| GRD | Credit Hours | Term | 
------------------------------

| W | 4.0 | 2009FA | 

| F | 3.0 | 2009FA | 

| A | 3.0 | 2009FA | 

| F | 3.0 | 2009FA | 

| F | 3.0 | 2010SP | 

| F | 3.0 | 2010SP | 

| F | 3.0 | 2010SP | 

| C | 4.0 | 2010SP | 

| F | 3.0 | 2010SP | 

| D | 3.0 | 2012S2 | 

| C | 3.0 | 2012S2 | 

| A | 4.0 | 2014FA | 

| A | 4.0 | 2015SP | 

| A | 4.0 | 2015SP | 

| null | 4.0 | 2015FA | 

| null | 4.0 | 2015FA | 


UCanAccess>

There are other such insconsitencies as well. Like in SQL server, for a particular query, one of the column output is 'A', but in ucanaccess it is 'null'.

How do I fix this?

You know, it's something impossible to answer with these informations. What's the result in ms access? What's "1i-stem dcccd student courses", a query or a table? If it's a query as I would expect, what's its definition? Which ucanaccess version are you using? – jamadei 3 mins ago — jamadei, Oct 12 '15 at 07:30
I do not have MS Access. I use ucanaccess in my java project as a referenced library which connects to the `.accdb` file. The second image is the o/p in ucanaccess command prompt. `1i-stem dcccd student courses` is a table. The version of ucanaccess is `3.0.1`. — mridula, Oct 12 '15 at 07:36
May you send along the output of the whole ucanaccess console? If possible, and only if there aren't private or personal data, it would be great to have database copy, through the ucanaccess forum or any other way. Is GRD a calculated field? What if you open the db directly with jackcess?(I can post the two needed lines of code if you don't know how) — jamadei, Oct 12 '15 at 07:54
I have edited the querstion to add the entire console output. This accdb file is one uploaded by our clients to our Java applicaton, we just read it by running queries and processing results. So I cannot tell if `GRD` is a calculated field - but my best guess is that it is. Yes please, let me know the jackcess code. — mridula, Oct 12 '15 at 08:02
Also, I am sorry I cannot upload the file to sourceforge, it has sensitive data. — mridula, Oct 12 '15 at 08:04
Ok, below the answer, please use the latest version, we put so much effort in this! — jamadei, Oct 12 '15 at 08:43

score 0 · Answer 1 · answered Oct 12 '15 at 08:38

Firstly you're using a old 2.0.9.5 version, so please, it would be great if you used the latest 3.0.1 and updated your question with the 3.0.1 output as first step. Second, the db is likely corrupted and it could be fixed using the MS Access Compact and Repair tool. However it may be very useful to execute the following code (jackcess) and let us know your findings(the program output).

package net.ucanaccess.test;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Iterator;

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Row;
import com.healthmarketscience.jackcess.Table;


public class Jackcess {
    public static void main(String[] d) throws IOException, SQLException{
        DatabaseBuilder dbd =new DatabaseBuilder(new File("Path to your file"));
        dbd.setAutoSync(false);
        Database db=dbd.open();
        Table t=db.getTable("1i-stem dcccd student courses");
        System.out.println(t);
          Iterator<Row> it=t.iterator();
          while(it.hasNext()){
              System.out.println(it.next());
          }
    }

}

i have updated the question with the log from ucanaccess version 3.0.1. Also, I ran the Jackcess code to print the rows from the table. They are too many so I can't post them here, but i can say that the data in the columns are the same as printed by ucanaccess console. — mridula, Oct 12 '15 at 09:29
I have asked my client to repair the accdb file and give it, lets see if that works. — mridula, Oct 12 '15 at 09:30

Query output is incorrect in ucanaccess

1 Answers1