Using queries in sql 2005 in an Access adp database
I have tha attached query that works fine in SQL, however i want to be able to use this query in an ADP file, however im not sure how to do it. I think i would have to use a stored procedure, however im not sure how to use these that well.
Could any one give me some help in getting this to work please.
Could any one give me some help in getting this to work please.
SELECT tblcustomerdetails.Auto, tblcustomerdetails.[Date Recorded], tblcustomerdetails.NAME, tblcustomerdetails.[Trading As Name], tblcustomerdetails.SalesPerson, tblcustomerdetails.[Internal Status], tblterminaldetails.TID, tblterminaldetails.MID, tblmerchantinfo.[Previous Acq]
FROM ((tblcustomerdetails LEFT JOIN tblterminaldetails ON tblcustomerdetails.Auto = tblterminaldetails.Auto) LEFT JOIN tblratesandfees ON tblcustomerdetails.Auto = tblratesandfees.Auto) LEFT JOIN tblmerchantinfo ON tblcustomerdetails.Auto = tblmerchantinfo.Auto;
ASKER
ive created the view, however when i change data on one of the tables that populate the vie, will the view automatically be updated? or would i have to run something to get it to do that?
view is actually a saved and compiled select statement so it will always show actual data from the related tables.
ASKER
sorry just one more question. I think the name makes it obvious but ill ask anyway.
As it is only a view i take it it is read only? If you have a form bound to a view you cant change the data on it?
As it is only a view i take it it is read only? If you have a form bound to a view you cant change the data on it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The answer to your question depends on what you actually want to do with your query. In Access, the query is usually feeding the data to a form or report. If so, then all you need to do is specify your query as record source for the form or report, and it will work. There's no need to create a view or stored procedure, unless your query is very, very large (about 20 times larger than yours) and Access fails to interpret it - then you create the view to help Access.
Even if you need to only work with the query results directly, it's still good idea to create a form (wizard will create it for you in no time), rather than create view and open it from Access. Access will work much faster with the form than with the view, especially when the database has many objects.
Finally, the data being editable or read-only does not depend on whether it's a view or direct query; it mainly depends on all participating tables having primary keys, and those primary keys included in the query. Further, if it's a form, you can specify the unique table you are going to edit - if you open the view directly, Access won't know, so the same multi-table query may be read-only when opened as a view, and editable in the form.
Even if you need to only work with the query results directly, it's still good idea to create a form (wizard will create it for you in no time), rather than create view and open it from Access. Access will work much faster with the form than with the view, especially when the database has many objects.
Finally, the data being editable or read-only does not depend on whether it's a view or direct query; it mainly depends on all participating tables having primary keys, and those primary keys included in the query. Further, if it's a form, you can specify the unique table you are going to edit - if you open the view directly, Access won't know, so the same multi-table query may be read-only when opened as a view, and editable in the form.
create view v_tblcustomerdetails
as
SELECT tblcustomerdetails.Auto, tblcustomerdetails.[Date Recorded], tblcustomerdetails.NAME, tblcustomerdetails.[Tradin
FROM tblcustomerdetails LEFT JOIN tblterminaldetails ON tblcustomerdetails.Auto = tblterminaldetails