Access Cookbook - Ken Getz [297]
The code in the AfterUpdate event of the first combo box is simply to requery the second combo box:
Me.SecondCombo.Requery
In the example shown in 14-07.adp, the form itself is not a bound form. In other words, its record source is assigned at runtime in the AfterUpdate event of the combo box, which selects an individual customer. If you were using this example on a bound form to filter records, you would need to call the code in the AfterUpdate event in the OnCurrent event as well.
14.8. Reference Data from More than One SQL Server Database in an ADP
Problem
You'd like to have your ADP connect to multiple SQL Server databases at one time. However, the Data Link dialog allows room for only one SQL Server database.
Solution
Although at first glance this seems to be a problem, the solution is readily at hand with SQL Server's three-part naming convention. You are probably already familiar with the OwnerName.ObjectName syntax for referring to SQL Server objects, which is needed when users other than the owner (or creator) of that object wish to use the object. The three-part naming syntax is:
DatabaseName.OwnerName.ObjectName
To refer to another SQL Server database in your ADP, follow these steps:
Create a new project and link it to the Northwind database. You can look at the list of tables and see only the tables from Northwind.
Create a new form. Type the following statement into the RecordSource property of the form:
SELECT * FROM pubs.dbo.authors
You will then see the Field List for the authors table in the pubs database. Figure 14-18 displays the Field List from the sample form, frmPubsAuthorsSQL, and shows that the form is now bound to data in the pubs database, not the Northwind database.
Figure 14-18. The Field List from frmPubsAuthorsSQL
Alternately, you can create a view in the Northwind database that selects data from the pubs.authors table:
CREATE VIEW vwPubsAuthors
AS
SELECT au_id, au_lname, au_fname, phone,
address, city, state, zip, contract
FROM pubs.dbo.authors
You can then base forms and reports in your Northwind project on the view.
Discussion
SQL Server allows users to access other databases residing on the same server when the three-part naming syntax is used. However, users must have been granted permissions in the source database if data is to be accessed with a direct SQL statement. SQL Server will return a permissions error message if those permissions have not been granted.
Working with data from multiple databases is easy in ADPs, even though you see the objects from only one database listed in the database window. Just remember to use the three-part naming syntax.
If the data you need is not just in another database but on another server, it gets a little more complicated. In this case, you need to set up a linked server in SQL Server to access the data. Linked servers in SQL Server use OLE DB providers, which means you are not limited to only SQL Server data. Linked servers allow you to use SQL Server as a gateway to many different data sources, just as you may use Access databases to link to multiple data sources.
14.9. Use Views to Update Data in an ADP When Users Don't Have Permissions on Tables
Problem
You have secured your SQL Server 2000 database and removed all permissions for users to directly interact with tables. You have created views and granted users permissions to update data through the views instead. However, users normally are not allowed to update data through views when they don't have access to the underlying tables. How can you allow them to update tables through views?
Solution
Whenever you secure your database in SQL Server, you have the option of denying permissions on tables and granting permissions for users to work with the data only through secondary objects such as views, stored procedures, or user-defined inline functions. As long as both the underlying table and the secondary object have the same owner, SQL Server does not check permissions on the underlying table,