Problem in running a query with linked table

[Manneke]

Hi everyone,

Here is my problem.
I run a query which works on some PC's but not all.
Doesn't matter which version of Access I use.
This query uses a MS_SQL linked table and is integrated in a Crystal report. This report is integrated in another application which uses a crystal report runtime.
In this application you can make a selection about a group of persons or select one individually and get the results in the report. The results fill some tables in an Access Database where the query is performed.
If the selection is made for a group or more than 4 persons the report runs fine, otherwise I get an Crystal Error Message 534.
I found that comes from Access and this not so special query ; result of subqueries.
If I run the query when less than 4 persons are selected
I get a login window where I have to fill the password to Access the data source of the linked table. Otherwise this windows doesn't appear. This windows is the result of the error message 534.

Is there some reasons ?
Any idea will be appreciated

I hope my explanations were not too confused.

Thanks in advance for your answers.

 

[cmmrfrds]

Embed the password when you link the table. There is an option to save password when linking the table.

 

[Manneke]

Hi cmmrfrds,

Thanks for your answer but it's not that, because the same Access database is used on all the PCs and it works without any problem on few PCs.
How can it be possible that this windows appears for a small selection and not for a big one ?

Thanks for any idea. ;-)

 

[cmmrfrds]

Can you explain your full configuration a little more.

Does the Access mdb reside on the network?
Is there a system DSN that that is used for the ODBC connection to the mdb on this server?
Does the Cyrstal report use the same system DSN to make an ODBC connection to the mdb?
Does this only happen in Crystal? How else have you tested other than Crystal?
Anything else that may help.

 

[Manneke]

Hi cmmrfrds,

The Access MDB can reside on the server or on the client machine.
It doesn't matter, just need to change the ODBC datasource on every client machine.
First I let it on the server and then put the MDB on each client but same result.
As I saw that Access was the problem, I took a look at the query to see if I could find a solution
(There is always a solution ;-) )
I decided to create a subquery where I put the linked table with the needed Access tables to get my result
and then added this subquery into the main query.
It solved the problem.
Now I don't understand why the old query could work on some machines but not all. Each machine uses the same ODBC datasource
It seems that nobody had this sort of problem.

If you or someone else have any idea to look at, it is welcome :^)

Have a nice day