Access Cookbook - Ken Getz [289]
document.cookie = "enddate=NULL;expires=Monday, 01-Jan-95 12:00:00 GMT"
The script calls the ReadVarInCookie function to find the values of startdate and enddate:
strStart = ReadVarInCookie("startdate")
strEnd = ReadVa1fp found, the code uses the DataSource component's object model
to set parameter values for the query on which the page is based:
MSODSC.RecordsetDefs("qryOrdersByDate").parametervalues.Add "[Start Date]",
strStart
MSODSC.RecordsetDefs("qryOrdersByDate").parametervalues.Add "[End Date]", strEnd
Finally, the code clears the cookie by setting the variable values to Null and providing an expiration date in the past:
document.cookie = "startdate=NULL;expires=Monday, 01-Jan-95 12:00:00 GMT"
document.cookie = "enddate=NULL;expires=Monday, 01-Jan-95 12:00:00 GMT"
We've only just touched the surface of coding DAPs. To go farther, you'll need to learn more about the document object model that Internet Explorer supports, and also about the Microsoft Office Data Source Control (MSODSC), the object model used in DAPs for retrieving and updating data.
Chapter 14. SQL Server
Microsoft has always made it easy to connect to SQL Server data from Access by allowing you to create linked tables using Open Database Connectivity (ODBC). You have also been able to create pass-through queries in Access that use ODBC to send commands to SQL Server for processing.
In Access 2000, Microsoft introduced a new way of using Access to work with SQL Server. Instead of creating regular MDB databases and using ODBC, you could create a new kind of application called an Access Data Project (ADP). ADPs don't use the Jet database engine and ODBC; instead, they use an OLE DB connection to a SQL Server database. In ADPs, you have the ability to view and modify SQL Server objects, and you can create forms, reports, and data access pages based on your SQL Server data.
In this chapter, we present a range of tips for using both traditional MDBs and the new ADPs to create Access applications that read and manipulate data stored in a SQL Server database. Several of the examples make use of the Northwind and Pubs sample databases that ship with SQL Server.
14.1. Dynamically Link SQL Server Tables at Runtime
Problem
Your Access SQL Server database uses linked tables and views in SQL Server. You have set up security and permissions in SQL Server and want to make sure that each user's linked tables are attached under their own permissions, not another user's permissions. In addition, you don't want the users to be prompted for an additional login ID and password each time they use a table.
Solution
If you link SQL Server tables from an Access database using the File Get External Data menu commands, you will be prompted to use or create a Data Source Name (DSN). The main drawback to DSNs is that they need to be installed on every user's machine. A better solution is to use VBA code to link or relink tables. You can supply connection information in the Connection string without having to create a DSN.
This technique uses DAO to create new TableDef objects in each database when the application starts up. The startup form for the application has a dialog where the user can supply a login and password to be used to connect to SQL Server. The list of table names is stored in a local Access ( Jet) database.
To add this technique to your application, follow these steps:
Create a table to hold the names and properties of the SQL Server tables to which your application will link. In the 14-01.MDB sample database, the local table is named tblSQLTables. The column definitions are listed in Table 14-1.
Table 14-1. Column definitions for tblSQLTables
Column name
Data type
Primary key?
Required?
SQLTable
Text 50
Yes
Yes
SQLDatabase
Text 50
No
Yes
SQLServer
Text 50
No
Yes
Enter data in the table. Figure 14-1 shows the datasheet view of the table used to store data about the tables that are linked from the Northwind database