ODBCLink/SE Reference Manual | Using ODBCLink/SE With ODBC Applications |
Notes for Users of
⊗For a table to be updatable in Access, it must have a unique key and Access must know about it. If the table is defined in ALLBASE/SQL with a unique key, this will be reported by ODBCLink/SE (in the SQLStatistics call that Access makes) and the table will be updatable. If Access cannot locate a primary key, it will prompt for the column or combination of columns that make up a unique key. You can ignore this prompt, and the table will not be
updatable from Access; or you can specify one or more columns that make a
unique key. If this combination of columns is not unique (i.e. if there are duplicate records with the same key combination) Access will not work properly. When you enter a unique key combination, ensure that it really is unique within the table. To see the column or columns that Access uses as
the unique key, look at the table in Design view. The column will have Akey@ symbol in the left most column.
⊗Access uses a different algorithm to access a table depending on whether a unique key exists or not. If it finds a unique key, it downloads the key values and then issues an SQL statement of the form Aselect...From...Where key=? Or key=?...@. It then displays a screenful of data and stops. (Note, however, that it may continue to download the keys in the background so long as the table is open in Atable view@.) If a unique key is not defined, Access just does a regular Select statement to read the table. It will display a screenful of data but will still continue to read the table in the background. You may view the SQL that Access (and any ODBC application) generates by turning on logging in the ODBC Setup Screen .
⊗To update a record in Access, you highlight the column you want to change, make the change, and then click on any other record. Access uses an algorithm called AOptimistic concurrency control@ to verify that a record has not been changed by another user. This is a less secure method than the SELECT FOR UPDATE used by other applications, however it minimizes the number of locks that are held on the database. Optimistic concurrency control works by Access generating and Update statement of the form AUpdate Table Set column1=?, column2=?,...WHERE column1=? AND column2=? AND column3=? And ....@. This ensures that the update will fail if the record has been changed by another user since the time it was last read in by Access.
•The default type of join used by Access is the Inner Join. When creating a Query with a join between two tables, you can change this to a Left Outer Join by
⊗You cannot join a local table to a remote table efficiently in Access unless there is a
ODBCLink/SE |
|
©M.B. Foster Associates Limited | 45 |