By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

running remote queries

P: 3
I have an Access database on a server which utilises data from an SQL server on the same machine (it's set up like this because I have some limited access skills but no sql skills and because the sql database is an erp system which I prefer to keep discrete).

I want to provide limited reporting and (non-updating) query capabilities to authorised users on the lan/wan. I'm hoping I can install access on each user's machine and link to selected reports and/or queries in the server's access database. I fear I will be able to link only to tables and that is a no-no because of the danger that the users will update the underlying sql database.

So - can I link to queries and reports ? If so, how ? If not, please suggest a simple alternative.

Many thanks.
Mar 21 '07 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,347
You're talking about linking to the SQL Server db or the Access db on the same machine?
With SQL Server you can link to queries (or the equivalent - Views), but on Access you can only link to tables.
If you can be clearer exactly what you want to do and what to restrict we can take this a little further.
Mar 23 '07 #2

P: 3
You're talking about linking to the SQL Server db or the Access db on the same machine?
With SQL Server you can link to queries (or the equivalent - Views), but on Access you can only link to tables.
If you can be clearer exactly what you want to do and what to restrict we can take this a little further.
I want to link to a view of data in the SQL database. So I can't use Access on the client machines and will have to use SQL ?
Mar 26 '07 #3

NeoPa
Expert Mod 15k+
P: 31,347
No, you should be fine.
Linking to a View is similar to linking to a SQL Srvr Table. Use ODBC Link to your SQL Srvr to get list of items to link to.
Mar 26 '07 #4

P: 3
Thanks for your help but sorry - I'm not getting this !

When I open my access database and "get external data" and then choose link tables the only information that is displayed is a Link Tables box with only one tab (Tables) with all the tables in the SQL database listed.

Am I seeing only tables because I haven't created any views in the SQL database yet ? I would have created an SQL view and found out rather than ask the question but I haven't yet learnt how to create an SQL view !
Mar 28 '07 #5

NeoPa
Expert Mod 15k+
P: 31,347
As I'm currently at work I can check this out for you.
Mar 28 '07 #6

NeoPa
Expert Mod 15k+
P: 31,347
The Views show in the list of Tables when connecting to the SQL Server via ODBC to attach.
Mar 28 '07 #7

NeoPa
Expert Mod 15k+
P: 31,347
A View in SQL Server is simply like a QueryDef (Saved Query) in Access. You can create one simply by selecting New in the Views of the database (In SQL Server Enterprise Manager) and typing in or designing your query. T-SQL code will be taken or, alternatively, use the design tools there of selecting tables and fields etc.
Mar 28 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.