SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
S.A with Access 2003 not showing linked tables?

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
S.A with Access 2003 not showing linked tables?
Author Message
Mindflux



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

Post S.A with Access 2003 not showing linked tables? Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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 View user's profile Send private message
Mindflux



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

Post Reply with quote
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 View user's profile Send private message
Mindflux



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

Post Reply with quote
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.
Wed Jun 12, 2013 10:38 pm View user's profile Send private message
Mindflux



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

Post Reply with quote
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.
Thu Jun 13, 2013 11:45 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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 View user's profile Send private message
Mindflux



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

Post Reply with quote
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.
Thu Jun 13, 2013 1:24 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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 View user's profile Send private message
Mindflux



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

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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 View user's profile Send private message
Mindflux



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

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
Weird. I just checked options again and the query text reverted to the original "#USE CATALOG API;"
Thu Jun 13, 2013 10:53 pm View user's profile Send private message
Mindflux



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

Post Reply with quote
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 View user's profile Send private message
Mindflux



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

Post Reply with quote
Any hope for this one? I abandoned using SQL Assistant on Access due to the above.
Mon Nov 07, 2016 11:42 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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 .
Tue Nov 08, 2016 11:57 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.