ODBC Errors when using a Nested Access Query

[dcrosier]

I am wondering if I am hitting a security issue to the database OR if there is a problem with my ODBC drivers.

I can get to the tables and use them to create the report. I get the following error when I attempt to use one of the queries that I have created in the Access 2000 database:

ODBC Error: [Microsoft][ODBC Microsoft Access Driver] " is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

When I respond - "OK" then I get another message box that pops up to tell me that Error detected by database DLL

Anyone have any suggestions?

Thanks.
Dawn

 

[kenhamady]

What is the name of the query?
Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[dcrosier]

qryProductionWorkInfo is the name of the query that I am adding to the following tables:

Job
JobOperation
Delivery
& I can't remember the other ones off the top of my head.

I have also tried to use a query based on those tables. It was named qryJobInfo - so I don't think the length of the name should be part of the problem.

Thanks.
Dawn

 

[kenhamady]

Check the FAQ on MS Access 2000, you may need the update described there.

Can you report on the tables without the query? Are you trying to link a Query to tables? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[dcrosier]

I AM trying to link the query into the tables. I can generate the report until I add one of the fields from the query to the report. (I don't have any problems getting out of the Visual Linking Expert. Just when I use one of the fields from the Query in the report!)

I'll go check out the FAQ on Microsoft's Web Site, but if you have any other thoughts, I would greatly appreciate your input.

Thanks.
Dawn

 

[kenhamady]

Crystal ignores tables, unless its fields are needed for the report (or if the table is used between other tables that are used).

So, it is ignoring the query until you use a field from it.

Can you report on the query all by itself?
Can you add the other tables to the query in MS Access, and report off of the new query?

I seem to remember some limits on linking tables to queries, but I don't remember the specifics. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[dcrosier]

I can use the query in Access just fine. (I have to sign onto the Database.)

I am unable to use the Query in Crystal 8.5 at all. (Either with other tables or all by itself.)

I would have created the report ENTIRELY in Access except they want me to group on job # and determin whether the job is late or not based on information that is stored for each work center. There are multiple work center details for each job. AND to further complicate matters, I am to show the job if the work center is late BUT if the next work center gets the job back onTime, then the job should fall off the report. - And back on the report if the next work center falls behind again.

The only way I could figure on doing such a complicated filter is through Crystal. Yet, I am not even there yet since I can't seem to get the fields on the report. If you have ANY other suggestions, then I would greatly appreciate your opinion.

I have tested with utilizing the Xtreme Sample Database through ODBC connectivity and don't seem to have any problems selecting a table and a query to generate a report. But I don't know how to get into Xtreme to create a query of my own. Do you know the passwords or alternative methods of getting into that Sample Database so I could determin if the problem resides inside Crystal?

I appreciate all your thoughts.

Dawn

 

[kenhamady]

If the query can't be used by CR at all, then that query may be the problem. I report on MS Access 2000 queries with no trouble. But not every query can be read by CR.

What do you mean by a "Nested" query. What is this query doing?

Does it have subqueries? That will probably make it ineligible for CR. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[IdoMillet]

I have joined queries (based on other tables and even based on other queries) to tables in Access 2000 as data sources for Crystal 8 reports with no problems.

Try to replace any double quotes (surrounding constants) in your query's SQL with single quotes.

Cheers,
- Ido

ixm7@psu.edu

 

[gjgcpa]

Has this issue been resolved yet? I am getting the same error messages, but it doesn't matter if I have "nested queries" or not. The errors occur if I use the ODBC data source for the crystal reports. If I point directly at the database, and query (not using ODBC), then everything works fine. With ODBC...crash and burn.

Any solutions?

 

[kenhamady]

You can use the tables through ODBC but not the Query?
And you have tried the Query through ODBC all by itself?
Have you tried testing that ODBC connection with another program? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/