Listing objects (tables and queries) within a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JimP - 24 Apr 2008 15:15 GMT

Is there a way to list the objects in a query via code?

Reply to this Message

Alex Dybenko - 24 Apr 2008 15:43 GMT

Like this:

SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Type In (1,5,4)

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

> Is there a way to list the objects in a query via code?

Reply to this Message

JimP - 24 Apr 2008 21:21 GMT

Thanks for the reply, Alex.

I think what I am trying to do is different and probably can't be done.

I have a number of queries (out of a 1,000) that have a problem with a join
that involves (2) tables. I'm trying to identify which queries need to be
fixed. However, I don't think listing the objects within a query can be done
except for some type of text search on the qdf.SQL property.

If you can help, the larger issue here is that one of the two tables is a
local MS-Access table and the other is a linked SQL Server db table. The
local table always has lower case codes and the SQL Server table can have a
mixture of codes. Case sensitiviy is enforced on the SQL Server table.
Sometimes the linked records are retrieved, sometimes not and I can't begin
to explain why.

I need to retrieve the matched records somehow.

My aplogies for the double-post here
Jim

> Like this:
>
> SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Type In (1,5,4)
>
>> Is there a way to list the objects in a query via code?

Reply to this Message

Alex Dybenko - 25 Apr 2008 09:47 GMT

You can try to import sql server table to access, then make join, whatever,
and then update sql server table based on primary key (hope it has int type)

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

> Thanks for the reply, Alex.
>
[quoted text clipped - 23 lines]
>>
>>> Is there a way to list the objects in a query via code?

Reply to this Message

JimP - 25 Apr 2008 14:52 GMT

Thanks,

I considered importing the table - this is for report writing onlu so I
don't need to worry about updating any tables.

> You can try to import sql server table to access, then make join,
> whatever, and then update sql server table based on primary key (hope it
[quoted text clipped - 27 lines]
>>>
>>>> Is there a way to list the objects in a query via code?

Reply to this Message

John Spencer - 24 Apr 2008 18:29 GMT

Do you mean you want to list all the fields in the select clause and all the
tables in the from clause and any additional fields in the where, order by,
group by and having clauses?

No. You can get the list of fields in a Select query. But getting a list of
fields in most other types of queries (update, append, crosstab, etc.) is not
easily done - if it can be done at all.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Is there a way to list the objects in a query via code?

Reply to this Message

JimP - 24 Apr 2008 21:12 GMT

Thanks for the reply.

I have a number of queries (out of a 1,000) that have a problem with a join
that involves (2) tables. I'm trying to identify which queries need to be
fixed. However, I don't think listing the objects within a query can be done
except for some type of text search on the qdf.SQL property.

If you can help, the larger issue here is that one of the two tables is a
local MS-Access table and the other is a linked SQL Server db table. The
local table always has lower case codes and the SQL Server table can have a
mixture of codes. Case sensitiviy is enforced on the SQL Server table.
Sometimes the linked records are retrieved, sometimes not and I can't begin
to explain why.

I need to retrieve the matched records somehow.

Jim

> Do you mean you want to list all the fields in the select clause and all
> the tables in the from clause and any additional fields in the where,
[quoted text clipped - 10 lines]
>
>> Is there a way to list the objects in a query via code?

Reply to this Message