3 Probably basic questions - A03 front end SQL Express 2005 back end

[Lightwave]

Access 2003 linked to SQL Express 2005 via a DSN Less connection.

Bear with me just starting to experiment with this - seems to be going well so far.

I have created a "View" in SQL Express and using some DSN Less code got it automatically being imported into Access 03 Front end.

Questions

Firstly -Is a View the SQL equivalent of a Query? (It seems to be from what I've seen)

Secondly -The code that I have imported in seems to have brougth it in so the View looks like a linked table - I was thinking it might be brought into the query section of the front end as some kind of link. Is this correct or would it be more normal to bring it into the query section? I'm thinking this as I assumed there would be a query link somewhat like the links in the table section and I used the DSN less code to import the "View" subsituting the name of the table with the name of the view so it might be something in the code that determines which section it goes into.

Thirdly - I note that I don't seem to be able to edit or add records to the view when getting to the information through the Access 03 front end but I can make a query on the DSN Less linked table and add and edit the same information that way. I also note that I can add and edit the same view if I go into the SQL Server back end and open the view directly. Is this correct?

 

[LPurvis]

Ad astra... Scientia? ;-)

1 Is a View the SQL equivalent of a Query?
Hmmm... Ish. Queries in a Jet/ACE database encompass both less and more than the functionality of any one server object type.... (What does that gibberish mean?)
A view is analogous to a query in Access which includes no parameters and performs no action upon data (other than to select it).

2 View looks like a linked table
That's exactly right.
First of all - you've not imported it - otherwise it would look like (and be) a local table, holding a copy of that View's returned data. But you've linked - as you should.
There's no concept of a linked query (it makes no sense) but a View is a server object. It can be thought of as a virtual table (Views, in the technical standard) should not be sorted - or expected to be sorted - in any particular manner, just like a table.
That a View can be interpreted in an Access application as a linked table is actually a really powerful piece of functionailty. Joining two or more tables is a common occurance and can still be relatively efficient joining several linked server tables in an Access query. Note relatively. There is overhead.
tblClients INNER JOIN tblOrders ON...
isn't parsed into that exact same request on the server. It's broken up and fetched iteratively. Creating a view to perform big, important and commonly used joins (and especially aggregates) ensures that data joining and aggregation occurs on the server and gives you better client performance as a result.

3 I note that I don't seem to be able to edit or add records to the view
Presumably when you say: "but I can make a query on the DSN Less linked table and add and edit the same information that way" then your view is based upon a single table on the server? And you can't edit the view - but you can edit the table upon which it's based?
When you link to a view via the Access UI, you'll be prompted to select the field(s) to uniquely identify a row (i.e. to create a local primary key on the linked table).
This allows edits to that view (when the view is updatable on the server of course).
Have a look here for a code based alternative to create linked views while specifying the fields in question.
http://www.utteraccess.com/forum/Prompt-Index-field-SQL-t1809844.html

Cheers.

 

[Lightwave]

Thanks Leigh useful.

I hadn't realised that phrase had been used in Start Trek... learn something new everyday.

 

[LPurvis]

Yep, a variation on Nasa's Apollo mission motto "Ad Luna Scientia".

Everything make sense in the exposition?

 

[Lightwave]

Yes thanks - seems to be relatively straight forward. Just familiarising myself with how things are done.