• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 854
  • Last Modified:

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

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.
0
Fletcher Burdine
Asked:
Fletcher Burdine
  • 2
  • 2
4 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
mlmccCommented:
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
    NewDatabase
       Select field list FROM 4 tables appropriately joined

    OldDatabase
       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.

mlmcc
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

0
 
mlmccCommented:
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.

mlmcc
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Thank you guys. Mike one of these days. I will have to buy you dinner.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now