S.A with Access 2003 not showing linked tables?

I've got an access front end with TONS of linked tables back to SQLServer. When I'm in a query editor in Access the popup from S.A doesn't show any linked tables, just local tables.

The reason I've picked S.A over some others was for the Access integration.

Can you help? :)

SQL Assistant reads table list from standard ODBC catalog returned by MS Access ODBC driver. You see only the tables reported by the driver. You can also see queries which are like views in other databases. So if you create queries for your linked tables, you can use them with SQL Assistant.

	SysOp wrote:
	SQL Assistant reads table list from standard ODBC catalog returned by MS Access ODBC driver. You see only the tables reported by the driver. You can also see queries which are like views in other databases. So if you create queries for your linked tables, you can use them with SQL Assistant.

Well I see the Access Object DB query used to have some sort of logic for this:

But if I uncomment that and change it to allow where type in (1,4,5,6) (4 are linked tables).. the popup box is empty except for history.

Can we touch more on this?

What was the purpose of the original msysobjects query shown above that is commented out now? If it was to get tables and views how can I use this over the catalog API?

I spend a LOT of time in access developing our software. To have such a great tool like SQL Assistant at my fingertips but have it ignore the bulk of my database objects is just a real travesty.

	Code:
	SELECT mo.Name, IIf(type = 5, 'VI', 'TA') AS Expr1 FROM MSysObjects AS mo WHERE mo.type in (1,4,6) and Left(name,4) <> "MSys";

This query works well for the objects in a query designer.. using type = 5 in the where criteria starts showing what I believe to be stored queries for reports/bound forms... .. most of them start with a ~ in front. I have no objects named this way.

Now if I could get this to work in SQL Assistant rather than relying on the ODBC driver.

I'm not sure if it will work, but as a first step, you need to make your query return the same set and type of volume that you see in Object queries for other types of database systems.

	SysOp wrote:
	I'm not sure if it will work, but as a first step, you need to make your query return the same set and type of volume that you see in Object queries for other types of database systems.

Can you elaborate on this? Most of the other ones return name, type, objectid, creation date and modification date... are you saying that I need those as well for the popup to acknowledge the ms access data?

	Code:
	SELECT msysobjects.Name , IIf(TYPE = 5, 'VI', 'TA') AS expr1 , msysobjects.Id , msysobjects.DateCreate , msysobjects.DateUpdate FROM msysobjects WHERE ( ((msysobjects.Type) IN (1, 4, 5, 6)) AND ((LEFT([name], 4)) <> "Msys") ) ORDER BY msysobjects.Name , msysobjects.Type;

This still yields an empty popup box.

I'm simply unsure if it can take a SQL query in that place. It doesn't seem to be complaining, but is it really trying executing that query? That I don't know.
I will need to check with the team.

	SysOp wrote:
	I'm simply unsure if it can take a SQL query in that place. It doesn't seem to be complaining, but is it really trying executing that query? That I don't know. I will need to check with the team.

Thank you. It seems like they had this in mind to begin with because of the commented code block but then decided to use the API instead?

I took your last query and completely replaced the content of Objects (Access) query, restarted Access, reopened the same mdb file and it worked!!!

	SysOp wrote:
	I took your last query and completely replaced the content of Objects (Access) query, restarted Access, reopened the same mdb file and it worked!!!

Really? I can't seem to reproduce that. Mine always comes up empty.

Weird. I just checked options again and the query text reverted to the original "#USE CATALOG API;"

	SysOp wrote:
	Weird. I just checked options again and the query text reverted to the original "#USE CATALOG API;"

LOL. Well that makes me feel better... ;)

Any hope for this one? I abandoned using SQL Assistant on Access due to the above.

	Quote:
	Any hope for this one? I abandoned using SQL Assistant on Access due to the above.

To the best of my knowledge, we use Access' ODBC driver functions to retrieve database catalog information. That's the meaning of "#USE CATALOG API;" And that's because the database catalog is not exposed in a way comparable to other database systems and cannot be queried like other database tables. If you don't see the linked tables, I believe it's because the ODBC driver doesn't report them. If my understanding of the internal working is correct, I'm afraid there is nothing we can do to retrieve linked tables as part of the internal .