One report - connect to SQL OR MDB w/same qry and layout 3

[cmoen]

I would like to design one report (*.rpt) file and pull data from either a SQL server OR Access database depending upon the user's choice of connection (online vs offline). The table names and structure is exactly the same.

I am using a dsnless connection and have tried the SetLogonInfo and Location commands but it does not allow me to change from a SQL OLE DB provider to a Jet OLE DB provider.

The report has been designed using one connection and needs to be modified to use either the SQL or Access dsnless connection - Please help!

So far, my only solution is to create 2 identical reports but change the database type at design time. Ughhh!

 

[delu0007]

Did you ever get a solution to this problem? I am having the same problem.

 

[courtoisf]

Maybe the best long-term solution would be to pass a recordset into the report. Then you could choose the source in your code according to automatic or manual settings by the user. It's a good thing to get into anyway, as there is more flexibility and power in this approach then trying to work in "argh" Crystal.

 

[delu0007]

I read somewhere that Active Data cannot be used in a report that accesses multiple tables. All of my reports access at least 2 tables. Plus, over 200 reports have already been defined so I would prefer to use the same reports and just manipulate them in VB code.

Is there still a way for me to use the Recordset method?

 

[balves]

Of course, the Active Data Driver can access multiple tables in one report.

However, if you have not created your Crystal Report with the Active Data Driver, you cannot switch to it in your VB app.

 

[courtoisf]

Here's something that worked, and it actually is a combination of approaches. I prepared a simple report with some formulas in it for headings and such, and then added some unbound fields for data. These end up listed in with the Formula Fields. Then I made sure I changed the Name property of each one to something easily identifiable, and also went to the formula list, right-clicked on them, and renamed them there. I used names without separators and such, as they cause problems.

Then in code, I set up an sql query, specifying the fields I wanted, as well as an alias that matched the unbound field names. So for instance, I had an unbound date field on the report and I named it ldate, and within the sql select statement I just added "as ldate" on as the alias for the date field I wanted.

Once I had opened the recordset, I set the report's command connection to it using:

myrepobj.Database.AddADOCommand myrst.ActiveConnection, myrst.ActiveCommand

Then I had Crystal automatically match the recordset fields to the report formulas(unbound) fields according to name using:

myrepobj.AutoSetUnboundFieldSource crBMTName

It worked just fine. Now I have multiple versions of the select statement working off a dialog box for user criteria. The nice thing is that you can join in any tables you want, extract sql function results, build expressions, etc. (anything sql can do), and it's all done in code. Crystal doesn't need to know anything but the completed recordset and where to slot the fields.

As mentioned, this is a combination of things, but it avoids the subscript error that setdatasource causes in these conditions, and the errors and unreliability of the individual setunboundfieldsource statements.

 

[kenhamady]

Delu0007,

I think you might have read that you can't CONVERT a report that has more than one table to using the Active Data Driver. You can CREATE a report with the ADD and it can use any number of tables, or simply a TTX file giving the fields used. But I have found that if you create a report using any other Driver, and you try to convert it so that it will accept a recordset, the conversion won't work if the report has multiple tables. I think it is because a recordset is considered one table.

Courtoisf,

Your approach seems the equivalent of the one that I have used. Namely creating field names in a TTX file, creating a report against that file, and then passing a recordset that has the same field names to the report in the application. Do you know if the unbound field approach has any advantages? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[courtoisf]

kenhamady,

Hi. I'll have to plead ignorance on the TTX side of things in that I believe I understand the approach well enough, but haven't tried it. So, I don't know about performance differences but in this case we can probably assume they wouldn't amount to much. Other than that, it does seem like the same twist on an old tale. A "six of one, half dozen of the other", sort of thing. I prefer the unbound approach right now for a few reasons that are probably based more on personal style than anything else. I try to avoid the use of any extra files if possible, knowing that long-term maintenance becomes more complicated when the numbers grow. Defining the fields as unbound allows me to do without the TTX file. Also, I considered spending the time to explore the unbound field technique and practicing it whenever possible to be a very valuable investment in my own saleability. (I was able to justify the time spent in some more "in-depth" study in VB/SQL/Crystal with concrete results.) With more and more systems to be responsible for, trying to focus time and energy gets harder every day. Of course, when a definitely better way is shown to me, I'm not going to pass it up.....

 

[kenhamady]

Courtoisf,

Thanks for the explantion. I have one last question. When you open the visual linking expert of this report. Are there any tables shown there, or is this window empty? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[courtoisf]

Nope. With no database tables referenced, the linker is not enabled.

 

[kenhamady]

OK, I was wrong, one more question. Are you using the "Report Creation API"? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[courtoisf]

Are you referring to the "experts", or "wizards" to some? If so, I don't use these as a rule. I do occasionally run one to check out the code approach on the other side, to analyze the technique for useful tidbits. I spent many years as a College Instructor, and voluntarily always took the "long, hard route" in order to provide a more comprehensive resource to my students. I guess by now it's ingrained. I consider Experts/Wizards a handy time-saver for basic functionality, if you haven't already got some modules of your own to slide into the fray. The reports I'm producing have some hefty data handling involved, so I usually build them "manually", piece by piece, testing as I go.

 

[kenhamady]

It is more than the wizard. The report creation API is a group of DLLs that allow you to change the structure of or even create a new report from within your code. These dlls go beyond the regular RDC, and they require royalty licensing from Crystal when you include them in your app.

The reason that I ask is that the following article on Crystal's web site seems to use pretty much the same approach as yours. It says that it is using calls from the royalty licensed Report Creation API, but I can't figure out what calls would be licensed calls.

http://support.crystaldecisions.com/kbase/c2006325.asp?

According to the 8.5 license help file, the dlls that are covered are:

CRAXDRT.DLL (depending on what calls you use)
CRAXDDRT.DLL
CRYSTALWIZARD.DLL
CRDESIGNERCTL.DLL

The calls for AddADOCommand and Set/AutoSet are specifically mentioned in the help file as NOT requiring a royalty license. I suppose this could have changed from 8 to 8.5 but that would surprise me.

I assume that you are using CRAXDRT, but are you also using CRAXDDRT or the other two in your project? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[courtoisf]

Of course, it would have been too much for me to see this before I spent the time to make one. Considering the amount of time I spent searching the Crystal site for information like this, with no results, it kind of ticks me off. Aaaannnyyway, the answer is no, I don't use the others, and I agree with you about the rest of it being too mundane for royalties to come into play. This could just be a case of someone at Crystal posting that header automatically when it doesn't really apply to that particular example. It's happened before, and will again. Proofreading is a dying art. I'm curious though, wouldn't their example give that subscript out of range error everyone has been tripping over?

 

[kenhamady]

I am trying to get to the bottom of that exact question. I haven't tried either option yet, but the article looks pretty much like your code's approach.

What is different in your approach that gets around the subscript error? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[courtoisf]

Setting the unbound fields by letting Crystal do it by name. I found that if you use SetDataSource (as the book and most of the help files say to do), and do the field setting specifically (as the book and this example say to do), you end up with errors. By using the AddAdoCommand to assign the recordset, but let Crystal assign the fields by name, it works flawlessly. It may operate differently in different version combinations between VB and Crystal, of course, but this method works with the latest Crystal and VB 6. At this point I'm assuming that there is an array-handling error in the SetUnboundFieldSource routine(s), that was left out of the AutoSetUnboundFieldSource routine(s).