Crystal Reports Points to an Microsoft Access Database Accdb reports fail when we split the DB from the VB code

Posted on 2011-05-02
Last Modified: 2012-05-11
We have several reports that were created to run off a Microsoft Access Database. They were all working fine up until we decided that we needed to separate the code from the database as users of the Access system would go into the application we designed and would inadvertently get a debug process started and before you know it they would have changed a line of code. So we broke the VB and DB up.
We noticed the following anomalies:

1. We immediately got an invalid Argument when we "Set Data Source Location" to the new database. So we took the old DB and mapped to the new DB file. This did not work until we changed one of the groups inside of the report. {OfficeUse.Code} All we did was go to Change Group and scroll through the list and pick the second duplicated field group {OfficeUse.Code}. That particular report has the group {OfficeUse.Code} that is duplicated once in Group Header 1 and Group Header 4.

2. Next once we got the data to connect. We noticed that the records were replicating exponentially. It did not make sense that we were getting duplicates when all the links were the same.

3. Finally we ran the report for ALL {OfficeUse.Code} 's and all records are returned. When we select one specific code for {OfficeUse.Code} which shows up in the records hundreds of times when ALL is selected,we get NO RECORDS RETURNED for that single code........this makes no sense to us...? One other thing here is that we also loose the logo for the report for this. It is almost as if every time we run for one specific {OfficeUse.Code} we loose connection with everything.

Has anyone ever experienced these anomalies before or know what causes them. We are going to have to update many reports and wondered if this will be a recurring theme.

We did try another report and set the data source location and it worked. So it may be the duplicated {OfficeUse.Code} group. Any knowledge might be helpful.
Question by:ruavol2
    LVL 84

    Accepted Solution

    Splitting the database should have no effect on your reports, assuming you're pointing at the correct file. I'd suggest you re-map the Access database from CR, and make SURE that you are pointing to the "backend" database (the one containing only the tables).

    Also, you can prevent users from mucking around in the code by deploying a .mde/.accde formatted file. This "compiles" the database, and does not allow users to go into design mode for Forms, REports and code modules.
    LVL 100

    Assisted Solution

    Posted for ruavol2

    I am working this project with ruavol2.  That was the reason for splitting the database out.  Users were (we believe accidentally, most aren't sopisticated enough to do it maliciously) making minor changes to forms or just resaving a form with a filter in place.

    We split the database part out then just used SET DATA SOURCE LOCATION to remap the report to the new database.  When we get the problem we have 2 symptons I have found.  We are using ODBC connections since this is Access 2007
    1.  WHen you look at the SQL it show
           Select field list FROM 4 tables appropriately joined

           Select ATable.Code FROM ATable

    It seems Crystal is then cross joining the 2 queries thus resulting in the issue.

    We also see ATable.Code listed twice in the field list in the formula editor and the group change field dropdown.
    It seems we have a phantom connection to the one table for that field.
    That field is used for record selection and grouping.  It is also the linking field from that table to one of the other tables.

    When I replace that field with the same field in the child table the problem goes away.  We cant get rid of the table because we use 8 other fields from it.

    We have moved other reports over to the new database without an issue.  It wuld seem that this table is the problem.

    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    Can you build a query in the Access database, and then use the Query as the source for that report? I seem to recall you cannot do this with CR, but could be wrong.

    LVL 100

    Assisted Solution

    Posted for ruavol2

    We could.  In many ways now that it is an accde file I would prefer to do it in Crystal o if the report requirements change we dont have to change the accde file.

    CR does have a COMMAND capability but again changing the source may cause issues.  Since we now have the db separate, this should be the last time we have to change the report data source.

    We have the issue resolved but just trying to find out how/why this happened so as to avoid it in the future.  We have many other reports to move over.  The first 2 had this issue.
    The 3rd did not but then it didn't use that table.


    Author Closing Comment

    Thank you guys. Mike one of these days. I will have to buy you dinner.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now