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? :)

Tue May 28, 2013 10:16 am

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6754

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.

Tue May 28, 2013 1:52 pm

Mindflux

Joined: 25 May 2013Posts: 645Country: United States

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.

Tue May 28, 2013 2:07 pm

Mindflux

Joined: 25 May 2013Posts: 645Country: United States

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.

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.

Thu Jun 13, 2013 11:45 am

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6754

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.

Thu Jun 13, 2013 1:10 pm

Mindflux

Joined: 25 May 2013Posts: 645Country: United States

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?

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.

Thu Jun 13, 2013 3:06 pm

Mindflux

Joined: 25 May 2013Posts: 645Country: United States

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?

Thu Jun 13, 2013 4:35 pm

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6754

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

Thu Jun 13, 2013 8:27 pm

Mindflux

Joined: 25 May 2013Posts: 645Country: United States

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.

Thu Jun 13, 2013 8:57 pm

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6754

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

Thu Jun 13, 2013 10:53 pm

Mindflux

Joined: 25 May 2013Posts: 645Country: United States

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... ;)

Fri Jun 14, 2013 10:34 am

Mindflux

Joined: 25 May 2013Posts: 645Country: United States

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

Mon Nov 07, 2016 11:42 pm

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6754

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 .