Access Criteria Function Compatible with SQL

[CharlieT302]

Hi Folks,

I have two versions of the same database. Both have been split between a front end (desktop) file and a back end (data tables on the server). Their back end records are synchronized each night, so they are identical.

Database 1:
The backend has been converted to an SQL server.

Database 2:
The data source (back end) is still in an Access format.

Both have a query that uses the following function:

In (SELECT [CLIENT_NUMBER] FROM [Qry_Report_ODA_Waiting_List_Names_ON] As Tmp GROUP BY [CLIENT_NUMBER] HAVING Count(*)>1 )


The Access format produces records. The one attached to the SQL server hangs up and never produces anything. THe queries are identical in every way.

Questions:
*What is this function doing?
*Is the above function incompatible with SQL?
*If so, is there another way of writing it to be compatible with SQL?


THanks

 

[AlexCuse]

How are you querying your SQL backend? If you have it set up as a linked table the access syntax should work.

I see you are selecting from a query though, so the real question is what is the SQL for Qry_Report_ODA_Waiting_List_Names_On?


A wise man once said
"The only thing normal about database guys is their tables".

 

[CharlieT302]

Qry_Report_ODA_Waiting_List_Names_On

is another query that uses two linked tables as the data source. The linked tables are in the SQL backend.

As I said, only the version whose data tables are in SQL is freezing on the report. The report simply will not come up.

The report on the non-SQL backend brings up the report in a few seconds.

 

[AlexCuse]

Have you tried refreshing your linked tables?

and does Qry_Report_ODA_Waiting_List_Names_On open properly on the SQL backend version?

A wise man once said
"The only thing normal about database guys is their tables".

 

[CharlieT302]

The links are refreshed each night as part of a nightly process. In fact, all tables and the queries that reference them can pull data without problem.

It is only when I include the criteria I described earlier that the query (and attached report) hangs up.

 

[AlexCuse]

I am at a loss here. Sorry for all the background ifo I have been asking for, it seems that you have covered your bases pretty well.

One thing I would try is removing "As Tmp " from your criteria, because at least from where I'm sitting it doesn't seem to be necessary.

Another thing you may try is changing your criteria query so it can use a where clause rather than Having?
something like this:

In (select a.[CLIENT_NUMBER] from (SELECT [CLIENT_NUMBER], count([CLIENT_NUMBER]) as CCN FROM [Qry_Report_ODA_Waiting_List_Names_ON] GROUP BY [CLIENT_NUMBER]) a where a.CCN >1)

Let me know if either of these modifications changes anything.

Alex

A wise man once said
"The only thing normal about database guys is their tables".