Solved

Using queries in sql 2005 in an Access adp database

Posted on 2008-06-16
6
182 Views
Last Modified: 2013-12-05
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.
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;

Open in new window

0
Comment
Question by:CaptainGiblets
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:orcic
ID: 21792229
You can create a view and use it in Access same way as you would a table.

create view v_tblcustomerdetails
 as
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
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 21792344
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?
0
 
LVL 7

Expert Comment

by:orcic
ID: 21792387
view is actually a saved and compiled select statement so it will always show actual data from the related tables.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 6

Author Comment

by:CaptainGiblets
ID: 21792464
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?
0
 
LVL 7

Accepted Solution

by:
orcic earned 500 total points
ID: 21792968
It depends...
It is possible to make a view that would be updatable, but you have to follow certain rules.
http://msdn.microsoft.com/en-us/library/ms180800.aspx
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 21793118
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.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question