running remote queries

[dipperdave]

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.

[NeoPa]

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.

[dipperdave]

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 ?

[NeoPa]

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.

[dipperdave]

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 !

[NeoPa]

As I'm currently at work I can check this out for you.

[NeoPa]

The Views show in the list of Tables when connecting to the SQL Server via ODBC to attach.

[NeoPa]

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.