Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Version 10: Not showing all the queries in an Access DB? 1

Status
Not open for further replies.

TheElephantMan

Programmer
Jun 18, 2004
42
US
Good morning, all.

I am using Crystal 10 and Access 2002, and when I attempt to open up a local database using the "Access/Excel (DAO)" option, I encounter an odd problem. When I expand the "Views", it does not display all of the queries that I have in my database.

Is this a glitch in Crystal, or Access? Or, perhaps there are some kinds of queries/views that Crystal is not able to open?

Thanks in advance.

TEM
 
Hi,

Does your query have parameters?
Crystal Reports sees parametized queries as Stored Procedures.
 
Hi,
With Access 2002, I believe you should be connecting via the OLEDB connection to Jet 4.0. DAO was the standard for Access 97 and earlier versions, and was optional with Access 2000.
HTH, Randy
 
I have a report I built off an access db with linked tables from an Oracle 9i db.
The report works in CR (10), but I get this error when trying to run the report in CE:

CrystalReportViewer
Query Engine Error. File 9e83876e7286a4.rpt.
Unable to retrieve Object.
Query Engine Error. File 9e83876e7286a4.rpt.

I have set up the report the same way I set up other access reports. I am wondering if it is the linked tables. Any ideas?
Thanks.







 
When you set up the ODBC connection within the Access database, did you select save password?

If not, the report is NOT going to be able to open the underlying table.

Obviously you also need to set up the ODBC on the CE server too (an often overlooked obvious error ;) ).

-k
 
I did select save password - a tip I found here on tek-tips :)

I have an ODBC connection on the CE server named & configured just as I have named the one that I use on my workstation. I have tested this connection and it works to get to the oracle db.

In the report I am using a UNC path to the access mdb file.
I have changed the user for the pageserver and job servers to a user which has rights to access the mdb file on another server.

I have gone into process>database in the Crys. Mgmt. Console and added the Admin userid, unchecked 'prompt', which i figured would be the correct set up but it does not work. I tried leaving prompt checked and taking out the user id. No luck.
I have tried this both on demand and scheduled and get the same error.
 
If you have Crystal Reports on the CE server, open it and run it there (I always install CR on the CE server for this reason), it gives a better description of the error.

If this isn't possible, check the Event Log.

-k
 
I checked out the event viewer, no problems or even informational items for crystal and nothing else out of the ordinary.
I installed CR on my test server and it does provide a more informative error when I try to run the report from the CE Server, but I still am not sure what to do about it:

Failed to open rowset
Query Engine Error - DAO Error Code 0xc4f
Source DAO.Database
Description: ODBC -- Connection to XISProd failed

I guess this indicates it's not a problem connecting to the access db, but rather the linked tables...?
Thank you for your quick responses; any further thoughts would be much appreciated.
 
Aha! "I have an ODBC connection on the CE server named & configured just as I have named the one that I use on my workstation. I have tested this connection and it works to get to the oracle db. "
Well, apparently I lied.
I looked and looked and it only took me 3 days to notice the "slight" difference in the configuration between these two data source set names.
We are using both 9i and 8i Oracle db's here and I was using the 8i syntax in my tnsnames and odbc connection on my workstation, then when I set up the connection on the CE server I went with 9i syntax (actually used the little config tool) and that is why I could not connect to the linked tables.

Ok, as always, when I think I have looked at everything I possibly can think of, I still need to look some more. Ug.

Part of what helped me realize this was going into the access database and clicking on one of the linked tables. A control tip came up and gave me the connection information, something like:
ODBC; DSN=MYPRODDB,UID=MYUID,DBQ=service name;blah...

the dbq part is apparently the service name from tnsnames, which, again I had configured different on each machine.

Synapsevampire, thank you for laying out quite clearly for me that there were only a very few possible problem areas here. Much appreciated.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top