how to get system views to not show in Access Link Table box?

Question

how to get system views to not show in Access Link Table box?

dan-16162

Ten Centuries

Points: 1275
More actions
March 8, 2006 at 12:42 pm

#68654

I'm looking to build a database in MS SQL Server 2005, and users need to be able to link to it with MS Access. From previous experience I am aware that when linking from Access to MS SQL Server 2000, most of the system tables and views do not show up in the Link Table window in Access. However, in experimenting with linking to a MS SQL Server 2005 database from Access, I discovered that *all* the system tables and views are showing up in the Link Table windown in Access (...and there are hundreds).
Does anyone know how I can get these system views to not show up in the Link Tables window in Access?
Any assistance you can provide is appreciated.
Thanks!
dan

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Charles Wannall Ten Centuries Points: 1003 More actions · Answer 1

Unless you have a really good reason for letting users do manual table-linking, you would do well to control it in one of several ways:

You link all the tables yourself, and expose the data through your interface.

You link all the tables yourself, but make a table of tables, a list that users can see and select from. You can even add another table to hold the user's ID and the table's ID, if it's possible to determine ahead of time which tables you want which users to see. Then you only show them the tables you want them to see.

If you can't link ahead of time, use the table of tables approach to display either all or user-appropriate tables, and then do the linking in code.

If any of these sounds usable I'll be glad to discuss them in more detail.

Gotta ask: Why would users themselves need to do the actual linking of a table?

Also, please include what version of Acces you're in and whether you're using and MDB (assumed here, otherwise "linking" has no meaning) or an ADP.

R. Brush SSCommitted Points: 1669 More actions · Answer 2

If you use an Access Project (.apd), all the user tables and views will available, but the system tables and views will not.

dan-16162 Ten Centuries Points: 1275 More actions · Answer 3

Thanks both for your responses!

Charles, you have a valid point and some good suggestions, however, I do have some reasons (I don't know if they're *good* or not ) for letting the users do manual table linking. This is part of a project to build a data warehouse, and this is an interim solution for power users. Phase 1 is to build the data warehouse (read-only), and this would allow power users in each department to pull data for their department, until the web UI is built in Phase 2 of the project. Security on the tables in SQL Server will be set so that users can only see tables that they should have access to. Training power users how to link to SQL tables from an Access database would be a more effective use of my time than developing, deploying, maintaining, and upgrading a custom interface app, while I'm trying to build the data warehouse.

Re: using an Access Project (.adp) - I have never used an Access Project before, so I did a little investigating upon reading your reply. This may be a possibility, but from my research, at this point I think an .mdb would likely be a better solution for us.

Our version of Access is 2003.

Thanks again for your responses! Any other thoughts on how to make the system views go away in the Access Link Tables box?

Thanks!

dan

tom menacher SSC Enthusiast Points: 192 More actions · Answer 4

If you are in Access and use the "File, Get External Data, Link Tables" menu, you will see all of the tables and views in your SQL database.

However, you only have to do this one time in your development process.

I usually copy my .mdb file and take it to my client's location, and then use the "Tools, Database Utilities, Linked Table Manager" menu. Only the tables already identified by the Access application appear in the dialog. You then click "Always prompt for new location" and select the tables you wish to link and, when prompted, point to the SQL database.

tmenacher

dan-16162 Ten Centuries Points: 1275 More actions · Answer 5

Thanks for your response!

However, what I'm really looking for is a way to get the system views & tables to not show up in the Link Tables box in Access. I'm realizing that I may have posted my question in the wrong forum, because I think the solution will likely have to do with permissions or a setting in SQL Server, as opposed to doing anything in Access. My apologies. If I remain unable to find a solution, perhaps I will re-post my question in one of the other forums.

Thanks to all who responded!

dan

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 6

Just a quick question.

Is the user connecting the database owner/sysadmin/...?

dan-16162 Ten Centuries Points: 1275 More actions · Answer 7

No. Users will be connecting with their AD logins (windows authentication/trusted connection).

dan

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 8

Go to Tools/Options and unclick "System Objects" and "Hidden Objects" on the View tab.

dan-16162 Ten Centuries Points: 1275 More actions · Answer 9

Metra, Thanks for your response! However, System Objects and Hidden Objects are already unchecked under Tools/Options in Access.

dan

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 10

Okay, you may have to do a custom link tables instead of using the built-in one. You would create a table listing the tables you want them to see, with maybe a boolean field called "selected", then create a form showing that list, allowing them to select the tables they need, and then prompt them for user name, password, server, whatever, and create the links by looping through the select list. If you put the "list of tables" on a network share and have it linked to your front end, you could edit it as necessary and they would automatically get the revised list.

Having fun yet?

Metra