Author |
Message |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
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? :)
|
|
Tue May 28, 2013 10:16 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: 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.
|
|
Wed Jun 12, 2013 10:38 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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?
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Weird. I just checked options again and the query text reverted to the original "#USE CATALOG API;"
|
|
Thu Jun 13, 2013 10:53 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: 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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
|