[Last Call] Learn how to a build a cloud-first strategyRegister Now


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
Medium Priority
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
  • 2
  • 2
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 35508239
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 101

Assisted Solution

mlmcc earned 1000 total points
ID: 35508508
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 35509024
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 101

Assisted Solution

mlmcc earned 1000 total points
ID: 35509521
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

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

825 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